Storing and retrieving image files in a database

[Duke Snyder]

hi JerkyRobot I can see no one answered your question. here is the answer.
a few things you will have to do before this will work. 1) you need to tweak
your DSN in the Advanced settings. make sure the CLOB (enable long text
retrieval) is checked. 2) make sure the table column you are storing the data
in is longtext or comparable. keep in mind this is Linux and MySQL. <!---
first we read the binary file ---> <CFFILE ACTION='ReadBinary'
FILE='/your_path/your_image.jpg' VARIABLE='aBinaryObj'> <CFIF
isBinary(aBinaryObj)> <CFSET base64 = toBase64(aBinaryObj)> <CFELSE>
<CFABORT showerror='The file does not appear to be a binary file'> </CFIF>
<!--- if everything is cool, we insert the data ---> <CFQUERY
datasource='your_DSN'> INSERT INTO your_table (your_field) VALUES
(<CFQUERYPARAM VALUE='#base64#' CFSQLType='CF_SQL_CLOB'>) </CFQUERY> <!---
now to get the data back, just query the DB ---> <!--- now we use cffile agian
to convert it back to an image ---> <CFFILE action='WRITE'
file='/your_path/your_image_name.jpg' output='#toBinary(getImg.attachment)#'
addnewline='No'> <!--- there you go. this will work for any binary file
(.exe, .zip etc.) --->

[Superflyc]

another way to do it, assuming it's been stored as a BLOB (or whatever your
database calls binary info) is to do a simple select query on it, then output
it to the browser like so: <cfcontent type='image/jpg'>
<cfoutput>#toString(your_image_field_here))#</cfoutput> CFCONTENT and CFHEADER
have lots of cool uses.

[shahzad]

Hi Duke Snyder, I copied your code and changed CF_SQL_CLOB to CF_SQL_BLOB and
tried to run it but i got a following error 'rror casting an object of type
to an incompatible type. This usually indicates a programming error in Java,
although it could also mean you have tried to use a foreign object in a
different way than it was designed.' I am using CFMX 7 and oracle 9i is the
db. I also tried to run the code using CF_SQL_CLOB but that gave me the
following error 'Macromedia][Oracle JDBC Driver]Value can not be converted to
requested type.' any ideas? thank you

[shahzad]

nevermind, someone helped me to fix the problem. For reference for the others,
i shouldn't be converting the image to base64 because my datatype for the
column in the db is BLOB. if it was CLOB, then i would convert it to base64