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.
Serving Images From Databases
70 responses to “Serving Images From Databases”
-
Great solution: simple and elegant! Never thought of applying dynamic URL onto IMG SRC.
-
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#" -
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 -
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. -
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 😉 -
Yeah, should have had the variable in the cfcontent, I’ve updated the post. Next time I’ll test what I write first. 😉 Thanks!
-
Interesting… what is the best way to get image data into the database?
-
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> -
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. -
Figured it out- the initial example is missing pound signs on the variable.
-
I haven’t found this method to be reliable I have found that some images display while other only half display…
-
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. -
Bob, thanks, had an extraneous code tag, fixed it.
-
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. -
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.
-
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#"> -
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 ?
-
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.
-
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?
-
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> -
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#">)
</cfquery> -
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. -
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.
-
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. -
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. -
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. -
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.
-
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:CFusionMX7runtimeserverscoldfusionSERVER-INFtempwwwroot-tmpaeris_net.pdf.
The cause of this exception was: java.io.FileNotFoundException: 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? -
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!! 🙂 -
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:CFusionMX7runtimeserverscoldfusionSERVER-INFtempwwwroot-tmpuploads
From here you can issue the "readBinary" tag like so …
<cffile action="readBinary" file="/uploads/#form.image#" variable="imageFile"> -
<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 -
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. -
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.
-
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? -
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) -
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 http://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. -
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?
-
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#">
<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> </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? -
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: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#,
#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 🙂 -
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… -
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#" > -
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. -
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/DatabaseOriented-Document-Management.cfm -
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?
-
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? -
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. -
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. -
re: John Mill
Did anyone ever figure out how to use multiple binary images, without writing multiple queries? -
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:CFusionMX7runtimeserverscoldfusionSERVER-INFtempwwwroot-tmp200021441-001.jpg (The system cannot find the file specified).
Please help a greenhorn out, Ben! -
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