Restore file inserted in varbinary(max)

[WolfShade]

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,

^_^

[-__cfSearching__-]

Now, I can't even insert gt 64k into the database - doing a "select len(contents)" everything larger than 64k shows up as exactly 64000

It is not the problem, but to get the size in bytes use dataLength() not len().  As far as the contents being truncated at 64K, I am not sure what is going on there. AFAIK the BLOB settings should only affect how much data you can retrieve from the database. So you should be able to INSERT anything you want. The settings should only kick in when you try and pull information out of the database. The settings will determine how much of the contents to retrieve: everything or only X bytes.

[WolfShade]

I just queried the table using dataLength() instead of len() - both are reporting the same size of files in the database: 64k.

Does CLOB need to be set as well as BLOB?

I was told that the BLOB setting has been turned on so there's no limit on size as far as the database is concerned - we are going to limit filesize via CF.  Yet I uploaded a 481k file twice, and both times the dataLength is 64000.

Odd.

^_^

[WolfShade]

Okay.  Now it's back to uploading the full file size (481k), but still only allowing 64k to be downloaded.  Still waiting to hear back from supervisor about settings on DSN.

^_^

[-__cfSearching__-]

(Resposting a summary as none of my email responses seem to be going through ...)

> Does CLOB need to be set as well as BLOB?

No, not as far as I know.  Varbinary columns fall into the BLOB (binary large object) category.

Weird. I am really not sure why you are seeing such strange behavior.  I tested the settings you described locally and could not reproduce  that behavior. I could +always+ INSERT more than 64K bytes, and as  expected, the "Enable binary large object retrieval" setting determined  whether or not I could retrieve the full contents.

-Leigh

[WolfShade]

I'm beginning to think that the admin lied about the BLOB being set.  IDK.  Frustrating, but I keep going.

Thanks for your advice.  We'll see if I can get a straight answer about the BLOB setting from the admin.

^_^

BTW.. I think the message you are replying with will include the post you are replying to if you click on the " symbol in the top of the editor window.

[-__cfSearching__-]

>> I think the message you are replying with will include the post you are replying to if you click

Yep, I know 😉  But I find this forum's rich text editor a bit too "quirky" at times.  So it is often quicker to "quote" manually. Besides, I usually respond via email .. and lord knows those never look the same once the forum software gets through with them 😉

[-__cfSearching__-]

duplicate

[-__cfSearching__-]

duplicate ..

[-__cfSearching__-]

BTW: I suspect it is saving the object's hash code instead of the actual bytes.