Blog

6Jun
2005
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:

view plain print about
1<!--- Determine image to retrieve --->
2<cfset ...>
3
4<!--- Get image --->
5<cfquery datasource="mydsn" name="image">
6SELECT imageData
7FROM table
8WHERE ...
9</cfquery>
10
11<!--- Set MIME type to GIF or JPEG or ... --->
12<cfcontent variable="image.imageData" ... >

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:

view plain print about
1<img src="/getImage.cfm?imageid=123">

And that'll do it.

Comments (69)



  • Vui Lo

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

    #1Posted by Vui Lo | Jun 6, 2005, 11:43 PM
  • 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 ...
    /cfquery

    !--- Set MIME type to GIF or JPEG or ... ---
    cfcontent mimetype="image/gif" variable="#image.imageData#"

    #2Posted by Roland Collins | Jun 7, 2005, 12:16 AM
  • 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.

    Doug

    #3Posted by Doug Hughes | Jun 7, 2005, 12:23 AM
  • 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.

    #4Posted by z3r0eff3ct (Matt) | Jun 7, 2005, 12:58 AM
  • Roland Collins

    Matt,

    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 ;)

    #5Posted by Roland Collins | Jun 7, 2005, 01:10 AM
  • 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!

    #6Posted by Ben Forta | Jun 7, 2005, 08:58 AM
  • bob

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

    #7Posted by bob | Jun 7, 2005, 01:15 PM
  • Roland Collins

    bob:

       <!--- 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">)
       </cfquery>

    #8Posted by Roland Collins | Jun 7, 2005, 01:48 PM
  • bob

    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.

    #9Posted by bob | Jun 8, 2005, 12:32 PM
  • bob

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

    #10Posted by bob | Jun 8, 2005, 01:17 PM
  • bob

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

    #11Posted by bob | Jun 9, 2005, 03:02 PM
  • 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.

    #12Posted by Bob Sifniades | Jun 10, 2005, 06:51 AM
  • Ben Forta

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

    #13Posted by Ben Forta | Jun 15, 2005, 09:18 PM
  • 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.



    #14Posted by Andy Rogers | Jun 29, 2005, 01:55 PM
  • Darren

    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.

    #15Posted by Darren | Jul 19, 2005, 01:13 PM
  • 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#">

    #16Posted by Andy Rogers | Jul 20, 2005, 12:39 PM
  • 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 ?

    #17Posted by Abhijit Sanas | Aug 16, 2005, 02:12 PM
  • 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.

    #18Posted by Jeff Gould | Sep 12, 2005, 04:30 PM
  • 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?

    #19Posted by Bob Clingan | Oct 10, 2005, 01:31 PM
  • bengen

    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>

    #20Posted by bengen | Oct 12, 2005, 08:49 AM
  • 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:\CFusionMX\temp\" nameConflict = "overwrite" fileField = "Form.FileToUpload" >

    <cffile action = "readbinary" file = c:\CFusionMX\temp\#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#">)
    </cfquery>



    #21Posted by Jeff Gould | Oct 12, 2005, 09:36 AM
  • bengen

    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(OraclePreparedStatement.java:3070)

    Diagnostics : null null

    The problem seems to be in the sql type binary sended by CF.





    #22Posted by bengen | Oct 12, 2005, 10:04 AM
  • 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.

    #23Posted by Jeff Gould | Oct 12, 2005, 10:19 AM
  • bengen

    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.

    #24Posted by bengen | Oct 12, 2005, 10:50 AM
  • bengen

    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.

    #25Posted by bengen | Oct 12, 2005, 11:38 AM
  • bengen

    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.

    Thanks.

    Ben.

    #26Posted by bengen | Oct 12, 2005, 11:46 AM
  • 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.

  • ric

    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>
    </TR>

    <TR>
       <TD class="snblackfont" colspan="2">
          <INPUT TYPE="Submit" VALUE="Submit" class="button">
          <INPUT TYPE="Reset" VALUE="Reset" class="button">
       </TD>
    </TR>
    </TABLE>
    </cfform>

    Here's the action file to insert the image into the table:

    <cffile action="readBinary" file="#form.image#" variable="imageFile">

    <cfquery datasource="#request.site.mainDSN#">
       INSERT INTO images
          (
          imageID,
          image
          )

       VALUES
          (
          1,
          <cfqueryparam value="#imageFile#" cfsqltype="cf_sql_blob">
          )
    </cfquery>

    Here's the error message I get:
    An error occurred when performing a file operation readBinary on file C:\CFusionMX7\runtime\servers\coldfusion\SERVER-INF\temp\wwwroot-tmp\aeris_net.pdf.
    The cause of this exception was: java.io.FileNotFoundException: C:\CFusionMX7\runtime\servers\coldfusion\SERVER-INF\temp\wwwroot-tmp\aeris_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?

    #28Posted by ric | Feb 9, 2006, 01:10 PM
  • Jayesh

    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!! :-)

    #29Posted by Jayesh | Feb 10, 2006, 02:13 AM
  • 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 ...

    C:\CFusionMX7\runtime\servers\coldfusion\SERVER-INF\temp\wwwroot-tmp\uploads

    From here you can issue the "readBinary" tag like so ...

    <cffile action="readBinary" file="/uploads/#form.image#" variable="imageFile">

    #30Posted by Mike Kaminsky | Feb 21, 2006, 05:35 PM
  • 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>
    </cfquery><br>
    <cfcontent type='image/jpeg' variable='#getBlob.thumbImage#'><br>
    </p>
    Any help would be appreciated, TIA

    #31Posted by John Mill | Mar 30, 2006, 03:45 PM
  • 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#
    </cfquery>
    </cflock>

    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:

    <cfoutput><table><tr>
    <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])#">
    </cfif>
    <cfelse>
    <!-- if no picture, output some other graphic -->
    <img src="images/nophotohereicon.gif" border="0" width="100" height="100" alt="" />
    </cfif>
    </td>
    </cfloop>
    </tr></table></cfoutput>

    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.

    #32Posted by Carla Scepaniak | Mar 30, 2006, 04:50 PM
  • 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.

    #33Posted by John Mill | Mar 31, 2006, 09:01 AM
  • Marshall

    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;
    </cfquery>

    <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?

    #34Posted by Marshall | Apr 1, 2006, 03:19 PM
  • 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)

    #35Posted by Tom Kitta | Apr 4, 2006, 10:37 AM
  • 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 www.alasafe.gov.

    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.

  • 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?

  • PuddinPop86

    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:\inetpub\wwwroot\cargohelper\images\" nameconflict="overwrite" accept="image/*" >
    <cfset uploadedfile = "c:\inetpub\wwwroot\cargohelper\images\#file.serverfile#">
    <br>
    <br>
    </span>
    <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>
    </td>
    </tr>
    </table>
    <br />
    </div>
    </cfif></td>
    </tr>
    </table>
    <p>&nbsp;</p>
    <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.email#', '#form.inventionname#', '#form.description#', '#form.picture1#')
    </cfquery>

    Can anyone help me?

    #38Posted by PuddinPop86 | Aug 15, 2006, 11:40 AM
  • Scott

    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.email#', '#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:\Apps\ColdfusionApplications\ChangeControlForm\Upload\" nameconflict="OVERWRITE">

    <cffile action="readBinary" file="D:\Apps\ColdfusionApplications\ChangeControlForm\Upload\#cffile.clientFileName#.#cffile.clientFileExt#" variable="uploadFile">

    <cfif cffile.fileExisted EQ "YES">
       <cfquery DataSource="ChangeControl" NAME="uploadFile">
          { call pr_uploadFile (#FormID#,
                 #SectionID#,
                 #uploadFile#,
              '#cffile.contentType#/#cffile.contentSubType#',
                 '#cffile.clientFileName#',
                 #cffile.fileSize#',
                 '#cffile.clientFileExt#') }
       </cfquery>
    ....
    </cfif>

    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

    AS

    INSERT INTO tblAttachments
    (
       FormID,
       SectionID,
       Attachment,
       MimeType,
       FilePrefix,
       FileSize,
       FileExt
    )

    VALUES
    (
       @FormID,
       @SectionID,
       @Attachment,
       @MimeType,
       @FilePrefix,
       @FileSize,
       @FileExt
    )

    Any recommendations?

    Thanks in advance for any help :)

    #39Posted by Scott | Sep 1, 2006, 03:54 PM
  • Scott

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

    #40Posted by Scott | Sep 5, 2006, 10:38 AM
  • Matt

    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">
    SELECT CLIENTID, FIRSTNAME, LASTNAME, CELLPHONE, OFFICENAME, EMAIL1, PHOTO, LOGO
          FROM CLIENTINFO, COMPANIES
          WHERE CLIENTID = #URL.CLIENTID#
          AND CLIENTINFO.COMPANYID = COMPANIES.COMPANYID
    </cfquery>


    <cfcontent type="image/jpeg" variable="#clients.photo#" >

    #41Posted by Matt | Jan 4, 2007, 06:39 PM
  • 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])#">
    </cfif>
    </cfloop>

    I hope this helps.

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

    http://www.dougboude.com/blog/1/2006/09/DatabaseOr...

    #43Posted by Matthew Bogdanov | Jan 17, 2007, 06:36 PM
  • 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?

    #44Posted by Ed HIghfield | Jan 24, 2007, 10:41 AM
  • 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?

  • Jennifer

    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
    </cfquery>

    [Macromedia][SequeLink JDBC Driver]An internal error occurred.
    ...
    VENDORERRORCODE 7363
    SQLSTATE HY000


    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.

    #46Posted by Jennifer | Mar 14, 2007, 09:05 AM
  • 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.

    #47Posted by Jim Dutcher | Apr 18, 2007, 11:38 AM
  • dylan

    re: John Mill

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

    #48Posted by dylan | Jun 11, 2007, 12:50 PM
  • Andy

    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">)
    </cfquery>



    I'm getting this error no matter what file I attempt to upload:

    C:\CFusionMX7\runtime\servers\coldfusion\SERVER-INF\temp\wwwroot-tmp\200021441-001.jpg (The system cannot find the file specified).

    Please help a greenhorn out, Ben!

    #49Posted by Andy | Jul 2, 2007, 01:07 AM
  • Andy

    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:\Inetpub\wwwroot\electriciansnet.com\picts\Fireworks\#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?

    #50Posted by Andy | Jul 2, 2007, 01:24 AM
  • 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

    #51Posted by Ng | Sep 18, 2007, 12:00 PM
  • 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

    #52Posted by Andy | Sep 19, 2007, 12:15 AM
  • 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

    #53Posted by Ng | Sep 19, 2007, 09:15 AM
  • 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/

    #54Posted by sam | Nov 27, 2007, 02:11 PM
  • 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?

    #55Posted by Shift4SMS | Dec 14, 2007, 12:29 PM
  • 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>;

    #56Posted by JJ | Jan 8, 2008, 11:57 PM
  • 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

    #57Posted by jabba | Jan 22, 2008, 06:24 AM
  • 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

    #58Posted by Michael | Jan 24, 2008, 01:43 PM
  • 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

    #59Posted by Michael | Jan 24, 2008, 01:49 PM
  • 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 download - ie image/jpeg. If you need to dynamically determine the mime type, there is a function on CFLib that maps it all - http://www.cflib.org/udf.cfm?ID=1123.
    Maybe I'm missing something in code that you haven't posted, but if not that appears to be the problem. HTH.

    #60Posted by Rachel Maxim | Feb 20, 2008, 11:38 AM
  • 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

    #61Posted by Michael | Feb 21, 2008, 10:10 AM
  • 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

    #62Posted by Julia | May 11, 2008, 11:01 AM
  • 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 ?

  • -=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=-

    #64Posted by -=JFK=- | Jul 31, 2008, 02:58 PM
  • 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

    #65Posted by Sandra | Aug 5, 2008, 03:08 PM
  • 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?

    #66Posted by Tony | Jul 22, 2009, 03:40 PM
  • 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

    #67Posted by Tony | Jul 22, 2009, 04:29 PM
  • Alex Mavity

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

  • 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