Storing files in db varbinary(max)

[drew_lw]

Once again, because of business requirements and compliance rules I need to perform a task out of the typical scope/method that I'm used to.

We need to store consent data within a database, and along with that, have the ability to attach a file to the record.

I must have been through over a hundred web posts looking for the real answer but haven't had much luck. Probably doesn't help that I'm a PHP coder and not a true blue coldfusion programmer...

I have a form where I'm using the following method to store the file to the db and a varbinary(max) field.

1. (cffile action="upload") Upload the form defined file to a temp directory. Accepted file types are images, doc, pdf, and txt. Possibly .zip down the line.

2. (cffile action="readbinary") Read in the file

3. Write the file to the db using cf_sql_varbinary cfsqltype and storing the filename and extension to a varchar field.

4. (cffile action="delete") Deleting the temporary file

I have a retrieval page that I'm using to mockup and test my code to retrieve the file. The original idea is to supply a hyperlink on the page where the file will be retrieved from the db, treated as an attachment, and simply let the user download the file rather than embedding it in the pages.

The retrieval code works in the following way:

1. Grabs an Id associated with the record in the db

2. (cfquery) to pull the file and file name from the db

3. (cfswitch) on the filename to determine the MIME type associated with the file.

4. This is where it's not working too well...

I have a (cfheader and cfcontent) statement in an attempt to pull the information from the db.

<cfheader name="content-disposition" value="attachment;filename="#qGetImage.ImageName#">

<cfcontent type="#sMime#" file="#qGetImage.ImageFile#">

This is the gist of the current code. Now I've tried application/octet-stream etc. to pull the file from the db to a file.

I've gotten it to work with images just fine by using the cfimage tag...but this was simply used to verify that the record in the db wasn't corrupt in any way. If I'm prompted for a file download it simply uses the retrieve.cfm?fileId=# (Where #=Id number of the record) as the filename and of course fails to download properly because of the missing filename and extension type. Juggling code around I'll get a ByteArray objects cannot be converted to strings error.

I've been reading and implementing for the past 4 or so hours with no luck so using this as a last result to solve this issue.

Am I able to use a single routine (cfheader/cfcontent) to export all files from the db or will I need different calls for different files?

Really hoping for some insight in this and thanks,

-drew

[ilssac]

The first thing I will add is to make sure you use the reset="true" parameter in your <cfcontent...> tag.  Otherwise any white space in your CFML file will be include in the response sent to the client throwing off any binary content you are trying to deliver.  With text based content like HTML extra whitespace is seldom a problem.  But binary data cares about every single byte sent.

[drew_lw]

Yea, sorry, I actually do have that attribute defined within cfcontent. I just didn't transfer over the code properly in my forum posting. The error I'm focusing on is ByteArray objects cannot be converted to strings.

Some additional background info:

CF 8.0.1, db is mssql

[-__cfSearching__- · Accepted solution]

The error I'm focusing on is ByteArray objects cannot be converted to strings.

That type of error generally means you are passing binary data into a function/tag when it expects a string.

<cfcontent type="#sMime#" file="#qGetImage.ImageFile#">

If qGetImage.ImageFile contains binary data, use cfcontent's "variable" attribute instead.  The "file" attribute is used when you are referencing a physical file, and the value would be a path like c:\somefolder\myImageName.jpg.

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_c_11.html

[JR__Bob__Dobbs-qSBHQ2]

You should also verify that the "Enable binary large object retrieval (BLOB)" option is checked for your CF datasource.

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=datasources_ADV_MJS_11.html#1278307

[drew_lw]

oh man, that was one of those "holy crap, are you kidding me moments!?"

Thank you so much! I can't believe I forgot and/or didn't read into the cfcontent issue.