Working with DBI and BLOB

[mhearse]

I'm trying to write a simple program to grab satellite images from the web. They are updated about every hour. After fetching the image, I check to see if it is current and store it in a MySQL database. Only one table in the database:

mysql> describe gw_ir;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| image_id   | int(11)     |      | PRI | NULL    | auto_increment |
| image_name | varchar(50) | YES  |     | NULL    |                |
| image_file | mediumblob  | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

The script is primitive, as this is I am definetely a DBI amateur:

use LWP::Simple;
use DBI;

$url     = "http://weather.unisys.com/satellite/sat_ir_west.gif";
$file    = "/usr/home/mhearse/weather_images/sat_ir_west.gif";
$newfile = "/usr/home/mhearse/weather_images/latest.gif";
($min, $hour, $day, $month, $year) = (localtime)[1,2,3,4,5];
$image_name = "$month-$day-$year-$hour:$min";
getstore($url, $file);

if (-s $file != -s $newfile) {
   rename $file, $newfile;
   $img_data = getFile($newfile);
   $dbh = DBI->connect("dbi:mysql:weather_images",
             "user", "********")
             or die("Error! $!\nAborting");
   $sql = qq(INSERT INTO gw_ir
                (image_name, image_file)
                 VALUES ("$image_name", "$image_file"));
   $sth   = $dbh->prepare($sql);
   $sth->execute or die("\nError executing SQL statement! $DBI::errstr
+");
   $dbh->disconnect;
} else {
   print "file is current\n";
}

sub getFile {
  my($file) = @_;
  my $fh;
  unless (open $fh, "<", $file) {
    die "Error opening $file : $!\n";
  }
  my $data;
  { local $/ = undef; $data = <$fh>; }
  close $fh;
  return $data;
}

The main problem I'm having is with the storing the gif as BLOB. It doesn't seem to be working. Am I doing something obviously wrong? Also, I'm downloading the image every 60 minutes (it's only about 100k). Then comparing it to the previous to see if it is newer. Is there a better way to see if the image is current? I appreciate any help.

[saskaqueer]

This is exactly the purpose behind using strict. Doing so, you would have found that you are placing the actual image data in $img_data, but are trying to put $image_file into the database. Switch either one of the variables and it should work fine.

update: I figured you could use a small rewrite. Some points I covered:

• I got rid of the useless temporary download file -- a little bit of in-memory manipulation will do just fine.
• I compare the image contents rather than the file size. In your version, you might have gotten false results if the new image size was the same but an actual different image.
• I changed the way the database update is done. I just used DBI's do() method instead of preparing and executing the SQL. Also, I threw in the use of placeholders.
• I added a check to die() if the image fetch fails. We wouldn't want false updates going on now, would we?

#!perl -w

use strict;
use DBI;
use LWP::Simple;

my $img_url = "http://weather.unisys.com/satellite/sat_ir_west.gif";
my $latest_img = "/usr/home/mhearse/weather_images/latest.gif";

my ($min, $hour, $day, $mon, $year) = (localtime())[1..5];
$year += 1900;

my $img_name = "$mon-$day-$year-$hour:$min";
my $img_data = get($img_url);

die("failed to fetch image from internet!\n");
    unless ( defined($img_data) );

open( my $fh_latest, ( -e($latest_img) ? '+<' : '+>' ), $latest_img )
    or die("could not open file: $!");
binmode($fh_latest);

my $latest_data = do { local $/; <$fh_latest> };

if ($latest_data ne $img_data) {
    seek($fh_latest, 0, 0);
    truncate($fh_latest, 0);
    print $fh_latest $img_data;

    my $dbh = DBI->connect(
        'dbi:mysql:weather_images', 'user', 'secret', {
            RaiseError => 1, AutoCommit => 1
        }
    );

    $dbh->do(
        'INSERT INTO gw_ir (image_name, image_file) VALUES(?,?)',
        undef, $img_name, $img_data
    );

    $dbh->disconnect();

    warn("File was not current; updates made.\n");
}

else {
    warn("File was current, no updates made.\n");
}

[mhearse]

You're right, that was sloppy. Here is a revision:

mysql> describe gw_ir;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| image_id   | int(11)     |      | PRI | NULL    | auto_increment |
| image_name | varchar(50) | YES  |     | NULL    |                |
| image_data | mediumblob  | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

#!/usr/bin/perl

use strict;
use LWP::Simple;
use DBI;

my $url     = "http://weather.unisys.com/satellite/sat_ir_west.gif";
my $file    = "/usr/home/mhearse/weather_images/sat_ir_west.gif";
my $newfile = "/usr/home/mhearse/weather_images/latest.gif";
my ($min, $hour, $day, $month, $year) = (localtime)[1,2,3,4,5];
my $image_name = "$month-$day-$year-$hour:$min";
getstore($url, $file);

if (-s $file != -s $newfile) {
   rename $file, $newfile;
   my $image_data = getFile($newfile);
   my $dbh = DBI->connect("dbi:mysql:weather_images",
             "ser", "********")
             or die("Error! $!\nAborting");
   my $sql = qq(INSERT INTO gw_ir
               (image_name, image_data)
                VALUES ("$image_name", "$image_data"));
   my $sth   = $dbh->prepare($sql);
   $sth->execute or die("\nError executing SQL statement! $DBI::errstr
+");
   $dbh->disconnect;
} else {
   print "file is current\n";
}
sub getFile {
  my($file) = @_;
  my $fh;
  unless (open $fh, "<", $file) {
    die "Error opening $file : $!\n";
  }
  my $data;
  { local $/ = undef; $data = <$fh>; }
  close $fh;
  return $data;
}

MySQL doesn't like the sql statement.

DBD::mysql::st execute failed: You have an error in your SQL syntax.  
+Check the manual that corresponds to your MySQL server version for th
+e right syntax to use near 'ÀµÖ* íÆ. ³ô®´ØR{mþÒ:»Àºëö*·ì¶k¼½.àë·' at 
+line 3 at ./simple line 24.

Error executing SQL statement! You have an error in your SQL syntax.  
+Check the manual that corresponds to your MySQL server version for th
+e right syntax to use near 'ÀµÖ* íÆ. ³ô®´ØR{mþÒ:»Àºëö*·ì¶k¼½.àë·' at 
+line 3 at ./simple line 24.

[BUU]

Just for correctness reasons, the reason this attempt doesn't work is that you attempt to interpolate the unescaped image data directly in to your sql statement. This is bad. Use placeholders, or at least dbi->quote.

[mhearse]

Your rewrite works flawlessly! Thanks much. You can disregard my previous reply.

[saskaqueer]

Actually, as a final "fix" to the whole thing -- do you actually need the latest image file to be kept on the file system, or are you just keeping it there to compare against the next fetch? If you do not need the image on the file system, you can bypass that altogether and just use the database:

#!perl -w

use strict;
use DBI;
use LWP::Simple;

my $img_url = "http://weather.unisys.com/satellite/sat_ir_west.gif";

my $dbh = DBI->connect(
    'dbi:mysql:weather_images', 'user', 'secret', {
        RaiseError => 1, AutoCommit => 1
    }
);

my ($min, $hour, $day, $mon, $year) = (localtime())[1..5];
$year += 1900;

my $img_name = "$mon-$day-$year-$hour:$min";
my $img_data = get($img_url);

die("failed to fetch image from internet!\n")
    unless ( defined($img_data) );

# fetch the last row we added to the table
my $latest_entry = $dbh->selectrow_arrayref(
    'SELECT image_file FROM gw_ir ORDER BY image_id DESC LIMIT 1'
);

# either no data in database or the file contents
# have changed since the last update
if (not defined($latest_entry) or $latest_entry->[0] ne $img_data) {
    $dbh->do(
        'INSERT INTO gw_ir (image_name, image_file) VALUES(?,?)',
        undef, $img_name, $img_data
    );

    warn("File was not current; updates made.\n");
}

else {
    warn("File was current, no updates made.\n");
}

$dbh->disconnect();

---

And even if you do need the file system file, it might even be better to use the database anyway and then output the file:

#!perl -w

use strict;
use DBI;
use LWP::Simple;

my $img_url = "http://weather.unisys.com/satellite/sat_ir_west.gif";
my $local_img = "/usr/home/mhearse/weather_images/latest.gif";

my $dbh = DBI->connect(
    'dbi:mysql:weather_images', 'user', 'secret', {
        RaiseError => 1, AutoCommit => 1
    }
);

my ($min, $hour, $day, $mon, $year) = (localtime())[1..5];
$year += 1900;

my $img_name = "$mon-$day-$year-$hour:$min";
my $img_data = get($img_url);

die("failed to fetch image from internet!\n")
    unless ( defined($img_data) );

# fetch the last row we added to the table
my $latest_entry = $dbh->selectrow_arrayref(
    'SELECT image_file FROM gw_ir ORDER BY image_id DESC LIMIT 1'
);

# either no data in database or the file contents
# have changed since the last update
if (not defined($latest_entry) or $latest_entry->[0] ne $img_data) {
    open( my $fh, '>', $local_img ) or die("could not open file: $!");
    binmode($fh);
    print $fh $img_data;

    $dbh->do(
        'INSERT INTO gw_ir (image_name, image_file) VALUES(?,?)',
        undef, $img_name, $img_data
    );

    warn("File was not current; updates made.\n");
}

else {
    warn("File was current, no updates made.\n");
}

$dbh->disconnect();

[cchampion]

Look at our Tutorials. You will find Handling huge BLOB fields with DBI and MySQL, which covers most of your needs.

Since you are there, however, don't miss the other tutorials about database programming.

Merry Christmas!

[punkish]

seems like you have solved your original problem. Congratulations.

Now, for another perspective on this. Is there any pressing reason to store the image in the db? I've never quite understood the reason to store images in a db.

I believe in using a tool for what it was created -- a file system is great for storing files -- let it do its job.

I much rather store in the db the image attributes that I might want to search on, along with an image index as the name of the image, but store the actual image on the file system. I would also create some really simple scheme for limiting the number of images in a given folder (number the folders 0, 1, 2, 3, etc.., and store 0-99.gifs in folder 0, 100-199.gifs in folder 2, etc.). Whatever scheme, just make sure that the OS doesn't have to spin loading or searching through an entire folder. Then use DBI to store various attributes and the name of the image in the db, and just search for that. And use the various file attribute comparison functions to figure out if the image is more recent or now -- a simple timestamp comparison should do.

any way, just another perspective.