Serving Images From Databases

When images are associated with database records, those images are usually stored on disk. The images are related to their respective records by storing their names in a table or perhaps by using the record’s identifier as part of the file name. The advantages of storing images as files are performance (no database access needed to retrieve the file, and likely nothing but straight HTTP access), and flexibility.
But sometimes you may indeed need to store images within database tables (perhaps as blobs). Storing the images is easy enough, serving them dynamically is a little more complex. You can’t simply use the column name in the IMG SRC attribute as you would static image files, although ColdFusion would indeed embed the image data within the tag, the browser would not display the image as intended.
When browsers render pages containing images they do so by making multiple requests, first for the page itself, and then for each image, making an HTTP request to obtain the URL named in the IMG SRC tag. But that URL need not be an actual file, it can also be a dynamic URL that returns an image.
Therefore, you can create a .cfm file that does nothing more than serve images. The code would work like this:

SELECT imageData
FROM table

This file would be saved on the server, and the URL to it (with the image identifier passed as a URL parameter) can now be used as an IMG SRC like this:

And that’ll do it.

70 responses to “Serving Images From Databases”

  1. Vui Lo Avatar
    Vui Lo

    Great solution: simple and elegant! Never thought of applying dynamic URL onto IMG SRC.

  2. Roland Collins Avatar
    Roland Collins

    Save yourself a step and just output it using the cfcontent tag itself. πŸ™‚
    !— Determine image to retrieve —
    cfset …
    !— Get image —
    cfquery datasource="mydsn" name="image"
    SELECT imageData
    FROM table
    WHERE …
    !— Set MIME type to GIF or JPEG or … —
    cfcontent mimetype="image/gif" variable="#image.imageData#"

  3. Doug Hughes Avatar
    Doug Hughes

    Ben, are you sure you can do the <cfoutput> of the image data? Last I knew you couldn’t <cfoutput> binaray data and you had to use cfcontent.

  4. z3r0eff3ct (Matt) Avatar
    z3r0eff3ct (Matt)

    Why retrieve from database, then save to file, and then delete the file?
    What I did was retrieve the image from the database and then converted it to binary data. And it works that way.
    Best of all, is to not store it in the database to begin with.

  5. Roland Collins Avatar
    Roland Collins

    He’s not writing to disk first – he’s writing the binary stream directly to the browser.
    Sometimes, storing images in a database (for branding portal sites, for instance) makes more sense than saving in a disk repository. Either way, you have to store the data on disk and reference it somehow. (and NTFS and FAT are just databases anyway πŸ˜‰

  6. Ben Forta Avatar
    Ben Forta

    Yeah, should have had the variable in the cfcontent, I’ve updated the post. Next time I’ll test what I write first. πŸ˜‰ Thanks!

  7. bob Avatar

    Interesting… what is the best way to get image data into the database?

  8. Roland Collins Avatar
    Roland Collins

    <!— retrieve the binary representation of the image file //—>
    <cffile action="readBinary" file="#fileName#" variable="binImage">
    <cfquery datasource="myDSN">
    INSERT INTO images (img_id, img)
    VALUES (1, <cfqueryparam value="#binImage#" cfsqltype="cf_sql_blob">)

  9. bob Avatar

    I get this error when I try it…
    "java.lang.String" is not a supported variable type. The variable is expected to contain binary data.
    My image is stored in a SQL Server database using the image datatype.

  10. bob Avatar

    Figured it out- the initial example is missing pound signs on the variable.

  11. bob Avatar

    I haven’t found this method to be reliable I have found that some images display while other only half display…

  12. Bob Sifniades Avatar
    Bob Sifniades

    This blog page is now too wide for me to read without repeatedly scrolling left/right, or increasing my monitor resolution by a couple of settings.
    It looks like the PRE tag, and the long line starting with "This file would be saved…" are responsible.

  13. Ben Forta Avatar
    Ben Forta

    Bob, thanks, had an extraneous code tag, fixed it.

  14. Andy Rogers Avatar
    Andy Rogers

    Great Script.
    I had a problem with only portion of the large images being loaded. Thought it was a SQL Server issue. But I found the solution in the CF Admin. Had to Check the "Enable binary large object retrieval (BLOB)." option in the Advanced setting of my Datasource. Once I did that it worked like a charm.

  15. Darren Avatar

    Can you do this with PDF. That is, the blob contains a pdf document that needs to be saved to the disk on the server.

  16. Andy Rogers Avatar
    Andy Rogers

    You can use the code below to force the browser to download the file. You have to store the name of the file along with the BLOB in the database.
    <CFHEADER NAME="content-disposition" value="attachment; filename=#qryDocument.DocName#">
    <cfcontent type="application/unknown" variable="#qryDocument.Doc#">

  17. Abhijit Sanas Avatar
    Abhijit Sanas

    cfcontent tag does not allow me to use variable, it gives me deletefile, file, reset and type. So where do I specify image.imageData. Which tag do I use ?

  18. Jeff Gould Avatar
    Jeff Gould

    Fantastic idea, Ben. Worked perfectly all the way around. Now, does anyone know how to retrieve the properties of the image (especially height and width)? I need to put landscape pictures in one cell and portrait images in another.

  19. Bob Clingan Avatar
    Bob Clingan

    I’m trying this technique to store documents such as Word Files and PDFs. I’m doing an insert to the db using cfquery param with type cf_sql_blob to store the item in SQL Server database with the type set to image. When I pull it back out using the cfheader and cfcontent tags it only works for Word docs but other formats like PDF show as corrupt. I’m guessing the only reason word docs work is perhaps they are more forgiving. Any thoughts on what I might be doing wrong?

  20. bengen Avatar

    HI, I’m trying to insert a jpg file into a blob (oracle 9i, CF 6.1), using cfqueryparam cfsqltype="cf_sql_blob", but I get this CF error :
    "ByteArray objects cannot be converted to strings."
    here’s my code :
    <cffile action="readbinary" file="e7_3.jpg" variable="binImage">
    <cfquery name="PutPicture" datasource=’#DSN#’ username=’#LoginDB#’ password=’#PasswordDB#’>
    INSERT INTO CFR_PICT (CFR,PICTURE) VALUES (‘CFr1’, <cfqueryparam value="#binImage#" cfsqltype="cf_sql_blob">)</cfquery>

  21. Jeff Gould Avatar
    Jeff Gould

    Hi Bengen,
    Your INSERT statement looks fine, but there is probably a problem with the way the file is being loaded. Try this:
    <cffile action = "upload" destination = "c:CFusionMXtemp" nameConflict = "overwrite" fileField = "Form.FileToUpload" >
    <cffile action = "readbinary" file = c:CFusionMXtemp#cffile.serverfile#" variable = "binImage">
    <cfquery name="PutPicture" datasource=’#DSN#’ username=’#LoginDB#’ password=’#PasswordDB#’>
    insert into CFR_PICT (CFR,PICTURE)
    values (‘Cfr1’ , <cfqueryparam cfsqltype="cf_sql_blob" value="#binImage#">)

  22. bengen Avatar

    Thanks Jeff for your quick response, I’ve made one step beyond, but now I’ve this error message :
    Type : java.lang.ClassCastException
    Location :
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(
    Diagnostics : null null
    The problem seems to be in the sql type binary sended by CF.

  23. Jeff Gould Avatar
    Jeff Gould

    I’m not sure exactly what this is telling us. I use SQL Server exclusively. I define my column as an IMAGE type, which stores "Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes." Also, in CF Administrator, make sure you have "Enable binary large object retrieval (BLOB)." turned on for the #DSN# datasource.

  24. bengen Avatar

    Sorry for my bad english (I’m french mothertongue).
    So, "Enable binary large object retrieval" is already checked in CF admin. I use an Oracle 9i DB with a target field defined as Blob.
    For now, I use a PLSQL stored procedure to put a jpg into the blob field, it works correctly. But now I need to do that directly trough cfm pages.
    I can easily access to the file (using cffile "upload" and "readbinary") but once to store into the DB, even using cfqueryparam and cfsqltype="cf_sql_blob" I get the error message : "null null".
    Maybe is it a problem of non update CF or ORacle drivers. I’ve to check it with our Oracle admin and CF admin.
    Anyway thanks for your help, I wanted just to know if it was a known problem.

  25. bengen Avatar

    Sorry for my bad english (I’m french mothertongue).
    So, "Enable binary large object retrieval" is already checked in CF admin. I use an Oracle 9i DB with a target field defined as Blob.
    For now, I use a PLSQL stored procedure to put a jpg into the blob field, it works correctly. But now I need to do that directly trough cfm pages.
    I can easily access to the file (using cffile "upload" and "readbinary") but once to store into the DB, even using cfqueryparam and cfsqltype="cf_sql_blob" I get the error message : "null null".
    Maybe is it a problem of non update CF or ORacle drivers. I’ve to check it with our Oracle admin and CF admin.
    Anyway thanks for your help, I wanted just to know if it was a known problem.

  26. bengen Avatar

    ah ah, it finally works after the change of the dsn. We’ve tried with an odbc driver, the application runs slower but the blob storage works. So, it’s up to us to check and update a lot of drivers.

  27. Carla Scepaniak Avatar
    Carla Scepaniak

    Hi, I’m not all that familiar manipulating BLOBS, and I’m getting confused on how to display the images within a formatted cf page. I want to display a page which will run the query and show any images associated with that person’s account. I don’t understand the usage of the <img> tag. When I use cfcontent, it launches a page all its own. I don’t want it to do that. I want to put the pictures on the page with other information. I can upload and get the blobs fine, I’m just kinda lost from there. I want to pull the data, display it, and let the temporary file get deleted from the server, too, because I only want the image data inside the SQL database. Does that make sense? Can anyone help me? Thanks.

  28. ric Avatar

    I’m stuck…
    I want to store a pdf into a blob. Here’s the form for the user to retrieve the file to store:
    <cfform action="act_procImage.cfm">
    <TABLE BORDER=0 CELLSPACING=0 CELLPADDING=2 WIDTH="98%" bgcolor="ffffff" align="center">
    <!—/// Image ///—>
    <TD class="snblackfont" colspan="2">
    <cfinput type="File" name="image" size="30" maxlength="50" required="No">
    <br>Select an image to store
    <TD class="snblackfont" colspan="2">
    <INPUT TYPE="Submit" VALUE="Submit" class="button">
    <INPUT TYPE="Reset" VALUE="Reset" class="button">
    Here’s the action file to insert the image into the table:
    <cffile action="readBinary" file="#form.image#" variable="imageFile">
    <cfquery datasource="">
    INSERT INTO images
    <cfqueryparam value="#imageFile#" cfsqltype="cf_sql_blob">
    Here’s the error message I get:
    An error occurred when performing a file operation readBinary on file C:CFusionMX7runtimeserverscoldfusionSERVER-INFtempwwwroot-tmpaeris_net.pdf.
    The cause of this exception was: C:CFusionMX7runtimeserverscoldfusionSERVER-INFtempwwwroot-tmpaeris_net.pdf (The system cannot find the file specified).
    It appears that the file’s path is necessary. What path do I need to define and how do I do it?

  29. Jayesh Avatar

    Being a part of Coldfusion QA team, I found this discussion very much useful and found soluntions to my problems which I am facing while implementing automation of the new <CFImage> feature.
    Thanks!! πŸ™‚

  30. Mike Kaminsky Avatar
    Mike Kaminsky

    Ric – you are trying to read a file that has not yet been uploaded to the server. You first need to use the following to get the file from your local machine onto the server.
    <cffile action="upload" filefield="image" destination="/uploads" nameconflict="OVERWRITE">
    cffile can then use a relative directory from whatever you specified as the destination directory. Thus, the above tag would upload the source file to a temporary directory located at …
    From here you can issue the "readBinary" tag like so …
    <cffile action="readBinary" file="/uploads/#form.image#" variable="imageFile">

  31. John Mill Avatar
    John Mill

    <p>The methodology described here works well, however, the use of cfcontent to produce a binary stream means that either one uses a secondary cfm file as the src of an img tag, or one directly streams it to the browser. The former, as delineated in the main article, means that each image will require a separate database query. The latter means that only a single image can be displayed on the page.</p><p>Does anyone know of a way where one could do a single query on a page, retreiving several records with blob image data, and then, outputing over the query, display all of the images?</p><p>For instance:<br>
    <cfquery datasource="dsnName" name="getBlobs"><br>
    select blobData<br>
    from tblBlobs<br>
    where ID between 1 and 10<br>
    <cfcontent type=’image/jpeg’ variable=’#getBlob.thumbImage#’><br>
    Any help would be appreciated, TIA

  32. Carla Scepaniak Avatar
    Carla Scepaniak

    Yes, I’ve done it on one of my websites. I hope this is what you were talking about in your question. Here’s what I did, the database has two binary fields, one for the full size image and the other for the thumbnail (of course I resize and compress everything that’s uploaded to fit within standards I devised). First the query, in this case we’ll do the thumbnails:
    <cflock name="getthosepics" timeout="5" throwontimeout="yes" type="readonly">
    <cfquery name="getphotos" datasource="#DSN#">
    select clientID, photofilename, binarythumbnailphotofield
    from PictureTable
    where clientID = #session.clientID#
    Then the output loop, I only allow up to 4 photos to be input at any time for each person, the check for the allowable count is done on upload or account edit. I use a plain cfoutput here because I’m actually inside another query output on the live page:
    <cfloop from="1" to="4" index="x">
    <td align="center" valign="middle" style="width: 25%; border: 1px solid ##333399;">
    <cfif getphotos.RecordCount gte x>
    <cfif not FileExists("#myDrivePhotoLocation##getphotos.photofilename[x]#_thumbnail.jpg")>
    <cffile action="write" file="#myDrivePhotoLocation##getphotos.photofilename[x]#_thumbnail.jpg" output="#toBinary(getphotos.binarythumbnailphotofield[x])#">
    <!– if no picture, output some other graphic –>
    <img src="images/nophotohereicon.gif" border="0" width="100" height="100" alt="" />
    Then, upon whatever following function the user performs, I delete the image file which was written to keep the directory cleaned out where the photos are temporarily written and located.
    I hope this helps, it works for me, anyway.

  33. John Mill Avatar
    John Mill

    Thanks for the comment Carla! Yeah, I have used temporary files before, what would be cool is if it were possible to stream the binary representation into the src of an image file without the need to write out the temprorary image file. After banging my head against it for a while I am thinking that the only way to accomplish this would be to code it in Java.

  34. Marshall Avatar

    Forta, you rule. I am, however, not completely clear on what should go into the cfset tag if you wanted that to be dynamic. I.E. I am trying to use this idea to create a random picture generator for my homepage, but it isn’t working out too well. Here’s what I have so far:
    <cfquery datasource="#DSN#" name="getimage">
    SELECT *
    FROM images
    ORDER BY rand()
    LIMIT 1;
    <cfcontent mimetype="image/gif" variable="#getimage.image#">
    I did make one assumption when writing this, and that is that the variable is the actual image itself, I was never clear on that part of the code either. Any ideas?

  35. Tom Kitta Avatar
    Tom Kitta

    The CFCONTENT way works very well, just remember two things:
    a) you probably should make sure that all content before cfcontent tag is discarded
    b) by default Data Sources in CF are limited to 64k. You may need to increase that limit or create new DS, just for Blobs (there were performance issues with DS that were blob enabled, thus 2 DS)

  36. Carla Scepaniak Avatar
    Carla Scepaniak

    Good luck on the Java, John! I’m just happy I managed to get mine to work like I wanted. It’s in use on
    And thank you, Tom, for your suggestion of the separate dsn for the blob fields. I will remember that if I see performance becoming an issue. That was very helpful.

  37. Carla Scepaniak Avatar
    Carla Scepaniak

    Okay, this might be a stupid question, but since this is my first experience working with blobs, does the size of a blob data field equate the size of the image? For example, if I uploaded a 25K image, does it take up 25K of space in the blob field of a SQL database?

  38. PuddinPop86 Avatar

    I am pretty new at coding in Coldfusion and I have a problem when uploading pictures…It saves the picture to a folder on the website and then enters the name into the database. But the name it enters is like c://temp/imagename.tmp which isnt the real name of the image. When I go to retrieve the images I can’t. The code is as follows;
    <cfif isdefined("form.picture1")>
    <span class="style3">
    <cffile action="upload" filefield="picture1" destination="c:inetpubwwwrootcargohelperimages" nameconflict="overwrite" accept="image/*" >
    <cfset uploadedfile = "c:inetpubwwwrootcargohelperimages#file.serverfile#">
    <div style="background-color:ffffff">
    <p align="center" class="style3">Thank You For Submitting Your Invention!</p>
    <table width="450" border="1" bgcolor="#FFFFFF" align="center" cellpadding="6" cellspacing="0">
    <tr valign="top">
    <td bgcolor="#FFFFFF" class="style4">
    <cfoutput><img src="images/#file.serverfile#"></cfoutput></td>
    <td bgcolor="#FFFFFF" class="style4"><cfoutput>#file.ServerFile#</cfoutput>
    <br />
    <cfoutput><input name="Picture" type="hidden" value="#form.picture1#"></cfoutput>
    <cfquery name="inventorinsert" datasource="cargohelpertwo">
    Insert Into Invention(fname, lname, email, inventionname, description, picture)
    Values(‘#form.fname#’, ‘#form.lname#’, ‘’, ‘#form.inventionname#’, ‘#form.description#’, ‘#form.picture1#’)
    Can anyone help me?

  39. Scott Avatar

    I think the problem you are encountering is that you are using the input field’s value as what you are inserting into the picture field, and I think it’s value is usually the location of the temp file on the server before it gets uploaded. You’ve probably already figured this out by now though, but in case you hadn’t, I think your Insert should look like this:
    Insert Into Invention(fname, lname, email, inventionname, description, picture)
    Values(‘#form.fname#’, ‘#form.lname#’, ‘’, ‘#form.inventionname#’, ‘#form.description#’, ‘#uploadedfile#’)
    Now…I’m trying to insert the actual picture into a MS-SQL table using Coldfusion and Stored Procedures are preferred for security purposes. We are still on version 5 on most of our CFServers though we plan on updating to MX soon, I need this to work now in 5 if possible. Here is the code I am using to get the file into a binary variable:
    <cffile action="upload" filefield="form.browse" destination="D:AppsColdfusionApplicationsChangeControlFormUpload" nameconflict="OVERWRITE">
    <cffile action="readBinary" file="D:AppsColdfusionApplicationsChangeControlFormUpload#cffile.clientFileName#.#cffile.clientFileExt#" variable="uploadFile">
    <cfif cffile.fileExisted EQ "YES">
    <cfquery DataSource="ChangeControl" NAME="uploadFile">
    { call pr_uploadFile (#FormID#,
    ‘#cffile.clientFileExt#’) }
    And this is the error I am getting:
    Error Occurred While Processing Request
    Error Diagnostic Information
    Expression result cannot be converted to a string
    Expressions used inside tags like CFOUTPUT, CFQUERY, CFMAIL, etc. must evaluate to a value that can be converted to a string for output or dynamic text accumulation purposes. Complex objects, such as queries, arrays, and COM/DCOM objects, cannot be represented as strings.
    The error occurred while processing an element with a general identifier of (#uploadFile#), occupying document position (19:11) to (19:22).
    Oh, and here is the basic idea of the stored procedure:
    CREATE PROCEDURE blah blah
    @FormID int,
    @SectionID int,
    @Attachment image,
    @MimeType varchar,
    @FilePrefix varchar,
    @FileSize int,
    @FileExt char
    INSERT INTO tblAttachments
    Any recommendations?
    Thanks in advance for any help πŸ™‚

  40. Scott Avatar

    Nevermind. I figured out the problem.
    I had to change the field in the database to type: Text
    Then format the data with ToBase64() before being inserted. Then when I retrieve the data I have to use ToBinary() on that data to convert it back to its original format. This is in CF 5 by the way.
    I also ran into another problem. Any files over 64,999k were generating errors upon using ToBinary on them when in Base64 format, the problem was the Long Text Retrieval setting for the ODBC in Coldfusion Administrator. I had to check the box to not have a limit for long text retrieval, and had to restart Coldfusion Services for the change to take effect.
    Hope this helps someone. Now my problem is that I would prefer to not have to write the files to the drive for retrieving/viewing purposes so I don’t have to worry about cleaning up files later. I would like to just display the content with cfcontent…but I’m finding more problems with the same error I posted in my last message about Binary information not being output, and Null characters when I try to convert it to a string…

  41. Matt Avatar

    Does anybody know how I can use the output from the <cfcontent> tag and save it to a folder on my site? my code looks like this below. it works for pulling the picture out of the database but I want to take the image and save a copy to a folder for manipulation…
    <cfquery name="clients" datasource="Pi4_Live">
    <cfcontent type="image/jpeg" variable="" >

  42. Carla Scepaniak Avatar
    Carla Scepaniak

    I’m not the most experienced CFer out here, but I don’t think you can do that with cfcontent….but then again, what do I know haha. However, I have an app where I take the binary photo data from a SQL db and write it to a directory, then I clean it up after manipulation. This example is a loop from a query in the event there are more than 1 matching photos from the query (in this case, 4 is the highest possible number):
    <cfloop from="1" to="4" index="x">
    <cfif not FileExists("#application.photoDest##photo.myfilename[x]#.jpg")>
    <cffile action="write" file="#application.photoDest##photo.myfilename[x]#.jpg" output="#toBinary(photo.mybinaryphoto[x])#">
    I hope this helps.

  43. Matthew Bogdanov Avatar
    Matthew Bogdanov

    In response to: Bob Clingan | 10/10/05 1:31 PM
    Here’s another site that’s done an excellent job on explaining the process of uploading a document or image into a database and retrieving it.

  44. Ed HIghfield Avatar
    Ed HIghfield

    This works well for CF 7 but we’re running Coldfusion 6.1 here and <CFCONTENT> won’t accept the variable attribute. Anyone know how to display the image in ColdFusion 6?

  45. Rob Ceelen Avatar
    Rob Ceelen

    We use this method for a year now and we starting to have problems. The load for the coldfusion server is very big while retrieving the binary data. We want to store files up to 100Mb but this causes a 80% ~100% cpu load for almost a minute. Multiple requests for these files result in a crash…. We use windows 2003 with coldfusion 7.02.
    Is there any way to retrieve the BLOB out of oracle without the load for coldfusion?

  46. Jennifer Avatar

    I know this is an old topic, but I was wondering if anyone had information regarding accessing BLOB data with CF MX 7 and Advantage Database 8.1
    Using a simple CFQUERY to select the blob data (not display it) is throwing an error.
    <cfquery name="pull_blob" datasource="mydatasource">
    Select myBlobCol
    From myAdvantageDatabaseTable
    [Macromedia][SequeLink JDBC Driver]An internal error occurred.

    I know CFMX has a problem doing select/update/delete operations on data type blob with an Oracle OCI JDBC driver. Is the same true for Advantage Database when I am connecting to my database using an ODBC Socket.

  47. Jim Dutcher Avatar
    Jim Dutcher

    I have incorporated this code and it works great! However, my clients now want the capability to upload multiple images at one time. I can upload multiple files into a table at one time, but when I try to load images, I get errors.
    Any help would be greatly appreciated.

  48. dylan Avatar

    re: John Mill
    Did anyone ever figure out how to use multiple binary images, without writing multiple queries?

  49. Andy Avatar

    I created a form with a file uploader that submits to the page the binImage var on it like this:
    <cffile action="readBinary" file="#form.fileNameField#" variable="binImage">
    <cfquery datasource="enet_picts">
    INSERT INTO image (img_id, img)
    VALUES (1, <cfqueryparam value="#binImage#" cfsqltype="cf_sql_blob">)
    I’m getting this error no matter what file I attempt to upload:
    C:CFusionMX7runtimeserverscoldfusionSERVER-INFtempwwwroot-tmp200021441-001.jpg (The system cannot find the file specified).
    Please help a greenhorn out, Ben!

  50. Andy Avatar

    The greenie got lucky! I got it right away. ColdFusion was looking in the webinf dir so I provided it with the complete path to my picts directory like this:
    <cffile action="readBinary" file="C:Inetpubwwwrootelectriciansnet.compictsFireworks#form.fileNameField#" variable="binImage">
    I know there is a way to do a mapping in cfadmin but I’ve never done it before. Besides, my picts dir has several sub dirs which means that I will manually have to change this path every time. There’s probably a way to accomplish the same thing with a url path among other sophisticated methods but I don’t know any of them! Any suggestions?

Leave a Reply