Image from Database

I am using CF 4.0 and have a SQL Server DB that I am storing images in. I have worked out how to get the images in using CF but when I run a query and return the image, how do I pass that image to the HTML to be displayed???
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


How do you pass the image into SQL server? must be through a #var#. If so use the same concept to retrieve it just as a another column.
Take a look at this article, it shows how to store and retreive images from
SQL server.,4413,2180309,00.html

Hope this helps.


------ Sample ASP Code ------

<%      Set dbConn = Server.CreateObject("ADODB.Connection")
        dbConn.ConnectionTimeout = Session("Grocevb_ConnectionTimeout")
        dbConn.CommandTimeout = Session("Grocevb_CommandTimeout")
        dbConn.Open Session("Grocevb_ConnectionString"), Session("Grocevb_RuntimeUserName"), _
        Set cmdTemp = Server.CreateObject("ADODB.Command")
        Set rstProduct = Server.CreateObject("ADODB.Recordset")
        cmdTemp.CommandText = "SELECT prod_id, prod_name, prod_desc, prod_unitprice,
                                        prod_unitquant, prod_imagetype " & _
                                        "FROM Products ORDER BY prod_name"
        cmdTemp.CommandType = 1
        Set cmdTemp.ActiveConnection = dbConn
        rstProduct.Open cmdTemp, , 0, 1

        objImageLoader.DbName = "Grocevb"
  objImageLoader.TableName = "Products"
  objImageLoader.NameOfImageColumn = "prod_image"
        objImageLoader.NameOfImageTypeColumn = "prod_imagetype"
        objImageLoader.NameOfImageIdColumn = "prod_id"
<TITLE>Example for loading images from SQL server</TITLE>

<%Do While Not rstProduct.EOF %>
        <%ProdId = rstProduct("prod_id") %>

        <% objImageLoader.CreateTempImageFile(ProdId) %>
        <% FileName =  "images/" & objImageLoader.ImageFile(ProdId) %>

        <TABLE border=1 cellpadding=0 cellspacing=0 width=100%>

        <TABLE border=0 cellpadding=5 cellspacing=0 width=100% cols=2>

                <td width=30%><IMG ALIGN=BOTTOM SRC= <% =FileName %>>
                        <p> </p>
                        <font size=2>Image type: <% = rstProduct("prod_imagetype") %>
                <td width=70%> <p align=center><font size=4><% =rstProduct("prod_name") %></font></p>
                        <p align=center><% =rstProduct("prod_desc") %></p>


        <p> </p>
        <TABLE border=0 cellpadding=5, cellspacing=0 width=100% cols=2>

                <td width=50%><p align=left>Unit quantity: <% = rstProduct("prod_unitquant") %></P>
                <td width=50%><p align=left>Unit price: <% =rstProduct("prod_unitprice") %></p>

        <p> </p>

        <% rstProduct.MoveNext %>
<% Loop %>


Think about the way the image tag works in html.  It specifies the location of an image FILE on the server.

Therefore, instead of storing the image in your database store the location of the image file in your database.

An example of this approach is below.

SELECT imageLocation
FROM someTable

<IMG SRC="#imageLocation#">

Methods that retrieve images that are stored in a database involve writing a temporary file to the server and then deleting it.  Needless to say this will produce a lot of overhead.  So, you might as well use the above method and avoid all the overhead of writing the image to the server each time a request is made for that particular image.
jcorbinAuthor Commented:
Thats what I was afraid of...wanted a "complete" SQL Server solution, but I am seeing that there may not be one....

this may be your solution - CFX_GETIMAGE from the allaire gallery.

Yopu can retrieve the blob from SQLserver but you will have to store it temporarily as a file.
I'm not a cold-fusion user, but it's not necessary to store it as a file under either ASP or perl.

Simply set your content-type to the type of stored image (image/gif or image/jpeg) and BinaryWrite the image.  I don't know if that's a possibility under Cold Fusion, but it's not a problem with either perl or ASP.

Here's a sample under ASP.

  imagenum = request.querystring("n")

  set conn = server.createobject("ADODB.Connection") "Provider=SQLOLEDB.1;Password=myPwd;User ID=myUID;Initial Catalog=myDB;Data Source=myServer"
  set rs = conn.execute("select picture from tblPictures where pictureID=" & imagenum)

  Response.ContentType = "image/jpeg"

  set rs = nothing

  set conn = nothing

Maybe with the above code, someone can help you convert it to CFM.

Then you just include the image tag in your normal CFM/ASP/HTML document which refers to the script that pulls the image.

<img src="mypage.asp?n=4">

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.