Skip to main content
WolfShade
Braniac
November 11, 2010
Answered

Restore file inserted in varbinary(max)

  • November 11, 2010
  • 1 reply
  • 7177 views

Hello, everyone.

I'm just learning about how to store a file directly into a database using varbinary(max).  I've got the upload/insert working just fine.

Now.. how do I retrieve the file and reconstruct it for download?

I've done some Google-ing on this, and I'm not finding anything helpful.

This is a MS-SQL Server 2005 database, working with a CF9 server.  I'm uploading the file to the webserver, then doing a READBINARY and saving it to a variable, then inserting that variable contents (in a CFQUERYPARAM tag) into the database.  How do I take the varbinary data and reconstruct the file?

Thanks,

^_^

    This topic has been closed for replies.
    Correct answer -__cfSearching__-

    Just noticed something.  The file that I used is 481k in size.  When it is retrieved from the db and saved, it's 63k in size.  It's being truncated, somewhere.


    Did you enable the appropriate CLOB/BLOB setting in your datasource? If not, CF only retrieves 64K by default.

    1 reply

    Inspiring
    November 11, 2010

    Have you tried just doing the reverse of what you did to get it into the DB in the first place, ie:xtract it with a SELECT query and write it to file with <cffile>?

    --

    Adam

    WolfShade
    WolfShadeAuthor
    Braniac
    November 11, 2010

    Yes.  It worked with .txt file, but a .doc file and a .docx file were both corrupted beyond repair (the "Open and Repair" option didn't work.)

    Am I missing an important step?

    Thanks,

    ^_^

    -__cfSearching__-Correct answer
    Inspiring
    November 12, 2010

    Just noticed something.  The file that I used is 481k in size.  When it is retrieved from the db and saved, it's 63k in size.  It's being truncated, somewhere.


    Did you enable the appropriate CLOB/BLOB setting in your datasource? If not, CF only retrieves 64K by default.