AdobeStock_455007340

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
WHERE ...




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. Ng Avatar
    Ng

    Hello everyone,
    I know is very long time ago but I am still having similair problem with after update ("Enable binary large object retrieval (BLOB)" enabled for the DSN ).
    One of the post by bengen talk about update driver, I am still very confusion, maybe I am not familiar with that. Can Bengen explaning more detail how to do that or anyone can help me with?
    Thank you and very appreciated.
    Ng

  2. Andy Avatar
    Andy

    Ng, which version of CF are you using because I did not have to enable anything.
    BTW, I have learned much since my last post and I have found out that I need to use a separate DSN for my BLOBing which I’m about to do because its true- serving binary images with coldfusion takes up a huge amount of resources. To see mine in action go to http://www.electriciansnet.com and use 33617 as your input (the images are in the movie)then open a new window and use 88888 and you will see how CF will drop the ball. But hurry because I’m about to make the DSN changes! I’m still somewhat of a "greenie" but I am around to help if I can. Regards, Andy

  3. Ng Avatar
    Ng

    Thanks Andy for responseding my post.
    My coldfusion is version 7, oracle version 9i.
    Here is my story, may be you can help me different way to do this.
    All I want to do is provide to the user to upload their file into my server, do not need to save in oracle database, maybe a copy from local drive to the server. Also all user can able to retrieve back for open and view.
    A lot of website doing that but some how every body have a hard time on upload and download file, that is including me.
    Seam like you are having fun experiencing in Blob, I will check out your website. Good luck and thanks.
    Regards,
    Ng

  4. sam Avatar
    sam

    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.
    regards,
    http://www.anarsist.org/

  5. Shift4SMS Avatar
    Shift4SMS

    Anyone have any issues inserrting the binary image into the table? I occasionally receive "Error Executing Database Query.: [Macromedia][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated" when inserting or updating records in my table and this was not an issue until I added the image column. I’m using MX7 and MS-SQL 2000 & 2005. The "big blob" setting does not have any affect on this particular error.
    The really strange part is when the error occurs, clicking on REFRESH and reposting the exact same data, which reexecutes the exact same query, executes without issue.
    Here’s my code:
    <cfif IsDefined("FORM.Logo") and FORM.Logo is not "">
    <cffile accept="image/gif,image/jpeg,image/png,image/x-png" action="upload" destination="#APPLICATION.UploadDirectory#" filefield="Logo" nameconflict="makeunique">
    <cfset filename="#cffile.ServerDirectory##cffile.ServerFile#">
    <cffile action="readBinary" file="#filename#" variable="BinImgLogo">
    <cffile action="delete" file="#filename#">
    <cfswitch expression="#cffile.ContentType#/#cffile.ContentSubType#">
    <cfcase value="image/gif">
    <cfset ATTRIBUTES.LogoType="GIF">
    </cfcase>
    <cfcase value="image/jpeg">
    <cfset ATTRIBUTES.LogoType="JPG">
    </cfcase>
    <cfcase value="image/png,image/x-png">
    <cfset ATTRIBUTES.LogoType="PNG">
    </cfcase>
    </cfswitch>
    <cfquery datasource="#REQUEST.DataSource#" username="#REQUEST.UserName#" password="#REQUEST.Password#">
    update [Site]
    set
    LogoType = <cfqueryparam value="#ATTRIBUTES.LogoType#" cfsqltype="CF_SQL_VARCHAR">,
    Logo = <cfqueryparam value="#BinImgLogo#" cfsqltype="CF_SQL_BLOB">
    where
    AccountID = <cfqueryparam value="#Val(ATTRIBUTES.AccountID)#" cfsqltype="CF_SQL_INTEGER">
    and SiteID = <cfqueryparam value="#Val(ATTRIBUTES.SiteID)#" cfsqltype="CF_SQL_INTEGER">
    </cfquery>
    </cfif>
    Any ideas?

  6. JJ Avatar
    JJ

    shouldn’t there be a side note to make sure that all content before cfcontent tag is discarded
    <a href="http://www.seekbc.ca"</a&gt;

  7. jabba Avatar
    jabba

    I am considering writing some books. The first book would be CFTipsPlus: Project 1 – Intranet. I would go through my thoughts, opinions, and Ideas for coding as well as creating an intranet. This book would have a lot of beginner stuff but who knows what you might learn. Please let me know what type of things you might like to know in a ColdFusion Book that you are not getting now. Also due to CFTipsPlus being more than just ColdFusion I hope to include CSS, Design, Documentation, and who knows whatever I am thinking at the time.
    BR,
    http://www.mp3flavour.com

  8. Michael Avatar
    Michael

    Ben,
    I’ve been using your approach here for a couple years now, and it works great… except for when the template calling "getImage.cfm" is trying to generate a word document instead of an HTML page.
    For example, this works:

    <body>

    <img src="/getImage.cfm?imageid=123">

    </body>

    ———————————————————————-
    But this doesn’t:

    <cfcontent type="application/msword">
    <cfheader name="Content-Disposition" value="filename=myfilename.doc"
    <body lang=EN-US>

    <img src="/getImage.cfm?imageid=123">

    </body>

    ———————————————————————-
    In the later example, ColdFusion generates the word document as expected; meaning string, numeric, and other values pulled from the database populate the document just fine… but images don’t… in its place, I get a placeholder frame and the MSWord generated "alt" text: "The linked image cannot be displayed. The file may have been moved, renamed, or deleted. Verify that the link points to the correct file and location".
    This one has me stumpped… could it possibly be the nesting of CFCONTENT tags (the one telling the template to generate a Word document, along with the one in getImage.cfm)?
    -Michael

  9. Michael Avatar
    Michael

    before anyone comments, the CFHEADER tag not being closed above (">") is just a typo… it’s correct in the actual code, and still doesn’t work. 🙁
    -M

  10. Rachel Maxim Avatar
    Rachel Maxim

    @Michael, it looks like you’re setting the mime type of the content you’re serving to application/msword. Thus the browser will treat it as a Word file and try to open it in Word. The mime type should be set to the mime type of the image you want to downl

  11. Michael Avatar
    Michael

    Rachel,
    Thanks for the comment, and sorry if my original post was unclear…
    In "getImage.cfm", the mime type IS set to ‘image/jpeg’ as one would expect (per Ben’s original code). However, the CFM template CALLING "getImage.cfm" (via an <IMG src=…> tag) is not generating an HTML page, but rather an MS Word document… thus the reason this ‘outer’ template uses a CFCONTENT tag who’s mime type is set to ‘application/msword’.
    So in essence, I’m nesting CFCONTENT tags… one in the ‘outer’ template that’s generating a word document, and one in the ‘inner’ template (getImage.cfm) to render an image inside that word document that comes from my database.
    Like I said… the whole process works fine when the outer template is generating HTML, but not when it’s generating a word document. 🙁
    -Michael

  12. Julia Avatar
    Julia

    n 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.
    Regards,
    http://www.onlineflashgames.org

  13. Super Mario Avatar
    Super Mario

    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 ?

  14. -=JFK=- Avatar
    -=JFK=-

    All. I have been using this blog post to develop my dynamic db image hosting solution. I was having trouble on our ColdFusion server getting images. When I tried to get BIG images out of the database… it would only return the first 64,000 bytes of the image.
    In your server ColdFusion Administrator interface, you have to make sure your "Data Source" has BLOB ("Enable binary large object retrieval (BLOB).") selected. I turned this on for my images and then they were being retrieved fully from our database.
    Respects,
    -=JFK=-

  15. Sandra Avatar
    Sandra

    I 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.
    Best Regards,
    http://www.mobiletrend.net

  16. Tony Avatar
    Tony

    Sorry to necro this thread from the grave, but…
    <img src="…"> is not working for me.
    I have my main file ‘testdhs.cfm’:
    <cfquery name="rs1" datasource="dhsdb">
    SELECT IMAGE
    FROM dbo.CASEIMG
    WHERE IMAGE_ID = #URL.imageid#</cfquery>
    <cfcontent type="image/tiff" variable="#rs1.IMAGE#">
    Then, in the page that will display the tiff,
    <img src="/cms_temp/testdhs.cfm?imageid=0" />
    This page displays the broken image icon "red X". However, when I check the properties of the broken image, it displays the proper URL, which when copied and pasted into the browser’s address bar, opens a dialog box asking if I want to open or save the .tif . If I open it, a viewer pops up and displays the image I wanted.
    So, why doesn’t the img src work? Do I need to include something else on that page?

  17. Tony Avatar
    Tony

    Nevermind, just read that IE won’t support TIFF without a plugin.
    I have decided to use OBJECT tags to utilize QuickTime. The TIFF now displays in my browser.
    If anyone else has a cleaner, better way to display TIFF’s inside a browser, post away. thanks

  18. Alex Mavity Avatar
    Alex Mavity

    This is excellent, thanks for the detailed coding work, found it very useful indeed.

  19. plutarco Gonzalez Avatar
    plutarco Gonzalez

    Quoted:" 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.
    # Posted By Andy Rogers | 6/29/05 1:55 PM"
    Thank you a lot Andy

  20. Randy Avatar
    Randy

    It is now March 9th, 2019…and believe it or not, a post by Carla on Mar 30, 2006, 04:50 PM in this thread helped me move my images from the database to JPG files on the hard drive.
    I had to make a few minor changes to it but I was really surprised when it pulled ALL the jpg blobs out of the database and created working JPG files from them all.
    Sometimes OLD posts are still Golden, MORE than a decade later

Leave a Reply