Store / Retrieve images from Database with ColdFusion

[faulken]

I would like to store / retrieve images in a Microsoft SQL 2000 server. Can this be done?

<!----------- STORE in database ------------------->
<cffile action = "readBinary" file = "C:\inetpub\wwwroot\photos\somewhere.jpg" variable = "aBinaryObj">

<CFQUERY NAME="storeImage" DATASOURCE="#DATA_SOURCE#">
UPDATE tImageTable
SET myimage = '#aBinaryObj#'
WHERE myimage_id = 1
</CFQUERY>

<!------------------ RETRIEVE IMAGE -------------------->
<CFQUERY NAME="getImage" DATASOURCE="#DATA_SOURCE#">
SELECT myImage
FROM tImageTable
WHERE myimage_id = 1
</CFQUERY>

<!--- Output binary object to JPEG format for viewing. --->
<cffile action="write" file = "c:\files\updates\somewhereB.jpg" output = "#getImage.myimage#">

<!--- HTML to view image. --->
<img src="C:\files\updates\somewhereB.jpg">

Thanks

[Stefan_K_]

Storing retrieving images/files in MS SQL Server through CF is definitly possibly, but something you want to avoid at almost all costs. Plus there hardly is a real reason to it.

I did it years ago and followed many threads about it.
And I don't remember anyone ever giving a real reason why the images must be in the DB!

[Anonymous440]

i would recommend just storing the image name or full path in the DB and store the actual images in a folder. That would help you keep DB size a lot smaller, and query times to a minimum.

What is the reason you wish to store the actual image data in the DB?

[faulken]

I am working with the System Architect application. I do not know if you are familiar with this application but they store their images in a Microsoft SQL database. However, the way System Architect stored their data in not very relational. So, what I do is use their visual basic API to transfer the data to database that I set up. I would like to transfer the data and images in a one step process. If I do not store the images in the database, I would have to transfer the data then export all the images to a local folder and finally ftp the images to server. This is the process I am using now. I thought if I kept the images in the database would be a faster process.

Thanks
Darrel

[Anonymous440]

I am not familiar with System Architect, however:

If I understand correctly, you are moving data from System Architect to another DB? Then you are using this other DB to display data on a page? Once this data is moved, will you be accessing System Architect again? Do you need to move everything at once, or on a request by request basis?

[mcadle]

If it is coldfusion, then use cffile action="upload" and it will store it on the serve where CF resides. If this is on the same server, then you only need to store the path and not the blob. Storing blob's is not a good practice at all. It can be done, but it doesn't mean it should be.

[faulken]

Why is storing blob not a good practice?

I am taking the data stored in the System Architect database and transfering it to another database (for example: database named mydatabase). The system architect database has images stored in the database. And the images change on a hourly basis. I do not have access to Microsoft's import/export tool (DTS). So, I have written a visual basic script to transfer the data. Once I have the data transfered, I have written a web application to display the data stored in the database. I am transfering the data to a new database (mydatabase) because I do not like to way the data is stored in the System Architect database.

I am trying to keep the storing of images in the database because of the frequency of the changes to the images. If the images remain in the database, I can update the second database (mydatabase) with the activation of the VB script. If the images are outside the database, then I have added two more steps to the update. One export ALL of the images (I have no way of knowing which ones have been updated). And then moving the exported images to the web server.

I know the added steps are not a big deal. Really, I just wanted to know if the images could be stored in the database (if it was technically possible). I did not know this is considered a BAD practice.

Thanks for Reading
Darrel

[mcadle]

Its even worse to have that much row movement (hourly) because you are storing a huge amount of data and moving it around....this cause db bloat. BUT if you have the physical space, then you get security control, portability. In the end it is personal preference. I just think it is a bad idea in the end run for server performance and db efficiency.

If you google on storing images in db's you will find everyones opinion on this subject. A lot of cons and a few pros, but it is your choice. SO to help you out, yes coldfusion can do this. What problem are you having specifically?

[tclaremont]

Just because you CAN do something does not mean that you SHOULD.

Like others have stated, the internet is full of discussions regarding this very riddle. Do a web search and look at th various opinions.

After a bit of research, you will notice that the most steadfast proponents of such an architecture are those that are reading "theory" rather than "practice". The less experienced developers tend to think that storing the images in the DB is a natural choice.

Those that have been around the database block for a while are convinced by experience that the database bloat, the resultant processing overhead involved in every query, and huge impact on speed and maintenance make for a losing proposition.

So, I guess the best way to put it is this... I don't recommend storing images in your database. You can take that advice from a seasoned professional, or you can learn it the hard way over time.

[Newsgroup_User]

tclaremont wrote:
> After a bit of research, you will notice that the most steadfast proponents of
> such an architecture are those that are reading "theory" rather than
> "practice". The less experienced developers tend to think that storing the
> images in the DB is a natural choice.

what exactly is a bit of research? and where did you do it? in a cave?

we in fact do store images & other binary data in db that depending on the
application need. db technology has come a very long way since whenever you did
your "bit of research". we pretty much nearly *always* backend GIS web apps
w/databases that do *nothing* but serve images (and other binary data that gets
converted to images). ever heard of arcSDE, oracle spatial, postGIS, etc.? that
what these db technologies are meant for & they work pretty darned good.

> Those that have been around the database block for a while are convinced by
> experience that the database bloat, the resultant processing overhead involved
> in every query, and huge impact on speed and maintenance make for a losing
> proposition.

and who exactly are these people? you mean in your opinion? it doesn't fit every
need but to dismiss it like this is nonsense.

[mcadle]

Again, peoples opinions. PaulH speaks truth as he is a GIS guy. ESRI does store images in a db for lots of their products i.e. arcview, etc. Oracle spatial is a specialized version for storing images, although Oracle 10G can store images too. I think the question overall is, if you have your choice, return a recordset with the path saved for a total of 40 KB or the same recordset with the image saved in the db for a total of 40 MB, which will load faster through coldfusion a web based application? Well you have to figure the bandwidth but 40 KB will always load faster than 40 MB. BUT you have no security over the files, if a record is deleted than you have to script to remove the file from the server, and if you move servers, you have to move the files too.

So going back to the fact that this is a CF forum and not a db forum, how can we help you integrate CF with your application for your needs to store images?

[Newsgroup_User]

mcadle wrote:
> Again, peoples opinions. PaulH speaks truth as he is a GIS guy. ESRI does
> store images in a db for lots of their products i.e. arcview, etc. Oracle

keeping the facts straight, esri only has one product that handles spatial
databases & that's arcSDE. the other stuff (arcView, arcMap, arcIMS, etc.) are
clients that can use that data.

> return a recordset with the path saved for a total of 40 KB or the same
> recordset with the image saved in the db for a total of 40 MB, which will load

well nobody actually shoves 40mb at a user, it's pretty much always tiled or for
binary data it's usually turned into a single image representing it or flash/SVG
vectors.

[mcadle]

Well, I am not a GIS guy, I only know that my ocompany uses ArcView and they said it has DB2 for storing some of its data. So, I would have to defer to PaulH on that issue. I know my example was a little far fetched, but I was trying to show the theory of storing images vs. paths.

So, what is the actual size of a dataset that would return the actual images? Is it smaller than storing the path? Probably not. Ultimately, I think that is what I was trying to demonstrate. So again, how can we help faulken store images via coldfusion?

[tclaremont]

Paul, if you read for comprehension you will see that the original poster specifically mentioned SQL Server 2000. I think you will agree that SQL Server 2000 does not fall into the same league as the databases that you apprently use for image storage.

Dismissing it with no mention of the database might be construed as nonsense. But since the databases was, if fact, indicated, it became a more specialized answer.

[Newsgroup_User]

tclaremont wrote:
> Paul, if you read for comprehension you will see that the original poster
> specifically mentioned SQL Server 2000. I think you will agree that SQL Server
> 2000 does not fall into the same league as the databases that you apprently use
> for image storage.

just to keep you informed, arcSDE runs on top of sql server. we also
have solution storing imagery on sql server (as that's our main db
platform). yes, i can read english fairly well.

> Dismissing it with no mention of the database might be construed as nonsense.
> But since the databases was, if fact, indicated, it became a more specialized
> answer.

still sounds like nonsense to me.

[tclaremont]

Nothing wrong with disagreeing, I guess.

[natedog409]

<Stefan K.>
<snip...>
And I don't remember anyone ever giving a real reason why the images must be in the DB!
</snip>

When you store images/files/binary data directly in a database, you can reap the benefits of replication without any additional coding (how much code would you have to write to manage your images/files - copying/moving/deleting - when users are running two copies of your client application, or when your Web application gets big enough that you want to have two or more Web servers accessing the same files?). I run a Web site called www.braintrade.biz and I store all uploaded files in a SQL 7 database.