Save file in MySQL using Coldfusion

Question

I'm trying to save a file (pdf or image) uploaded from a form into a MySQL database using Coldfusion. I'm saving the file as binary data into the database which has a column setup as a BLOB but having trouble writing the file back to the screen. What is the correct way to save a file in a database and then retrieve the file in Coldfusion? Note: Client wants files stored in database instead of in filesystem.

Saving file in database code

<cffile action="upload" filefield="form.file_upload" destination="#fileDirectory#" result="file_result" nameconflict="makeunique">

<cfif #file_result.fileWasSaved# IS 'Yes'>
   <cfset title = Evaluate('form.file_title')>

   <cffile action="readbinary" file="#fileDirectory#/#file_result.serverFile#" variable="file_binary_data">

   <cfquery name="insertFile" datasource="#request.app.datasource#">
    INSERT INTO (table) (form_id, file_name, file_info)
    VALUES ('#form_id#', <cfqueryparam value="#title#" cfsqltype="CF_SQL_VARCHAR">, <cfqueryparam value="#file_binary_data#" cfsqltype="CF_SQL_BLOB">)
   </cfquery>

Writing file back to screen

<cfheader name="content-length" value="#ArrayLen( getFile.file_info )#" />
<cfheader name="content-disposition" value="inline; filename=#getFile.file_name#.gif" />
<cfcontent type="image/*" variable="#getFile.file_info#">

Answer 1

I would recommend storing the file in the filesystem, and storing the filename in the database. The database is optimized for storing and retrieving fixed-width values; storing huge files there isn't performant.

Answer 2

First, make sure that you have either enabled BLOBs in your DB andvanced connection settings in Administrator, or that the buffer is sufficiently large to take care of the images.

Second, I'd try testing generating just the image instead of writing it inline to the page, to see what the result actually is.