Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

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 [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.

In reply to Retrieve file BLOB from SQL Server 'Image' field type by porg

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or or How to display code and escape characters are good places to start.
Log In?

What's my password?
Create A New User
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (6)
As of 2021-06-12 19:36 GMT
Find Nodes?
    Voting Booth?
    What does the "s" stand for in "perls"? (Whence perls)

    Results (53 votes). Check out past polls.