RE: Storing images in SQL
Rick, Utilizing CF to simply identify the location of the image is simpler for several reasons. I consider myself much junior to the great minds on this forum but I will humbly offer my experience. Simply put, speed and flexibility are your bonus payoffs. Key points for flexibility are: - you can identify the base image path in your application.cfm and simply reference the image name in the database in your code/query. This is a powerful key when you get a large project with thousands of images and you can identify separate image locations by department, location, branch etcetc to suit the business needs. - We have also found great flexibility in creating client maintained admin interfaces that allow them to dynamically build their own catalogs as they get new products through an interface that they use to add items in the database and upload new images to the image location for their department. - Speed, the biggest bonus. A simple query with a result of less than 30k in data (including the image reference) is loads better than the same query returning effectively the same data at 80+kb. Multiply that times thousands of users.. etc Case in point? http://www.edwardsluggage.com I hope my 2cents helps. Tony Gruen Development/SFNetworks [EMAIL PROTECTED] -Original Message- From: Rick Lamb [mailto:[EMAIL PROTECTED]] Sent: Monday, January 08, 2001 2:40 PM To: CF-Talk Subject: RE: Storing images in SQL Larry, I've asked this question before also. And got similar responses as yours which is appreciated. But is there any way you could tell us (a number of people have asked this question) why it's slower and messier. I'm sure for somebody that knows the details on what it takes to do this can logically agree with you (and others who have said the same) and I too would like to be one of those well informed people that come to the same conclusion for the same reasons. Could you or anyone else familiar with the techniques required for this please elaborate on this so I can say something other than "it's a bad idea because that's what somebody said." Thanks, Rick -Original Message- From: Larry C. Lyons [mailto:[EMAIL PROTECTED]] Sent: Monday, January 08, 2001 3:28 PM To: CF-Talk Subject: Re: Storing images in SQL Michael, In a nutshell don't. Its far faster, easier and a lot less messy to store the images's name and then dynamically construct the path information. regards, larry -- Larry C. Lyons ColdFusion/Web Developer EBStor.com 8870 Rixlew Lane, Suite 201 Manassas, Virginia 20109-3795 tel: (703) 393-7930 x253 fax: (703) 393-2659 http://www.ebstor.com http://www.pacel.com email: [EMAIL PROTECTED] Chaos, panic, and disorder - my work here is done. -- Michael Buffington wrote: Does anyone have any experience storing images in SQL 7.0, and retrieving them in CF? Any tips would help! Michael Buffington ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Storing images in SQL
Does anyone have any experience storing images in SQL 7.0, and retrieving them in CF? Its far faster, easier and a lot less messy to store the images's name and then dynamically construct the path information. I've asked this question before also. And got similar responses as yours which is appreciated. But is there any way you could tell us (a number of people have asked this question) why it's slower and messier. I'm sure for somebody that knows the details on what it takes to do this can logically agree with you (and others who have said the same) and I too would like to be one of those well informed people that come to the same conclusion for the same reasons. Could you or anyone else familiar with the techniques required for this please elaborate on this so I can say something other than "it's a bad idea because that's what somebody said." There are a couple of reasons that I prefer to use the filesystem for binary data. One is that it's not especially easy to read and write binary data to and from the database from CF. Most versions of CF provide no facility for working with binary data; CF 4.5.x provides the ToBinary function, but it's still not trivial to read and write the data. In the past, I've used CFX tags to do this, but it still was more trouble than it was worth. Another is that, generally, you don't get much benefit in storing binary data in a relational database. You're not going to build a query that uses the binary data as a filtering condition; text-based relational database just don't provide any useful facility for this. Also, in my experience, the act of reading and writing binary data from the database is often slower than reading it from a filesystem. This makes sense; binary and long-text data isn't stored in the same leaf/page structure as other data within the database. Instead, the leaf or page with the selected record contains a pointer to some other data storage location which contains the actual binary data. Finally, using the database to store this data is often an unnecessary bottleneck. If, to return an image, I have to retrieve the image itself from the database, that's going to occupy a significant amount of CF's time, as CF receives the image data stream. Alternatively, if I retrieve the name and filesystem location from the database, CF can often push the work to the web server, if the files are stored in web-accessible locations. In that case, CF can be used to find the file name, then write that within an IMG tag, and that's all the work it has to do. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Storing images in SQL
One of the "smaller" issues is backing up.. if the images are stored in the database, they will also be backed up every time you back up the database. If you have a lot of graphics, it will greatly increase the costs (time, media and slowing down the website) to do your daily backups.. If the graphics are stored on the server, you only have to back them up from your development machine. Al Musella a1webs.com There are a lot of other smaller issues, such as the way ColdFusion handles binary data and how that data is transferred between the database server and the ColdFusion application server, but hopefully the above description will give you a general idea of why developers always caution against storing image data directly in the database. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Storing images in SQL
Rick, Others have dealt with speed and storage issues regarding images and databases. Here's how I handle things. Rather than calling a BLOB object. What happens when your client wants to change a significant proportion of the images - its much easier copying the new images to the appropriate directory than inserting the images into the database. Secondly, speed. To display the images, it requires a database call, that is slower than the img src=" tag. Just two issues to consider. regards, larry -- Larry C. Lyons ColdFusion/Web Developer EBStor.com 8870 Rixlew Lane, Suite 201 Manassas, Virginia 20109-3795 tel: (703) 393-7930 x253 fax: (703) 393-2659 http://www.ebstor.com http://www.pacel.com email: [EMAIL PROTECTED] Chaos, panic, and disorder - my work here is done. -- Rick Lamb wrote: Larry, I've asked this question before also. And got similar responses as yours which is appreciated. But is there any way you could tell us (a number of people have asked this question) why it's slower and messier. I'm sure for somebody that knows the details on what it takes to do this can logically agree with you (and others who have said the same) and I too would like to be one of those well informed people that come to the same conclusion for the same reasons. Could you or anyone else familiar with the techniques required for this please elaborate on this so I can say something other than "it's a bad idea because that's what somebody said." Thanks, Rick ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Storing images in SQL
Michael, In a nutshell don't. Its far faster, easier and a lot less messy to store the images's name and then dynamically construct the path information. regards, larry -- Larry C. Lyons ColdFusion/Web Developer EBStor.com 8870 Rixlew Lane, Suite 201 Manassas, Virginia 20109-3795 tel: (703) 393-7930 x253 fax: (703) 393-2659 http://www.ebstor.com http://www.pacel.com email: [EMAIL PROTECTED] Chaos, panic, and disorder - my work here is done. -- Michael Buffington wrote: Does anyone have any experience storing images in SQL 7.0, and retrieving them in CF? Any tips would help! Michael Buffington ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Storing images in SQL Imaging database?
This topic came up a long time ago on the list. Someone gave out a url for a very nice image database that was done in java with a cf frontend. Anyone know the url? jon - Original Message - From: "Larry C. Lyons" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Monday, January 08, 2001 4:27 PM Subject: Re: Storing images in SQL Michael, In a nutshell don't. Its far faster, easier and a lot less messy to store the images's name and then dynamically construct the path information. regards, larry -- Larry C. Lyons ColdFusion/Web Developer EBStor.com 8870 Rixlew Lane, Suite 201 Manassas, Virginia 20109-3795 tel: (703) 393-7930 x253 fax: (703) 393-2659 http://www.ebstor.com http://www.pacel.com email: [EMAIL PROTECTED] Chaos, panic, and disorder - my work here is done. -- Michael Buffington wrote: Does anyone have any experience storing images in SQL 7.0, and retrieving them in CF? Any tips would help! Michael Buffington ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Storing images in SQL
Larry, I've asked this question before also. And got similar responses as yours which is appreciated. But is there any way you could tell us (a number of people have asked this question) why it's slower and messier. I'm sure for somebody that knows the details on what it takes to do this can logically agree with you (and others who have said the same) and I too would like to be one of those well informed people that come to the same conclusion for the same reasons. Could you or anyone else familiar with the techniques required for this please elaborate on this so I can say something other than "it's a bad idea because that's what somebody said." Thanks, Rick -Original Message- From: Larry C. Lyons [mailto:[EMAIL PROTECTED]] Sent: Monday, January 08, 2001 3:28 PM To: CF-Talk Subject: Re: Storing images in SQL Michael, In a nutshell don't. Its far faster, easier and a lot less messy to store the images's name and then dynamically construct the path information. regards, larry -- Larry C. Lyons ColdFusion/Web Developer EBStor.com 8870 Rixlew Lane, Suite 201 Manassas, Virginia 20109-3795 tel: (703) 393-7930 x253 fax: (703) 393-2659 http://www.ebstor.com http://www.pacel.com email: [EMAIL PROTECTED] Chaos, panic, and disorder - my work here is done. -- Michael Buffington wrote: Does anyone have any experience storing images in SQL 7.0, and retrieving them in CF? Any tips would help! Michael Buffington ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Storing images in SQL
It's probably pretty clear to most people what is going on when just a reference to the image (/directory/name.img) is stored in the database so I'll just discuss what additional overhead is incurred when the entire image is stored in the database. When you store the actual image in the database, the request from the client for the image that would normally get handled relatively speedily and efficiently by the Web server and file system (after all that is what they are designed for) is actually passed off to ColdFusion. This ties up one additional thread on the ColdFusion server for each image being processed. You only get so many threads: usually 5 times the number of processors. If you had a single CPU server, you could tie up all the available threads with one page that had five images on it. ColdFusion then makes a query to the database which is usually on a different server. This takes network resources, from the NICs to the bandwidth utilized to transfer the query and return the image data. If the database is not on a separate server (which is not generally a good idea because, among other things, you are tying up CPU cycles on your Web server) then you still incur the overhead of processing the request, finding the data in the database and passing this back the ColdFusion application server all via ODBC, OLE etc. On the database's end of things, RDBMSs usually store BLOB (Binary Large Object) data relatively poorly since most RDBMSs store information in 2k extents and 8k pages. What in effect happens is the RDBMS stores the image elsewhere in the database and places a pointer where you would logically expect the image data. This is also true for the TEXT datatype. When the RDBMS goes to return the data, it has to "hunt" the image in a separate spot. In any case, you've added quite a bit more overhead to the Web server, the database server, and the network. You've also increase the amount of time it takes to process the request, making you site appear slower to outside users. Some users may have special requirements that necessitate storing the images in the database, but, in most situations, the gain is little to nothing. There are a lot of other smaller issues, such as the way ColdFusion handles binary data and how that data is transferred between the database server and the ColdFusion application server, but hopefully the above description will give you a general idea of why developers always caution against storing image data directly in the database. Benjamin S. Rogers Web Developer, c4.net voice: (508) 240-0051 fax: (508) 240-0057 -Original Message- From: Rick Lamb [mailto:[EMAIL PROTECTED]] Sent: Monday, January 08, 2001 5:40 PM To: CF-Talk Subject: RE: Storing images in SQL Larry, I've asked this question before also. And got similar responses as yours which is appreciated. But is there any way you could tell us (a number of people have asked this question) why it's slower and messier. I'm sure for somebody that knows the details on what it takes to do this can logically agree with you (and others who have said the same) and I too would like to be one of those well informed people that come to the same conclusion for the same reasons. Could you or anyone else familiar with the techniques required for this please elaborate on this so I can say something other than "it's a bad idea because that's what somebody said." Thanks, Rick -Original Message- From: Larry C. Lyons [mailto:[EMAIL PROTECTED]] Sent: Monday, January 08, 2001 3:28 PM To: CF-Talk Subject: Re: Storing images in SQL Michael, In a nutshell don't. Its far faster, easier and a lot less messy to store the images's name and then dynamically construct the path information. regards, larry -- Larry C. Lyons ColdFusion/Web Developer EBStor.com 8870 Rixlew Lane, Suite 201 Manassas, Virginia 20109-3795 tel: (703) 393-7930 x253 fax: (703) 393-2659 http://www.ebstor.com http://www.pacel.com email: [EMAIL PROTECTED] Chaos, panic, and disorder - my work here is done. -- Michael Buffington wrote: Does anyone have any experience storing images in SQL 7.0, and retrieving them in CF? Any tips would help! Michael Buffington ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: storing images in SQL database
Quoting Rick Lamb [EMAIL PROTECTED]: Could you guys give me suggestions for the best way to store images in a database. I usually just store the image name in the database. If necessary you could also store the path to the image in the database. Other than that I don't know what you need to know. - This mail sent through IMP: sparky.drule.org ~ Paid Sponsorship ~ Get Your Own Dedicated Win2K Server! Instant Activation for $99/month w/Free Setup from SoloServer PIII600 / 128 MB RAM / 20 GB HD / 24/7/365 Tech Support Visit SoloServer, https://secure.irides.com/clientsetup.cfm. Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: storing images in SQL database
This is also how I've always done it. But I'm curious if there is a way to store the actual image in the database. I was thinking maybe I could use one of CF's binary functions to convert it to binary, then to wddx or store it directly in the database as binary. If it wasn't much of a performance hit, and is possible, seems to be a much cleaner solution. Rick -Original Message- From: Jennifer Larkin [mailto:[EMAIL PROTECTED]] Sent: Friday, December 29, 2000 5:07 PM To: CF-Talk Subject: Re: storing images in SQL database Quoting Rick Lamb [EMAIL PROTECTED]: Could you guys give me suggestions for the best way to store images in a database. I usually just store the image name in the database. If necessary you could also store the path to the image in the database. Other than that I don't know what you need to know. - This mail sent through IMP: sparky.drule.org ~ Paid Sponsorship ~ Get Your Own Dedicated Win2K Server! Instant Activation for $99/month w/Free Setup from SoloServer PIII600 / 128 MB RAM / 20 GB HD / 24/7/365 Tech Support Visit SoloServer, https://secure.irides.com/clientsetup.cfm. Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: storing images in SQL database
I started a thread with the same question a few weeks back. You can do it, but others convinced me that there is little to be gained and a lot of overhead in the db. The net... you are better off storing the files in the file system (that's what the db does anyway, only less efficiently). HTH Dick At 4:18 PM -0600 12/29/00, Rick Lamb wrote: This is also how I've always done it. But I'm curious if there is a way to store the actual image in the database. I was thinking maybe I could use one of CF's binary functions to convert it to binary, then to wddx or store it directly in the database as binary. If it wasn't much of a performance hit, and is possible, seems to be a much cleaner solution. Rick -Original Message- From: Jennifer Larkin [mailto:[EMAIL PROTECTED]] Sent: Friday, December 29, 2000 5:07 PM To: CF-Talk Subject: Re: storing images in SQL database Quoting Rick Lamb [EMAIL PROTECTED]: Could you guys give me suggestions for the best way to store images in a database. I usually just store the image name in the database. If necessary you could also store the path to the image in the database. Other than that I don't know what you need to know. ~ Paid Sponsorship ~ Get Your Own Dedicated Win2K Server! Instant Activation for $99/month w/Free Setup from SoloServer PIII600 / 128 MB RAM / 20 GB HD / 24/7/365 Tech Support Visit SoloServer, https://secure.irides.com/clientsetup.cfm. Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: storing images in SQL database
if the purpose of storing them in the db is for "protection" or regulating access to specific images, a better way is to store the images in a private directory and use cfcontent shoot them out. of course, for this you'll need a gateway page like "getimage.cfm" and usually wind up passing a session token and an image id like img src="getimage.cfm?session=53053058180939585930985image=42120492". i recommend using tokens or "tickets" to get images instead of cookie-based session variables, since not all browsers submit cookie data when requesting images with img tags or especially with javascript. --brendan avery / [EMAIL PROTECTED] ~ Paid Sponsorship ~ Get Your Own Dedicated Win2K Server! Instant Activation for $99/month w/Free Setup from SoloServer PIII600 / 128 MB RAM / 20 GB HD / 24/7/365 Tech Support Visit SoloServer, https://secure.irides.com/clientsetup.cfm. Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
