http://www.perlmonks.org?node_id=1142818

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

Hello Masters of Perl Wisdom. This is my first post here, though I read the site often. Thank you for the opportunity.

I have a file (in this example, it's a docx file) that is stored into a SQL Server 'Image' type field and also a varbinary() field type. I'm pulling the image-type field (don't know if this matters).

I am using baby-Perl code (below) to retrieve the file from the DB and re-build the file locally on my PC, however, when I try to open the file for use on my local PC, it is corrupted and unusable (recovering doesn't work).

Can you please let me know if the below code SHOULD work? Should I be adding the "content-type" somewhere, or am I missing something else? Any help would be greatly appreciated:

use strict; use warnings; use DBI; use DBD::ODBC; #command [arg1] [arg2] #perl getfile.pl [outputfilename.extension] [documentsUniqueId] my $outfileName = $ARGV[0]; # filename: "TestRecoverBlob.docx" my $uniqueId = $ARGV[1]; #unique to find the proper DB row/field hex n +umber - 0x01420C.....B689 my $user = "user"; my $pass = "pass"; my $dsn = "dbi:ODBC:Driver={SQL Server};Server=X.X.X.X;Database=DB_NAM +E"; my $dbh = DBI->connect($dsn,$user,$pass) or die DBI::errstr(); $dbh-> {LongReadLen} = 26214400; #25MB max #10485760 = 10MB; my $sth = $dbh->prepare(qq{ select t.RDFILE from DB_NAME.schema.TABLE_NAME t where t.UNIQUEID = $uniqueId }); $sth->execute(); # Create file : open (FILE, ">", $outfileName); while (my $row = $sth->fetchrow_arrayref()) { # Save blob data into this file: print FILE $row; } close FILE; $dbh->disconnect(); print "Finished."

Update: Please note, I have read the other BLOB posts that I found before posting this. Other's are using different DB types, and I haven't seen an example yet for retrieving a large BLOB from SQL Server and reconstructing the file locally... this use case appears to be different from others in that I have a need to retrieve documents of variable mime/application types.

Another Update: I noticed that my call to the DB did grab the full blob - but I also noticed my code didn't set binmode on the filehandle (FILE). I added those pieces, and the file still will not open. It builds a file fine, but it won't open in Word.

[...] open (FILE, ">", $outfileName); binmode FILE; #set binmode while (my $row = $sth->fetchrow()) { #fetchrow_arrayref # Save blob data into this file: print FILE $row; } close FILE; $sth->finish(); $dbh->disconnect();

Another-nother Update: bringing syswrite into this also does nothing.

Using the MS SQL tool "BCP" to export the blob produces the exact same file as this Perl script. This looks to be like a blob encoding/packing issue. Has anyone experienced this before?

FINAL UPDATE: I'm an idiot. Reviewed the hex per the recommendation of Perl Monks (sundial + Myrddin), and found that the document was an executable file. Other testers had replaced the DOCX file without my knowledge, and I was still working on that same field and it's unique ID.

I will now do the only honorable thing, and hang myself with my keyboard wire. Thanks for your help. BTW- the script works to retrieve BLOBs and reconstructs the file perfectly. My apologies for wasting everyone's time.