Retrieve file BLOB from SQL Server 'Image' field type

[porg]

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.

[choroba]

See Handling BLOB data in ORACLE using perl script, another today's message. Are you colleagues?

[porg]

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?

[porg]

This post http://stackoverflow.com/questions/8309181/perl-module-for-generating-doc-or-docx-document-files seems to imply that there is no way for Perl to write a DOCX, however, would this really matter if the data is stored as a blob? It seems that if all the binary data is already there, then all that should be done to reconstruct the DOCX file would be to dump the binary... right?

[Myrddin Wyllt]

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.

[Corion]

                    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.

[monkey_boy]

The column names suggest a single row will be returned.

[porg]

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.

[monkey_boy]

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];
}

[porg]

thanks for the tip