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

Posted June 30, 2008 at 8:29 AM by Ben Nadel

Tags: ColdFusion, Ask Ben

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.

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

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

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?

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.

Thanks man.

Excellent site.

this site is nice and very helpful.

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

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)

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.

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

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

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

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.

@Eric,

In this post, you can see how to access the underlying Binary output and then write to it directly. This concept should work in CFMX 6.1, but might need to be tweaked:

http://www.bennadel.com/index.cfm?dax=blog:1227.view

The trick is:

GetPageContext().GetResponse().GetResponse()

What's the data type that should be used in MSSQL to store the blob? binary(50)?

@azapper,

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

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.

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

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

@Steve,

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

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

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

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.

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

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.

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.

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

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?

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

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.

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

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>

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

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.

Thanks Steve
Thanks Ben

This completes the last step in a document library I'm building for our DC office.

Your welcom. By the way, sorry Ben -- I have never personally met you. You wouldn't know me for Adam. ;-)

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

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

@Ben,

Good idea, I'll keep that in mind and share the final UDF - maybe at Google Code or something.

Thanks again.

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

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

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

@Ted,

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

@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

@Ted,

Sometimes kludge is just the way to get it done :)

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.

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

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

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

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

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.

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.

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

Post A Comment

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.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment