Updating binary data through DBI

EyeOpener has asked for the wisdom of the Perl Monks concerning the following question:

Hello monks,

I'm working on a script which will gzip a bunch of images in a SQLServer 2000 database. The actual script is pretty lengthy (it iterates through several records and includes tons of error-checking), so I've abbreviated it here:

use DBI;
use Compress::Zlib;

my $server = "servername";
my $database = "databasename";
my $connstr = "driver={SQL Server};Server=$server;database=$database";
my $dbh  = DBI->connect("dbi:ODBC:$connstr") or die "$DBI::errstr\n";

my $qry_getdata = qq {
   SELECT Image FROM Table1
   WHERE Id = 1
};
my ($bindata) = $dbh->selectrow_array($qry_getdata);
my $output = Compress::Zlib::memGzip($bindata);

# Put the compressed data back here

$dbh->disconnect();
[download]

This works, but I'm stuck on the step of updating the database with the compressed data now stored in $output. I can't do a simple

   UPDATE Table1
   SET Image = $output
   WHERE Id = 1
[download]

because binary fields are handled differently (actually, this is an "Image" datatype). I'm not sure if this is a database, DBI, or ODBC question, but I was hoping one of you fabulous monks could point me in the right direction or provide a code sample. Anyone? Thanks in advance!

Comment on Updating binary data through DBI Select or Download Code

Replies are listed 'Best First'.
Re: Updating binary data through DBI by tachyon (Chancellor) on Jun 18, 2004 at 01:00 UTC
The first question is why are you trying to compress images? Most formats are already efficiently compressed so there is very little mileage in this. Anyway if you read the DBI docs and conform to best practice you will use place holders: When trying to insert long or binary values, placeholders should be used since there are often limits on the maximum size of an INSERT statement and the quote method generally can't cope with binary data. `$sth_q = $dbh->prepare_cached( 'SELECT Image FROM Table1 WHERE Id = ?' + ); $sth_u = $dbh->prepare_cached( 'UPDATE Table1 SET Image = ? WHERE Id = + ?' ); # blah $sth_u->execute( $image, $id );` [download] The main exception is M$ .BMP files which are not compressed by default. You can compress these very effectively and may well see compression of 95-98%. This only really serves to bring them towards the same size as an equivalent gif or png..... cheers tachyon	[reply] [d/l]
Re^2: Updating binary data through DBI by EyeOpener (Scribe) on Jun 18, 2004 at 19:55 UTC
Thanks to tachyon and everyone who suggested using placeholders (and, er, reading the DBI docs). That's exactly what I needed. (And yes, these are uncompressed images I'm dealing with, hence the need...)	[reply]
Re: Updating binary data through DBI by Roger (Parson) on Jun 18, 2004 at 01:02 UTC
You need to use a bind variable to submit binary data to the database. `my $sql = "UPDATE Table1 SET Image = ? WHERE Id = 1"; my $sth = $dbh->prepare($sql); $sth->execute($bindata);` [download] Assuming that you have loaded binary data into $bindata variable earlier. You may need to tell the database driver the size of your binary data in bytes, check the database driver manual to find out the exact command to do this.	[reply] [d/l]
Re: Updating binary data through DBI by mpeppler (Vicar) on Jun 18, 2004 at 05:40 UTC
Using a placeholder here is a good idea, assuming the system supports it. In the case of Sybase (yes I know - you're using MS-SQL) this wouldn't be possible, so the solution is to convert the image/binary value to a hex string, like this: `$hex_string = unpack('H*', $output); $sth = $dbh->prepare("update Table1 set Image = 0x$hex_string where Id + = 1"); ...` [download]	[reply] [d/l]
Re: Updating binary data through DBI by jZed (Prior) on Jun 18, 2004 at 01:00 UTC
Use placeholders, possibly setting the TYPE in the call to execute() if you need to. See the DBI docs.	[reply]