by Lisa Hooper - GUI Computing
Until recently I had never really made much use of a database's capability to store image data. It was really just a feature for me to play around with but not something I had been able to make use of in a practical sense. All of a sudden clients are requesting online pricelists and catalogues with pretty pictures as part of their active data needs. Sure we can store all these images as files on the web server but storing them in the SQL Server database from which the rest of the product data is retrieved is a much neater and more secure solution.
I knew how I was going to get my image into the database from a VB client; the question then was how do I get the image out of the database to display on the asp page? At present there is no functionality in ASP scripting to actually do this. You cannot even retrieve the image using GetChunk methods and write them to a binary file which could then be displayed because there is no way to write binary files in ASP. Granted you could do all this by writing an activex dll in VB and talking to it from your ASP page but as far as I'm concerned the fewer interactions an asp page has with external objects the better.
The way to do it is to create a new asp page that constructs an HTTP header containing a mime type of IMAGE/GIF. The page will then use the binary information from your image field to provide a gif to the browser.
My ShowImage.asp page looks like this:
<%@ LANGUAGE="VBSCRIPT" %> <%Option Explicit Dim Rs, sSQL, OBJdbConnection ' Clear out the existing HTTP header information Response.Expires = 0 Response.Buffer = TRUE Response.Clear ' Change the HTTP header to reflect that an image is being passed. Response.ContentType = "IMAGE/GIF" Set OBJdbConnection = Server.CreateObject("ADODB.Connection") OBJdbConnection.Open "DSN=INVENTORY;UID=UserId;PWD=Password;DATABASE=dbInventory" sSQL = "Select * from tblInventoryItems where InventoryItemId = " & request.querystring("InventoryItemId") set rs = OBJdbConnection.Execute(sSql) If not rs.eof Response.BinaryWrite rs("ItemPicture") Response.End End If CloseRecordset(rs)%> <%Set OBJDbConnection = nothing%>
You must then refer to this ASP page in an image tag within the HTML or ASP page from which you want to display the image.
<%@ LANGUAGE="VBSCRIPT" %> <%option explicit Dim Rs, sSQL, OBJdbConnection Set OBJdbConnection = Server.CreateObject("ADODB.Connection") OBJdbConnection.Open "DSN=INVENTORY;UID=UserId;PWD=Password;DATABASE=dbInventory" %> <HTML> <HEAD> <TITLE>Inventory Tracking System</TITLE> </HEAD> <BODY> <%sSQL = "Select * from tblInventoryItems where InventoryItemId = " & request.querystring("InventoryItemId") set rs = OBJdbConnection.Execute(sSql) <%If not rs.eof then%> <%=rs("ProductName")%> <p> <IMG SRC="SHOWIMG.ASP?InventoryItemId=<%=rs("InventoryItemId")%>"> <%End if%> <%Set rs = nothing%> </BODY> </HTML> <%Set OBJDbConnection = nothing%>
It should be noted that this method requires that the raw GIF data follows the HTTP header. Applications such as MS Access add OLE headers to this raw data meaning that they cannot be displayed using this method. All in all, this easy to use method produces great looking web pages and certainly beats storing images on the file system.