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???
jcorbinAsked:
Who is Participating?
 
clockwatcherConnect With a Mentor Commented:
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")
  conn.open "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"
  response.binarywrite(rs("picture"))
  response.end

  rs.close
  set rs = nothing

  conn.close
  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.

<html>
<body>
<img src="mypage.asp?n=4">
</body>
</html>
0
 
anushaCommented:
jcorbin,

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. http://www.zdnet.com/devhead/stories/articles/0,4413,2180309,00.html

Hope this helps.

Anusha:-)
adesilva@hq.nasa.gov



------ 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"), _
                        Session("Grocevb_RuntimePassword")
        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"
        objImageLoader.OpenConnection  
%>
<HTML>
<HEAD>
<TITLE>Example for loading images from SQL server</TITLE>
</HEAD>
<BODY bgcolor="#FFFFFF" TOPMARGIN=8 LEFTMARGIN=4>

<%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>
       
                <td width=70%> <p align=center><font size=4><% =rstProduct("prod_name") %></font></p>
                        <p align=center><% =rstProduct("prod_desc") %></p>
                </td>

        </TABLE>

        <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>
       
                <td width=50%><p align=left>Unit price: <% =rstProduct("prod_unitprice") %></p>
                </td>          
       
        </TABLE>
       
        </TABLE>

        <p> </p>

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

</BODY>
</HTML>



0
 
benjaFLCommented:
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.

<CFQUERY>
SELECT imageLocation
FROM someTable
</CFQUERY>

<CFOUTPUT QUERY>
<IMG SRC="#imageLocation#">
</CFOUTPUT>

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.
0
 
jcorbinAuthor Commented:
Thats what I was afraid of...wanted a "complete" SQL Server solution, but I am seeing that there may not be one....

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

http://www.allaire.com/developer/gallery/index.cfm?ObjectID=7233&nocache=yes


Yopu can retrieve the blob from SQLserver but you will have to store it temporarily as a file.
0
All Courses

From novice to tech pro — start learning today.