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,

^_^

[Adam Cameron.]

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]

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__-]

What code did you use (just the basic SELECT and save/download)?

[WolfShade]

The file is uploaded via form submission and saved to server HD, then CFFILE action="BINARYREAD" and saved to OUTPUT.  From here, the query inserts the bin to a varbinary(max) datatype.

For retrieving the file, I'm doing a SELECT query plus the following (#contents# is the column in the database that contains the binary):

     <cffile action="write"
          file="#ExpandPath( "./" )##selectFiles.filename#"
          output="#contents#" />

The txt file opened with no problem.  The doc and docx files both could not be opened, at all.

^_^

[Adam Cameron.]

You probably need to make sure your character encoding is the same throughout the process.

--

Adam

[WolfShade]

I'm a complete n00b when it comes to storing/retrieving files in a varbinary(max) (or any datatype, really) in a database.  How do I maintain character encoding in this process?

Also, I don't know if it makes any difference, but I've noticed something else, just now.  The text file is 86 bytes in size, but the other files are much larger (at least 27k and 80K, I think.)  Could file size be part of the issue?

Thanks for your advice.

^_^

[-__cfSearching__-]

Open the file. What are the actual contents?

[WolfShade]

The text file is just one sentence ("This is so the file isn't empty - SQL doesn't like empty files.")

The other two (doc and docx) are filled with a lot of text, tables, formatting, and a few images.

[-__cfSearching__-]

The other two (doc and docx) are filled with a lot of text,

tables, formatting, and a few images.

No. Open the "bad" file with notepad (ie 86 byte file)

[WolfShade]

The 86 byte file isn't the "bad" one, it's the other two (doc & docx) that are corrupted/unopenable.  I'll open one of those in Notepad.  What am I looking for?

^_^

[-__cfSearching__-]

No, I read your response incorrectly. I thought the larger .docx files were being saved as 86 bytes. Maybe the data is corrupted? Are you using the correct cfsqltype when you insert the binary data into the database?

[WolfShade]

<cfqueryparam CFSQLTYPE="CF_SQL_VARBINARY" value="#binFile#" />

[-__cfSearching__-]

 <cffile action="write"          file="#ExpandPath( "./" )##selectFiles.filename#"          output="#contents#" />

What you described sounds right.  I notice #contents# is not scoped. Since we cannot see the full context, are you certain #contents# is pointing to your query column?

[WolfShade]

I can throw the scope on there, but it's within the CFOUTPUT QUERY="queryname" tag and there is nothing else called #contents# in any scope.

[WolfShade]

I put it in scope.  Still getting the same error when trying to open the DOC file:

I had a screenshot of it, but now I can't upload it.. sigh.

[-__cfSearching__-]

Try a simple standalone test with hard coded file paths.

<cfset variables.fileName = "NewFileName.doc">
<cffile action="readbinary" file="c:\myFile.doc" variable="variables.binFile">

<!--- assumes identity column for record ID --->

<cfquery name="addFile" datasource="#dsn#" result="savedFile">
    INSERT INTO TestTable (FileName, Content)
    VALUES
    (
        <cfqueryparam value="#variables.fileName#" cfsqltype="cf_sql_varchar">
        ,  <cfqueryparam value="#variables.binFile#" cfsqltype="cf_sql_varbinary">
    )
</cfquery>

<cfquery name="selectFiles" datasource="#dsn#">
    SELECT    FileName, Content
    FROM    TestTable
    WHERE    ID = <cfqueryparam value="#savedFile.IDENTITYCOL#" cfsqltype="cf_sql_integer">
</cfquery>

<cffile action="write"
          file="c:\#selectFiles.filename#"
          output="#selectFiles.content#" />

Done

[WolfShade]

Tried that and get the same error message.

[WolfShade]

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.

[-__cfSearching__- · Accepted solution]

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

[WolfShade]

If that is something that has to be done from within CF Admin, I'll put in a request for it.

If that is something that I can set with a parameter, let me know.

Thanks,

^_^

[-__cfSearching__-]

Yes. The BLOB setting must be changed through the CF Administrator.

http://help.adobe.com/en_US/ColdFusion/9.0/Admin/WSc3ff6d0ea77859461172e0811cbf364104-7fe5.html

[WolfShade]

The request has been submit, and I have been informed (earlier today) that BLOB has been selected, so (at least in dev) I'll have unlimited size for uploading.  And the whole file is being inserted into the table; but I'm still only downloading 64k of that file.  Is there another setting or something else I should be doing that I'm not aware of?

Thanks,

^_^

[-__cfSearching__-]

1) Are you sure they enabled the correct setting?

ie Enable binary large object retrieval (BLOB)."

2) I am not sure, but you might need to reset the database connections (or restart the server) to fully apply the setting. Even if it is not required, it could not hurt on a dev box.

If you are consistently receiving a maximum of 64K only, then it strongly suggests the proper setting is not enabled. Especially if you can successfully process files smaller than 64K ..

[WolfShade]

Actually, I'm not sure exactly what is going on.  Things have changed.

First, I could upload any file size and the whole file would be in the database; I just couldn't download more than 64k.

Then I was informed that the request went through and there's no limit as far as the DSN goes; we have to implement our own limits.

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.

I've emailed my supervisor to see if he knows what is going on.

Thanks,

^_^