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.
What are the advantages (and disadvantages) to save the image in the database?
ReplyDeleteYou do not have any advantage. Does not help you with anything.
DeleteOne advantage might be that you can detach a database and move it to a new host without needing to copy the image files.
ReplyDeleteA 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.
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?
ReplyDeleteWell 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.
ReplyDeletePros:
ReplyDelete-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.
In Aaron's list of cons, you could add more load on the database server too, couldn't you?
ReplyDeleteAren't some of the new "No SQL" databases good for storing documents like images too? Just curious.
@Chris
ReplyDeleteYes 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.
Nice post Paul.
ReplyDeleteOne 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.
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.
ReplyDeleteIf 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.
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
ReplyDeleteOffshoot 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
ReplyDeleteCome 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!
ReplyDeleteThanks for a wonderful share. Your article has proved your hard work and experience you have got in this field. Brilliant .i love it reading.
ReplyDeletepotaup
Many thanks for sharing the information in your blog
ReplyDeletesupersmashflash2s.com
I need to save and retrieve pdf file into SQL server using ColdFusion, any code to change to do this,
ReplyDeleteAmazing post thank yuo for sharing http://codyqrvc118.cavandoragh.org/15-gifts-for-the-social-media-management-for-b2b-consultants-lover-in-your-life
ReplyDeleteErzurum
ReplyDeleteistanbul
Ağrı
Malatya
Trabzon
44Y
Çorum Lojistik
ReplyDeleteKaraman Lojistik
GĂĽmĂĽĹźhane Lojistik
Denizli Lojistik
Artvin Lojistik
2JNT2G
FAF8F
ReplyDeleteMalatya Şehirler Arası Nakliyat
Manisa Parça Eşya Taşıma
Maraş Şehir İçi Nakliyat
Kırşehir Şehir İçi Nakliyat
Bybit GĂĽvenilir mi
Tekirdağ Boya Ustası
Çorlu Lojistik
Rize Şehirler Arası Nakliyat
Isparta Lojistik
183C3
ReplyDeleteTunceli Şehir İçi Nakliyat
Bybit GĂĽvenilir mi
Batman Şehir İçi Nakliyat
Şırnak Şehir İçi Nakliyat
Eryaman Fayans Ustası
Muş Parça Eşya Taşıma
Artvin Şehirler Arası Nakliyat
MuÄźla Lojistik
Antep Evden Eve Nakliyat
4A66A
ReplyDeleteAdıyaman Lojistik
Bolu Evden Eve Nakliyat
Samsun Lojistik
Referans KimliÄźi Nedir
Hatay Şehir İçi Nakliyat
Ankara Asansör Tamiri
Kütahya Parça Eşya Taşıma
Amasya Evden Eve Nakliyat
Siirt Evden Eve Nakliyat
71EAF
ReplyDeletehttps://referanskodunedir.com.tr/
FE6F9
ReplyDeletebitget
huobi
kizlarla canli sohbet
türk kripto telegram grupları
toptan mum
mexc
binance
bitcoin seans saatleri
kripto kanalları telegram