Dec 1, 2009

Reading and writing binary to a mySQL database with ColdFusion

Ever wanted to save an image to a database with ColdFusion? I see this question asked quite often. I don't have a need to do this but I mocked up a quick demo or reading and writing binary data to and from a mySQL database.

The code is very simple. I read the binary data from a image. Write it to a database, read it back out and write it to the browser. I also rotate the image to show the result from the database acts just like any other image data.

You might note I made use of 2 great new features of ColdFusion 9.

1. Data source specific to the application. I no longer need to specify a datasource in my cfquery tags as I have this setup in Application.cfc.

2. On insert ColdFusion can return the autoincrement id value of the record just inserted. Eg in SQL server select @@identity does the same thing. This was available in CF8 but it was lacking and was different depending on the database type.

In ColdFusion 9 the old values still work ( so code wont break ) but we also can now use queryResult.generatedKey.

I always try include a working demo: http://railo.kukiel.net/imageinsert/

Here is the CF Code:

Here is a sample to create the mySQL table:


Also note as this gets alot of people that by default CFadmin only allows 64k to be passed from CF to the database. You will probably need to increase this value. For this example I just added 2 extra 0's ( 6.4meg ). If you don't half the image will be inserted and you will be confused as to what the image ( or other binary files ) are corrupted.


Posted by Paul Kukiel at 10:14 AM
Labels: , , ,

9 comments:

  1. What are the advantages (and disadvantages) to save the image in the database?
    ReplyDelete
  2. One advantage might be that you can detach a database and move it to a new host without needing to copy the image files.

    A disadvantage is that there is overhead to and from the database.

    I'm sure there are many more pro's and con's and I'm interested to hear what others have to say.
    ReplyDelete
  3. Wouldn't it save server space? - if you have a site that has users submit images - wouldn't writing them to the database be more efficient as far as space?
    ReplyDelete
  4. Well it would still use space on the database server. Each "blob" is still the same size as the file its just stored in the database rather then the file system as a file.
    ReplyDelete
  5. Pros:
    -You can secure the "files" on your app much easier.
    -if you have a multi-app server environment, you can share images/files/etc across the applications without duplicating files.

    cons:
    -i think there is a little bloat storing as blobs (not 1:1 file to blob IIRC)
    -can't take advantage of webserver auto-caching the content
    -more load on your app server versus the file server

    Thats all I got right off the bat.
    ReplyDelete
  6. In Aaron's list of cons, you could add more load on the database server too, couldn't you?

    Aren't some of the new "No SQL" databases good for storing documents like images too? Just curious.
    ReplyDelete
  7. @Chris

    Yes without a doubt this would cause an increase on load for the DB server. Servering a static image via apache or IIS I imagine the load its very tiny compared to retrieving it from a database.
    ReplyDelete
  8. Nice post Paul.

    One huge adavantage of this is when running in a cluster with replicated databases, everything is replicated all text and binary content.

    It is a big slower, but you can also cache the binary data on disk and only update the database when it changes.
    ReplyDelete
  9. One thing that might be worth your testing relates to the 64k buffer behavior. In past versions of CF inserting to SQL Server, I found that the full data stream was written to the database regardless of the buffer setting. Retrieval, however, was truncated if the data exceeded 64k without adjusting the buffer. No lost data, just inaccessible if it exceeded the buffer size.

    If it's a busy site, you might also consider having a seperate "blobDSN" for use with the increased buffer setting. There is a performance impact associated with the buffer setting that you may not want to impose on every database transaction.
    ReplyDelete

Subscribe to: Post Comments (Atom)