Data Access and Transactions

Retrieving Image Data

Images (as with long bodies of text) are stored in a database as BLOB fields. Because of the added overhead for the DBMS, retrieving images from a database is slower than referencing an image URL on disk, so whether or not you store images in a database depends on the requirements of your application. You can retrieve image data with ADO by using the GetChunk method of the Field object. This method requires that you specify the number of bytes or characters that you wish to retrieve.

It takes two files working together to retrieve multiple images on the same page. The main file, which contains HTML formatting and IMAGE tags, requires the use of a separate ASP file to perform the actual image query. The secondary ASP file, Image.asp, retrieves the requested image, and returns it as a binary object in the HTTP response using a MIME content type of “image/gif.” The following is the source for Image.asp:

<%@ LANGUAGE=JScript EnableSessionState=False %>
<%
   var ID, rs, fld, cBytes
   ID = Request.QueryString("ID");
   if (ID + "" != "undefined") {
   rs = Server.CreateObject("ADODB.Recordset");
   rs.Filter = "ImageID=" + ID;  //--- Search criteria
   rs.Open ("Images", Application("ConnectionString"),
   adOpenForwardOnly, adLockReadOnly, adCmdTableDirect);
   if (!rs.EOF) {
            fld = rs("ImageData");     //Get field reference.
            cBytes = fld.ActualSize;   //Determine size.
   
   //Return raw binary image data as "image/jpeg" MIME type.
   Response.ContentType = "image/jpeg";
   Response.BinaryWrite(fld.GetChunk(cBytes));
         }
   else {
   Response.Write("Image '" + ID + "' not found.");
         }
   
   rs.Close();
      }
   else {
   Response.Write("No ID");
      }
%>

In the main file, images stored in the database can now be retrieved using the image ID as a URL parameter to Image.asp, like this:

<IMG SRC="./image.asp?ID=<%=ImageID%>">

When you use a firehose cursor, there isn’t a good way to discover the size of a BLOB field before you read it. If you must use a firehose cursor, consider maintaining a separate column in the database table that stores the image’s size in bytes. Otherwise, you could call the GetChunk method repeatedly until it returns nothing. Alternatively, if you think that the image will fit into available memory, simply use the Value property to retrieve the data all at once.