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.
DeleteGreat Article Journal Paper Writing Services projects for cse JavaScript Training in Chennai JavaScript Training in Chennai Project Centers in Chennai
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.
This blog is so nice to me. I will continue to come here again and again. Visit my link as well. Good luck
ReplyDeletecara menggugurkan kandungan
cara menggugurkan kandungan
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
Alot of blogs I see these days don't really provide anything that I'm interested in, but I'm most definately interested in this one. Just thought that I would post and let you know. Nice! thank you so much! Thank you for sharing.
ReplyDeleteb612az.com
I have read your article, the information you give is very interesting.
ReplyDeleteinstagram viewer
Many thanks for sharing the information in your blog
ReplyDeletesupersmashflash2s.com
just information we only provide information for those who need it
ReplyDeleteA. obat telat datang bulan
B. posisi berhubungan agar cepat hamil
C. makanan dan minuman agar cepat hamil
D. panduan agar cepat hamil
E. cara agar cepat hamil
F. cara agar cepat hamil setelah selesai haid
G. cara alami untuk segera mendapat kehamilan
instagram takipçi satın al
ReplyDeleteinstagram takipçi satın al
takipçi satın al
takipçi satın al
instagram takipçi satın al
takipçi satın al
instagram takipçi satın al
aşk kitapları
tiktok takipçi satın al
instagram beğeni satın al
youtube abone satın al
twitter takipçi satın al
tiktok beğeni satın al
tiktok izlenme satın al
twitter takipçi satın al
tiktok takipçi satın al
youtube abone satın al
tiktok beğeni satın al
instagram beğeni satın al
trend topic satın al
trend topic satın al
youtube abone satın al
beğeni satın al
tiktok izlenme satın al
sms onay
youtube izlenme satın al
tiktok beğeni satın al
sms onay
sms onay
perde modelleri
instagram takipçi satın al
takipçi satın al
tiktok jeton hilesi
pubg uc satın al
sultanbet
marsbahis
betboo
betboo
betboo
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
ReplyDelete