Save Image FILE to database, retrieve and display

Hey guys,

I thought this was easy and as surprised that I couldn't figure it out. Have any of you done this? Basically what I want to do is:

1. Allow the user to submit an image
2. Save the submitted image to the database MS SQL 2000
3. Retrieve the Image
4. Display the Image

Thanks in advance for the help.

daTropics

 

Why do you want to store the image to the database? It's doable - but not recommended because the image will then have to be transferred twice (from db server to web server then from web server to client) doubling the bandwidth used on your web server.

Not to mention being detremental to the caching benefits of coldfusion and mysql, having a large ram footprint on both the sql server and your http server etc etc.

 

Yeah I know - it's a requirement, can you help me out?

 

IIRC
1. Use the "image" data type for the storing the images in the table.
2. Insert the image using cfqueryparam and the "cf_sql_blob" type
3. Retrieve the image in a query and use cfcontent with the "variable" attribute to display the image

 

Make sure "enable binary large object retrieval" is enabled for your datasource in coldfusion administrator.

Read the binary from the file field
<cffile action="readbinary" file="#form.filefieldname#" variable="bin_filedata">

then

<CFQUERY ....>
INSERT INTO tablename FIELDS (........, binarydata, .......) VALUES (........., <cfqueryparam value "#bin_filedata#" cfsqltype="cf_sql_blob" />,.....)

Hope that helps

 

oh yeah for the submit part
<input type="file" name="filefieldname">

for the retrieval / display part
You have different options - but I prefer to create a file that serves up the image because you can then also check for hot links or put other security measures

inside show_image.cfm
<CFPARAM name="image_id" default="">
<!--- Get image --->
<cfquery name="grab_image">
SELECT imagefieldname
FROM tablename
WHERE whatever <cfquery>
(OR it would be better to use a stored procedure here)

<!--- Set MIME type to GIF or JPEG or ... --->
<cfcontent variable="grab_image.imageData" ... >


then inside your display page <img src="show_image.cfm?image_id=_theid_">


there are other ways if you want to save the extra query for the blob

 

Why not let your users upload the image to a folder in your root directory and store the path to the file in the database. Then query the database to retrieve the path and display the image using an <img> tag.

You can use <cffile> to upload the image.

 

Thanks guys, this is great!

United - yeah I know what you're saying but the client has this as a requirement - you know how it is,

Thanks again

 

Advantage,

did you try this code yourself?
does it work?
I'm getting an error, which version of CF are you using?
The client has 4.5, would that be an issue? (don't laugh)

thanks!

daTropics

 

Got them go to CF7 - working - thanks!

 

Nice to know that not everyone is using the latest and greatest. A coworker recently switched to eclipse, but before that they were using CF Studio 4 ;-)

 

Don't know why though - because they are running around trying to do the stuff that the later versions do with ease - can't wait to sink my teeth into CF 8!!!!!!

 

VERY true. But I admit I get a kick out of hearing CF4.5 is still being used ;-)

I can't wait to try CF8 either. CFPDF, CFTHREAD,CFIMAGE, debugging, server monitoring, queries that return identity values - oh boy!

 

This sounds the best idea to me. So how we do this in the img tag?

 

krmria,

Best to start your own thread rather than resurrecting one that's 4 years old.

 

I have one question about storing image in database is that the image field in database has type varchar or image.please help me.

 

If I given the image as varchar in database then it will work or not?

 

Why? This thread is still live on google search list so 4 years old or not makes no difference

 

Saving Images To database

This should help. Don't use image or varchar just use blob as the datatype.

 

if course it makes a difference ... if you actually want a timely response.

- resurrecting old threads is what spammers do. I usually ignore them completely
- old threads usually have tons of responses already. most people don't have time to read a slew of posts just figure out if they can answer the new question. ie When you're getting something for nothing (free advice) don't insult those donating their time by making them work even harder. Most will decide it's not worth the effort
- .. and after 4 years most of the people you're asking probably aren't even following anymore anyway (notice the delays...).

but if you want wait for answers or wade through a ton of unrelated comments to get to one ... go for it!