Reading and writing binary to a mySQL database with ColdFusion

by 2:14 AM 24 comments
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.

24 comments:

  1. What are the advantages (and disadvantages) to save the image in the database?

    ReplyDelete
    Replies
    1. You do not have any advantage. Does not help you with anything.

      Delete
  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
  10. I'm looking for a few bugs in my post. But I think I should have someone look and point out it. http://colorswitchplay.com

    ReplyDelete
  11. Offshoot showcasing is an extraordinary approach to profit, and genuinely simple in case you're engaged and willing to put in the work to assemble a productive business. That being stated, there are a couple of entanglements of offshoot showcasing that can put a damper on your prosperity. Realize what they are, and how to settle them! More Information

    ReplyDelete
  12. Come to Kizi kids &, you will have the opportunity to discover thousands of the hottest games in the world with many genres such as action games, puzzle games, strategy games, fashion games or racing games. Click now!

    ReplyDelete
  13. Thanks for a wonderful share. Your article has proved your hard work and experience you have got in this field. Brilliant .i love it reading.

    potaup

    ReplyDelete
  14. Many thanks for sharing the information in your blog


    supersmashflash2s.com

    ReplyDelete
  15. I need to save and retrieve pdf file into SQL server using ColdFusion, any code to change to do this,

    ReplyDelete