Skip to main content
F
faulken
Participant
August 30, 2006
Question

Store / Retrieve images from Database with ColdFusion

  • August 30, 2006
  • 14 replies
  • 5043 views

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
This topic has been closed for replies.

14 replies

Show previous replies
M
mcadle
Inspiring
August 30, 2006
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.
A
Anonymous440
Inspiring
August 30, 2006
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?


A
Anonymous440
Inspiring
August 30, 2006
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?
F
faulkenAuthor
Participant
August 30, 2006
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
S
Stefan_K_
Inspiring
August 30, 2006
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!
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices