in reply to Re: Working with DBI and BLOB
in thread Working with DBI and BLOB

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.

Replies are listed 'Best First'.
Re^3: Working with DBI and BLOB
by BUU (Prior) on Dec 25, 2004 at 19:37 UTC
    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.