Reading and writing binary to a mySQL database with ColdFusion

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

19 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. This post is very interesting for us because its given as much information about congregational ministry keep sharing more post.
    abcya | brainpop | abcya 7

    ReplyDelete
  11. This blog is so nice to me. I will continue to come here again and again. Visit my link as well. Good luck
    http://www.jualobataborsiherbal.com/ obat aborsi
    http://caramenggugurkankandungan.info/ cara menggugurkan kandungan
    http://www.jualobataborsiherbal.com/cara-menggugurkan-kandungan/ cara menggugurkan kandungan
    http://jualobatpenggugurkandungan.net/ obat penggugur kandungan
    http://tandatandakehamilan.net/ tanda tanda kehamilan
    http://tandatandakehamilan.net/cara-cepat-dan-selamat-menggugurkan-kandungan/ cara menggugurkan kandungan
    http://obataborsi59.com/ obat aborsi
    http://obattelatdatangbulan.info/ obat telat datang bulan

    ReplyDelete
  12. I like your post. It really useful with me. Thanks for sharing these useful information!
    happywheels3game.com

    ReplyDelete
  13. Thank you for your post, I look for such article along time, today i find it finally. this post give me lots of advise it is very useful for me

    lennyfacetext.com

    ReplyDelete
  14. I was very impressed by this post, this site has always been pleasant news. Thank you very much for such an interesting post. Keep working, great job! In my free time, I like play game: facebooklite.com.br. What about you?

    ReplyDelete
  15. Thanks for the information you shared. Please share useful information with your friends regularly
    papasgamesonline.com

    ReplyDelete
  16. I was very impressed by this post, this site has always been pleasant news. Thank you very much for such an interesting post. Keep working, great job! In my free time, I like play game: vex3game.com. What about you?

    ReplyDelete