5 Replies Latest reply on Oct 5, 2009 1:45 PM by drew_lw

    Storing files in db varbinary(max)

    drew_lw Level 1

      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,