Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at NCDevCon 2011 (Raleigh, NC) with: Anthony Mineo

Ask Ben: Streaming Binary Data From The Database (BLOB) To The User Using ColdFusion

By Ben Nadel on

Hi Ben, I must have searched our old friend Mr. Google and your site for nearly an hour without luck - so I figured I better ASK BEN. I normally retrieve binary object from a database by first writing them to the server's disk and then href-ing to that file. My question is, how do I retrieve a file directly from the database without the intermediary step of writing to the server first? Thanks in advance!

To be honest, I have never used binary storage capabilities of a database; as such, I decided to take your question and flesh it out into a full demo so that I could learn as well as teach. I knew what the answer would be, but had never implemented it. Let's take a look at the demo:


 
 
 

 
 
 
 
 

This demo is divided up into two ColdFusion pages. The first page, the form input, does not really pertain to the question asked; it merely sets up the environment for the answer (and lets me experiment with using BLOB fields for the first time). The second ColdFusion page is the one used as the IMG src attribute; this is the page that reads the binary data out of the database and streams it to the user without creating an intermediary anchor tag / href or writing the file to disk (which was the primary question).

Let's take a quick look at the form input page:

  • <!--- Param form data. --->
  • <cfparam name="FORM.id" type="numeric" default="0" />
  • <cfparam name="FORM.name" type="string" default="" />
  • <cfparam name="FORM.hotness" type="string" default="" />
  • <cfparam name="FORM.photo" type="string" default="" />
  • <cfparam name="FORM.submitted" type="boolean" default="false" />
  •  
  •  
  • <!--- Check to see if the form as been submitted. --->
  • <cfif FORM.submitted>
  •  
  • <!---
  • Normally, this is where we would validate data, but for
  • this demo, we are not going to worry about valid data.
  • --->
  •  
  •  
  • <!---
  • Check to see if are inserting or updating data. For this
  • part, we are not going to worry about the photo - that
  • will come afterwards.
  • --->
  • <cfif FORM.id>
  •  
  • <!--- Update the record. --->
  • <cfquery name="qUpdate" datasource="#REQUEST.DSN#">
  • UPDATE
  • hottie
  • SET
  • name =
  • <cfqueryparam
  • value="#FORM.name#"
  • cfsqltype="cf_sql_varchar"
  • />,
  •  
  • hotness =
  • <cfqueryparam
  • value="#FORM.hotness#"
  • cfsqltype="cf_sql_decimal"
  • scale="1"
  • />
  • WHERE
  • id =
  • <cfqueryparam
  • value="#FORM.id#"
  • cfsqltype="cf_sql_integer"
  • />
  • </cfquery>
  •  
  • <cfelse>
  •  
  • <!--- Insert the new record. --->
  • <cfquery name="qInsert" datasource="#REQUEST.DSN#">
  • INSERT INTO hottie
  • (
  • name,
  • hotness
  • ) VALUES (
  • <cfqueryparam
  • value="#FORM.name#"
  • cfsqltype="cf_sql_varchar"
  • />,
  •  
  • <cfqueryparam
  • value="#FORM.hotness#"
  • cfsqltype="cf_sql_decimal"
  • scale="1"
  • />
  • );
  •  
  • <!--- Return the new ID. --->
  • SELECT
  • ( LAST_INSERT_ID() ) AS id
  • ;
  • </cfquery>
  •  
  • <!--- Store the new id into our form variable. --->
  • <cfset FORM.id = qInsert.id />
  •  
  • </cfif>
  •  
  •  
  • <!---
  • ASSERT: At this point, we have either created or
  • updated the given record and the ID of that record
  • is stored in FORM.id.
  • --->
  •  
  •  
  • <!---
  • Now that we have created a hottie record, let's check
  • to see if we need to update photo.
  • --->
  • <cfif Len( FORM.photo )>
  •  
  • <!--- Upload the file. --->
  • <cffile
  • action="upload"
  • filefield="photo"
  • destination="#ExpandPath( './' )#"
  • nameconflict="makeunique"
  • />
  •  
  • <!--- Read in the binary data. --->
  • <cffile
  • action="readbinary"
  • file="#ExpandPath( './' )##CFFILE.ServerFile#"
  • variable="binPhoto"
  • />
  •  
  • <!--- Delete photo from server. --->
  • <cffile
  • action="delete"
  • file="#ExpandPath( './' )##CFFILE.ServerFile#"
  • />
  •  
  •  
  • <!---
  • Insert the binary data as a BLOB into the current
  • hottie record.
  • --->
  • <cfquery name="qInsert" datasource="#REQUEST.DSN#">
  • UPDATE
  • hottie
  • SET
  • photo =
  • <cfqueryparam
  • value="#binPhoto#"
  • cfsqltype="cf_sql_blob"
  • />
  • WHERE
  • id =
  • <cfqueryparam
  • value="#FORM.id#"
  • cfsqltype="cf_sql_integer"
  • />
  • </cfquery>
  •  
  • </cfif>
  •  
  • </cfif>
  •  
  •  
  • <!---
  • Query for the selected hottie. At first, this will return
  • no records, but once the form is submitted, it will return
  • the selected hottie.
  • --->
  • <cfquery name="qHottie" datasource="#REQUEST.DSN#">
  • SELECT
  • h.id,
  • h.name,
  • h.hotness,
  •  
  • <!--- Check to see if the hottie has a photo. --->
  • (
  • CASE
  • WHEN
  • h.photo IS NULL
  • THEN
  • 0
  • ELSE
  • 1
  • END
  • ) AS has_photo
  • FROM
  • hottie h
  • WHERE
  • h.id =
  • <cfqueryparam
  • value="#FORM.id#"
  • cfsqltype="cf_sql_integer"
  • />
  • </cfquery>
  •  
  •  
  • <cfoutput>
  •  
  • <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  • <html>
  • <head>
  • <title>ColdFusion / MySQL Blob Demo</title>
  • </head>
  • <body>
  •  
  • <h1>
  • ColdFusion / MySQL Blob Demo
  • </h1>
  •  
  •  
  • <form
  • action="#CGI.script_name#"
  • method="post"
  • enctype="multipart/form-data">
  •  
  • <!--- Send submission flag. --->
  • <input type="hidden" name="submitted" value="true" />
  •  
  • <!---
  • Send back the ID of the record we are working
  • with. This will be zero the first time.
  • --->
  • <input type="hidden" name="id" value="#FORM.id#" />
  •  
  •  
  • <p>
  • Name:<br />
  • <input
  • type="text"
  • name="name"
  • value="#FORM.name#"
  • size="20"
  • maxlength="40"
  • />
  • </p>
  •  
  • <p>
  • Hotness:<br />
  • <input
  • type="text"
  • name="hotness"
  • value="#FORM.hotness#"
  • size="10"
  • maxlength="4"
  • />
  • </p>
  •  
  • <p>
  • Photo:<br />
  • <input
  • type="file"
  • name="photo"
  • size="50"
  • />
  • </p>
  •  
  • <!--- Check to see if a photo is available. --->
  • <cfif (
  • qHottie.RecordCount AND
  • qHottie.has_photo
  • )>
  •  
  • <p>
  • <img
  • src="./photo.cfm?id=#qHottie.id#"
  • title="#HtmlEditFormat( qHottie.name )#"
  • />
  • </p>
  •  
  • </cfif>
  •  
  • <p>
  • <input type="submit" value="Submit" />
  • </p>
  •  
  • </form>
  •  
  • </body>
  • </html>
  •  
  • </cfoutput>

This is a pretty basic form input and file upload page. I am not using any real data validation since this is just a demo. When the form is processed, I am either inserting or updating the hottie record, in this case, for Julia Stiles. Once this has been done, I take care of the photo upload. This step is a bit ganky since I have to store the file to server first, then read it in using a BinaryRead, then delete the file from the server, then update the hottie record using a CF_SQL_BLOB data type. I am sure that there is a cleaner way to grab the binary data right out of the ColdFusion page request, but that is a whole other blog post.

You will notice that the IMG tag of our displayed photo points to our other ColdFusion page, photo.cfc. This is where we really start to answer the user's question - how do we stream the binary data to the user. The answer is, assuming you have ColdFusion 7 or later, the CFContent tag. The CFContent tag allows you to stream binary data directly to the client:

  • <!--- Param URL data. --->
  • <cfparam name="URL.id" type="numeric" default="0" />
  •  
  •  
  • <!--- Query for hottie record and pull back image binary. --->
  • <cfquery name="qHottie" datasource="#REQUEST.DSN#">
  • SELECT
  • h.id,
  • h.name,
  • h.photo
  • FROM
  • hottie h
  • WHERE
  • h.id =
  • <cfqueryparam
  • value="#URL.id#"
  • cfsqltype="cf_sql_integer"
  • />
  • </cfquery>
  •  
  •  
  • <!--- Set the header values. --->
  • <cfheader
  • name="content-length"
  • value="#ArrayLen( qHottie.photo )#"
  • />
  •  
  • <!---
  • Set the file name of the inline graphic to be the name
  • of the hottie record.
  • --->
  • <cfheader
  • name="content-disposition"
  • value="inline; filename=#qHottie.name#"
  • />
  •  
  • <!--- Stream the binary image data to the user. --->
  • <cfcontent
  • type="image/*"
  • variable="#qHottie.photo#"
  • />

Once you see the code, it's pretty straightforward; we are simply grabbing the BLOB data out of the database and using that as the binary data we stream via ColdFusion's CFContent Variable attribute.

Now, in our example, we are streaming to the client using an IMG src attribute; however, we could have easily let the user click on the a link and taken them directly to the photo.cfm URL for a similar effect. Right now, you are probably thinking, holy macrol! CFContent is amazing! And, you are way right. However, depending on the size of your files and the traffic on your site, you might want to be careful about using it as it does have some impact on the available resources for the server. The good thing is that several people have found that this affect is not too bad.

I have to say that I really liked using the database to store binary data rather than rely on a third party file system. All things being equal, it just seems like a better solution to asset management; why bother keeping another moving part when you could store your assets directly in the database. I know I have looked into this a bit before, but I should give it another look and see what the pros and cons of database storage are.




Reader Comments

Ben,

Just wanted to say I enjoy the small screencasts you have been including with your posts as of late. Help with the learning process. This post has me intrigued to try out BLOB data storage again myself. I tried years ago with not much success and haven't looked much at it since then.

Reply to this Comment

@C.Gratz,

Thanks man. I think they help tie the whole idea together a bit. I like to show it up front so you get a birds eye view of everything that is going on; then, when we dive into the code to get a close-up, you have a much better reference for where you are.

Reply to this Comment

I second the motion - great screencasts. It looks like you're using the Jing software to create them. How are you liking it?

Getting back to the topic of streaming binary files... if your HTTP server uses mod_rewrite or isapi_rewrite, you can use URL rewriting to pretty up the URL in the img tag.

The streamed image could appear as "1234.jpg" in your markup, but be converted to "photo.cfm?id=1234" by Apache/IIS/etc. before it gets to ColdFusion.

I got this to work on my machine without too much trouble. I have absolutely no idea why you'd ever need to do this, but it's a nifty technique to keep in mind. :)

Reply to this Comment

What about caching? We were serving photos from our DB using a similar method, but decided to switch back to storing the images in web accessible directories. In my head at the time (i'll admit i tend to make assumptions without proper study), I assumed our method did not cache the image. If your image changes regularly, your method is justified, but if the image is static, I don't see how the extra traffic on the db and bandwidth is necessary. Or am I crazy?

Reply to this Comment

JD: I would usually implement caching as a reverse proxy with Apache mod_cache or Squid. Caching is complex thing and using existing code for it is usually good idea.

Reply to this Comment

@David,

Nothing wrong with a little mod-rewrite action, even if just to create nicer looking URLs :)

@JD,

You raise a good point. I think with this kind of operation, there are always gonna be pros and cons. And, the extra Database transfer is definitely a con. There's a lot to be said about having an image as a web-accessible file that can just be handled by the server rather than ColdFusion. I assume that database-stored assets are generally done out of necessity or when you simply don't want to deal with a file system.

Reply to this Comment

Seems like another debate on the use of File System or Database for binary data storage. Choice is often dependent on use-case. But for images, I'd rather use the File system to store them in non-web accessible areas and store related-information in a database.

Caching of course can be handled at either the application server (using cfheader tag or header function for ColdFusion or PHP respectively, to set Expires response headers) or web-server level (using .htaccess for Apache to set Expires response headers)

Reply to this Comment

Hi,

I am new at this but when testing this page on my server I get the following error. Can any one help. Thanks a lot!

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]'LAST_INSERT_ID' is not a recognized built-in function name.

Reply to this Comment

@john,

It looks like you are on a SQL Server (rather than MySQL). You would need to replace the LAST_INSERT_ID() with:

@@Identity

... to get the latest ID creation.

Reply to this Comment

Hi Ben,

A few questions.

1) Is there a reason you didn't use the result attribute, and use the IDENTITYCOL variable to grab the id?

2) Do you know if it's possible to store java classes as binary objects in the db?

Thanks,

j

Reply to this Comment

@Big Jim,

I believe that the IDENTITYCOL was only added in ColdFusion 8. Plus, I am just used to returning the identity manually. It's not enough of an effort for me to change to using the CF8 results column. Maybe I will move that way eventually.

As far as storing binary java classes, I have no idea idea. There very well might be. You can definitely store the physical files in the DB (just as you could on a file server) - the question becomes, is there an easy way to load the binary class file into the Java memory space. Or, would you have to write it first to a file and then use some sort of URL class loader?

Reply to this Comment

Any idea if there is a quick fix for MX6.1? Apparently it doesn't like the "Variable" attribute in the CFCONTENT tag:

Attribute validation error for tag CFCONTENT.
The tag does not allow the attribute(s) VARIABLE. The valid attribute(s) are DELETEFILE,FILE,RESET,TYPE.

The error occurred in C:\wwwroot\BlobTest\photo.cfm: line 58

56 :
57 : <!--- Stream the binary image data to the user. --->
58 : <cfcontent
59 : type="image/*"
60 : variable="#qHottie.photo#"

I tried changing it to file and adding the filename from the above cfheader tag, but no go.

Reply to this Comment

@azapper,

I haven't tested this, but I'd go with VARBINARY so you can have variable-length binary data.

Reply to this Comment

Any idea how to modify this to work with large blobs? By "large", I mean blobs too big to be pulled from the table in a single read. (I'm not exactly sure if the size limitation is ODBC, JDBC or ColdFusion, but there is a limit)

I'm using similar code to download premade PDF's and some can be much larger than the average web graphic -- some greater than 1Mb.

Reply to this Comment

@Steve,

For that, you can do one of two things:

1. Make multiple BLOB reads and create a server-local file based on all of them. Then, when you build up the file, stream that to the client.

2. Make multiple BLOB reads and write them to the underlying binary stream being sent to the client:

http://www.bennadel.com/blog/1227-Using-ColdFusion-To-Stream-Files-To-The-Client-Without-Loading-The-Entire-File-Into-Memory.htm

Hope that helps.

Reply to this Comment

Thank you VERY much. That is exactly what I was looking for (actually more, I did not know about the preload/RAM limitation of CFContent).

Reply to this Comment

@Steve,

No problem my man. Unless you are loading huge files, though, I wouldn't worry too much about the RAM limitations of CFContent.

Reply to this Comment

I'm getting this error message:

Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT ( LAST_INSERT_ID() ) AS id' at line 12

Table - hottie

Fields - id, name, photo, hotness

Also, I think this is a typo (photo.cfc) should be photo.cfm

Thanks

Reply to this Comment

@Barry,

It sounds like you don't have multiple statements enabled in your query (the ";" is probably what is throwing the error). Is that possible? Have you run multiple queries in one CFQuery tag before with that particular data source?

Reply to this Comment

Was working - CF9 broke my code.

In reference to my previous "big blob" post, I opted to pull the data in bite size chunks and save to a local server file and use cfcontent to send the file. No problems until I upgraded to CF9. Here is my code:

<cfset variables.blockSize=4096 />
<cfset variables.action="write" />
<cfloop index="variables.offset" from="1" to="#qMyInvoiceMimeInfo.dataLen#" step="#variables.blockSize#">
<cfquery name="qMyInvoiceData" datasource="#APPLICATION.DSN.Vault#" dbtype="ODBC">
select
substring(Invoice#ATTRIBUTES.type#,#VARIABLES.offset#,#VARIABLES.blockSize#) as [data]
from
[InvoiceHistory]
where
invoiceNumber = <cfqueryparam value="#Val(qMyInvoice.invoiceNumber)#" cfsqltype="CF_SQL_INTEGER">
and billingEntity = <cfqueryparam value="#Val(qMyInvoice.billingEntity)#" cfsqltype="CF_SQL_INTEGER">
</cfquery>
<cffile action="#variables.action#" addnewline="no" attributes="normal" file="#qMyInvoiceMimeInfo.fileName#" output="#qMyInvoiceData.data#" fixnewline="no" />
<cfset variables.action="append" />
</cfloop>

The problem I am experiencing is that in CF9, the "append" action does not seem to work with cffile tag. I've done additional testing and the problem seems isolated to using ByteArray as the output withing the cffile call (at least that is my guess - cffile with plain text works fine.

Any ideas? Is this a CF9 bug?

On a side note, I've tried using java io calls as well attempting to bypass using cffile altogether but I can't seem to get the ByteArray to cast properly for the write() call -- I get a "write method not found" message because it does not recognize the parameter.

Reply to this Comment

@Steve,

I am not sure if I am following the code correctly. That "data" field that comes back - is that a string or a binary value? I believe that substring() returns string values, not byte arrays (thought I could be way wrong).

What do you want it to be? It's supposed to be a chunk of a binary file?

Reply to this Comment

The "data" field is a binary blob defined as an "image" in MS-SQL and it contains a pdf -- sometimes a very large pdf. I'm not certain the intent of substring(), but it has been returning a ByteArray.

The work around I did was to bypass this logic entirely and instead simply return InvoicePDF in the query and pass this to the output parameter of cffile and it's working so far. My concern is that when I did this in the past, I ran into size limitations and only x number of bytes would be saved, even when "large blobs" was checked in the CF admin (which it is).

I'll see if I can find one of those Mb+ PDF's and see if it works. Maybe CF9 handles this now -- the last time I worked on this issue I was using CF7.

Reply to this Comment

Well CF9 must handle this differently than the CF7 I remember (or it could be the CF9 is running in a 64 bit environment and the CF7 was 32 bit). I just ran a test with a 4.5 Mb pdf, no issue and it was very quick on my local LAN -- under 2 seconds including download. I was stressing over nothing. Thanks for reading my vent.

Reply to this Comment

@Steve,

Glad it's working for you. I just did some Googling and I see and other people use substring() and instr() with blob fields, so I guess it is does something meaningful; I just don't have the experience with blob fields to know what that is. In any case, glad its working.

Reply to this Comment

I'm hoping someone might be able to help who worked with BLOBs before although I'd appreciate your insight as well Ben - if you've got time.

Generally speaking, I've follow much of the process outlined in this article (got me off to a great, quick start - thanks) and then combined that with other kinds and my experiences, blah blah, blah to end up with this final code used for output:
<cfheader name="content-length" value="#ArrayLen(blob_content)#" />
<cfheader name="content-disposition" value="inline; filename=#blob_name#" />
<cfcontent type="#GetMimeType(blob_extension)#" variable="#toBinary(blob_content)#" reset="yes" />
<cfflush>
<cfabort>

I wrote a UDF to convert the file extension (parsed on db insert) to the respective mime type. So what my problem is that this works wonderfully for every fileformat I've tested, EXCEPT PDFs. The binary gets returned to the browser (I can see it displayed in IE or from view source in FF - pointless to test more browsers at this point).

Any idea why this might be happening?

Reply to this Comment

My guess is the cfcontent type="xxx" value you are using. The content type is not the file extension, although they are related. For PDF's, the type should be "application/pdf".

Reply to this Comment

Steve,

Thanks for the quick reply - I am able to confirm that my UDF is correctly returning application/pdf as the mimetype for PDF files.

It makes me wonder if it has something to do with the response headers.

Reply to this Comment

@Derek,

What happens if you change the disposition from inline to attachment?

Also, I don't think you need the CFFlush / CFAbort after the CFContent tag. When you us the Variable attribute, it overwrites the entire request and halts the processing (as far as I know).

Reply to this Comment

Here is my code. Nothing really stands out as dramatically different other than I'm not setting the header value for content-length.

<cfheader name="Content-Disposition" value="inline; filename=#GetFileFromPath(qMyInvoiceMimeInfo.fileName)#">
<cfcontent type="#qMyInvoiceMimeInfo.mimeType#" variable="#Evaluate('qMyInvoice.Invoice#qMyInvoiceMimeInfo.type#')#" reset="yes"><cfabort>

Reply to this Comment

HA - sweet.

Thanks a ton!

Changing from "inline" to "attachment" did the trick. I did remove the flush and abort and it doesn't seem to have an affect. I'll probably keep them in there for "mental reminders".

Thanks Ben

FWIW - We met years ago at CFUNITED, I always get the help I need from your site and I greatly appreciate it. Thanks to you and your user base.
:-)
And no, you wouldn't likely recognize me, I'm one of those CF'ers no one ever hears from but advocates the hell out it when given the chance.
:-)

Reply to this Comment

I know I had problems using "attachment:" vs. "inline:". I can't remember what it was exactly but I vaguely remember that it was something secruity related on certain browsers or certain browser security settings.

Reply to this Comment

**
I know I had problems using "attachment:" vs. "inline:". I can't remember what it was exactly but I vaguely remember that it was something secruity related on certain browsers or certain browser security settings.
**

I'll make sure I pay attention to that in testing Steve - thanks for the heads up.
:-)

GO BLACKHAWKS!!!!
:-)

Reply to this Comment

@Derek,

Oh great - glad that got it working. Not all things needs to be delivered at attachments; so, it might make sense for you to extend the user-defined-function concept a bit and make one for disposition, ala getContentDisposition( fileExt )... that way, you can change it per file type.

Hopefully we will meet again :)

Reply to this Comment

Ben:

I have moved directory based images into SQL 2008 Express Image field and now want to move those same images to SQL 2005 Image field and then read the SQL 2005 Image Field and display the pictures in the browser for verification.

Everything seems to be working except when I read an empty Image field in SQL 2005, when the web page errors out with:
"ColdFusion was unable to create an image from the specified source file.
Ensure that the file is a vaild image file."

I have not figured out how to validate whether or not there is a valid image in the Image field and want to avoid writing the Image from the DB to a file where I could use. The Case example you show above does not seem to work as I guess the Image field is not NULL when no Image has been transferred

Many thanks

Ted Daniels

Reply to this Comment

@Ted,

Can you check the length of the image field before you try to create an image from it? If it doesn't show up as null, at the very least, it should have a zero length or something along those lines.

Reply to this Comment

Ben:

The BLOB record has about the same length as all the other BLOBS. These are all BLOBS of JPG mugshots, and for some reason I don't yet understand, the binary for this one starts off differently from the others. For example all the others start with 0xFFD....., while this one starts with 0x2F3....;, the binary of the images saved OK in the database (and why not, it's just a BLOB), but is not recognized as a valid image file when I read it back in order to xfer to another database. I am seeking a way to flag all files like this that are not recognized as binary images so I can run a batch process and build a table of all bad image files so I can go back and check them all.

Ted

Reply to this Comment

@Ted,

Hmm, I'm not coming up with any good ideas. I don't know much of the nuances of binary data storage. Sorry :(

Reply to this Comment

@Ben Nadel,

Well that makes me feel better - I thought it was me being kinda dumb that I could not figure this out easily.

I can kludge around it but was looking for an elegant solution!

Ted

Reply to this Comment

Hi Ben,

I am trying to use insert images into a field of a Table in Microsoft SQL server. Following your instruction of upload, read to binary and then insert, I saw that something is inserted into the Table. The data type for the field is "image". However, how I could not load it on a webpage? I am thinking <img src="#ThisList.Content1#">, but I got an error "ByteArray objects cannot be converted to strings."

I put
<cfcontent type="image/*" variable="#ThisList.Content1#"/>
on the webpage and the image I got is only top portion of the uploaded image. So, may be the image was not uploaded completely.

ThisList is the query to get the list back.
Conetent1 is the field that has image as datatype.

How can I see/display the image from the database?

Any insights will be helpful!

Thank you!

Dan Chan.

Reply to this Comment

@Dan,

I have had this happen to me before. ColdFusion actually puts a limit on how large the a query object can be. This is defined in the ColdFusion administrator. If you increase that size, you'll probably find that the image starts showing up. Right now, what's likely happenig is that the image binary data is being truncated as part of the request.

If you can't change that value (ie. you don't have access to the ColdFusion admin), I have seen someone else using a looping technique to read the image from the database in multiple passes. For more info on that, take look at Jason's comment at the bottom of this post:

http://www.bennadel.com/blog/1251-Ask-Ben-Splitting-And-Joining-Large-Binary-Files-Using-Buffers-In-ColdFusion.htm

Let me know if that helps at all.

Reply to this Comment

Ben,

Thank you! I think the setting is "Longtext buffer size". I saw that the setting is for 64k. So, I uploded a photo under 64k and the whole photo is displayed.

I have a link on a ColdFusion page with
<cfcontent type="image/*" variable="#ThisList.Content1#"> to display the image from the database. When I click on the link, IE will try to download the page.

After a few tries, the page will display correctly, but I don't know why yet.

Any idea on making sure the image is displayed all the times?

Thank you.

Dan Chan

Reply to this Comment

Ben,

I understood the problem now!

<cfheader
name="content-disposition"
value="inline; filename=Test.jpg"
/>

I needed the above statement to make the browswer render the jpg file I got.

Thank you!

Dan Chan

Reply to this Comment

@Dan,

Glad you got it working. Content-disposition is an excellent header. And, just to discuss the flip-side for a second, if you *want* the browser to try to save it, you can use a value of "attachment" rather than "inline."

Reply to this Comment

Hi ALL,

First step is to make sure Allow Blob objects in advanced settings of datasource of coldfusion administrator for viewing pdf which is stored in db and next step is to set the content type as pdf

eg:<cfcontent type="application/pdf" variable="#queryname.columnname#">

all the best.

Reply to this Comment

Hey All,

Thanks Ben for all the great info you post on your site.

My question is, I need to insert Images (Digital Signature) into BLOB datatype (DB2).

1. How can i do this using SQL directly, considering the images are stored directly in the table and not on a document server.
2. Whats the best way to select the image and use it in the application, to carry over from one page to the other?

Thanks a lot.

Reply to this Comment

@Jim. Regarding the serialization of Java objects, you can defintely store serialized objects in a DBMS BLOB column.

@Santosh. I don't believe DB2 has any advanced image features. However, it does store BLOB data REALLY well. I use DB2 as my DBMS of choice. I simply pass the BLOB data to an SP that inserts or updates the BLOB and any meta data. To get it out, I query directly from ColdFusion as I haven't had much luck with getter SPs and BLOB data. You can then reference the new query object directly in a cfimage tag. Once it is created and "reassembled" you can pass it and reference it just like any other variable. I hope I got what you were asking :)

Reply to this Comment

Ben, let me say first that your explanation and code examples worked great. I have employed them in my database and they are working fine. One question that I have is that once we upload, a pdf for example, and then move that table from one environment to another, the pdf will not open. The error message reads: There wan an error opening your document. The file is damaged and could not be repaired.

Any help you could offer would be greatly appreciated.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.