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.

Replies are listed 'Best First'.
Re: Retrieve file BLOB from SQL Server 'Image' field type
by choroba (Archbishop) on Sep 23, 2015 at 15:01 UTC

      Hi, thanks for the reply - I read that post before posting this (we are NOT colleagues), and I attempted to integrate his retrieval method using STDOUT, but I still receive the same result. Also it seems his question had a different use-case... Should I always be using STDOUT/fetchrow() for this type of retrieval?

        Dealing with a general docx file, especially one produced by Word, is difficult because Microsoft don't follow their own specs in all cases. Usually, most specific cases are easier - it's perfectly possible to produce valid, well formatted docx documents using e.g. Template::Toolkit and Archive::Zip, since docx files are just zipped xml at heart.

        I have no knowledge of SQL Server BLOB formats, but it might be worth trying to unzip the .docx file you produce to see if:-

        1. It's a valid zip file.
        2. It contains stuff that looks like xml

        If it doesn't look anything like a zip file, you probably need to do some more processing on the BLOB before writing it to file; if it looks docx-ish, you just have to work out what changes you need to make Word eat it.

Re: Retrieve file BLOB from SQL Server 'Image' field type
by Corion (Pope) on Sep 24, 2015 at 05:45 UTC
    select t.RDFILE from DB_NAME.schema.TABLE_NAME t where t.UNIQUEID = $uniqueId

    Are you sure that the rows get returned in the order you want?

    You will need an ORDER BY clause on something.

      The column names suggest a single row will be returned.


      This is not a Signature...
      Hi, thanks for the reply - this will only return one result, so I would assume the info is returned in the manner i should read it.
Re: Retrieve file BLOB from SQL Server 'Image' field type
by monkey_boy (Priest) on Sep 24, 2015 at 13:16 UTC
    while (my $row = $sth->fetchrow()) { #fetchrow_arrayref # Save blob data into this file: # print FILE $row; # if you are fetching an array reference with a single value: print FILE $row->[0]; }



    This is not a Signature...
      thanks for the tip
A reply falls below the community's threshold of quality. You may see it by logging in.