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

Posted June 30, 2008 at 8:29 AM

Tags: ColdFusion, Ask Ben

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:

 Launch code in new window » Download code as text file »

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:

 Launch code in new window » Download code as text file »

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.

Download Code Snippet ZIP File

Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page

• Add To Del.icio.us
• Add To Digg
• Add to Spurl.net
• Add to Wists
• Add to Simpy
• Add to Newsvine
• Add to BlinkList
• Add to Furl
• Add to reddit
• Add to FARK
• Add to BlogMarks
• Add to Yahoo! My Web
• Add to smarking
• Add to Ma.gnolia
• Add to Segnalo

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.

Post Comment  |  Ask Ben

Post A Comment

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Formatting: <strong>bold</strong> <em>italic<em>
	Remember my information
	Subscribe to entry *
	Send me a copy of this comment (as part of the comment thread)

 

* If you subscribe, any new posts to this thread will be sent to your email address.