Pulling images from an Access DB 

In a previous article, I demonstrated some techniques that could be used to retrieve images from a database for your ASP pages. I used GIF files stored as the binary content of a SQL Server table. Many readers wanted to know how to get the same result with Access databases. The technique I described was based on the content of the KB article Q173308 which states clearly that it doesn’t apply to Access or FoxPro databases.

In this article, I'm going to expand on the previous idea in that piece, investigate a couple of weak points in the standard solution, and demonstrate that the Access and FoxPro limitation is in fact not a matter of technology but simply a matter of record format. I'll show you how to pull images from Access tables and display them in ASP/HTML pages absolutely seamlessly.

Why Are Access and FoxPro Images Different?

When the browser meets a <IMG> tag, it downloads the file specified by the src attribute. This could be the name of an image file or the name of a new ASP page. If the latter then the ASP page must return a stream of bytes, which amount to a GIF. The browser, though, needs to know about the format of the bytes that it has received, so it can handle them appropriately. For this reason, you specify the proper content type, be it image/gif, image/bmp, image/jpeg or something else:

Response.contentType = "image/gif"

There is a problem with this line of code though: it means that the browser is expecting a GIF file and your application should pass a GIF file, and nothing else, from the first to the last byte. In other words, either the database should store the images as GIF files, or you have to manipulate the content so that it is a GIF file. Some databases store raw GIF files, others use JPEG images, and yet others use OLE to wrap a picture object. In all cases, you must know the details, and set the Response's contentType property accordingly. While JPEG and BMP files have a corresponding MIME type (image/jpeg and image/bmp), binary data stored as OLE objects in Access and FoxPro databases don't.

Let's suppose you create a new Access table and add an OLE object field. You insert a Bitmap image object into this field, and save it:

Notice that you can create a bitmap object from file too, and such a file might be a GIF or a JPEG file, not necessarily a bitmap. If you try to retrieve such a picture through ASP, you'll fail because what's actually stored in the database is neither a GIF nor a JPEG or a BMP file, despite the file you selected at creation time. What's stored in the database is not in a format that you can reasonably specify a MIME type that the browser could handle.

When storing images into databases, you should decide which format you want the image to have. You can store all the bytes of the image, or rely on the Access/FoxPro helper framework that lets you manage binary objects through OLE. Which format you choose at design time affects the way you retrieve images later on through ASP. In particular, when you use Access or FoxPro you store the image as a BMP file, even if you specified a GIF or a JPEG image (this has to do with OLE specification more than with Access or FoxPro). In this case, a content type of image/bmp doesn’t work because the image bytes are preceded by an OLE header, and this confuses the browser.

Before we set about finding a way to get rid of this header, let me say that this is what happens if you define the field as an OLE object and use the Access Insert Object dialog to fill it. (It's the dialog box shown above). You could always arrange your own made-to-measure module (I'll cover that in another article) to fill database fields the way that best suits your needs (using raw GIF, JPEG, BMP, TIFF, fractals and whatever else you need.) This said, let's see how to extract images from a fairly standard Access database.

Working with Access DB

There are two key points with Access database fields that contain images: the use of the BMP file format and the presence of a 78-bytes header before it. The first thing you need to do in order to work around these is set the content type of the Response object to image/bmp :

<%

    response.Expires = 0

    response.Buffer  = True

    response.Clear

    response.contentType = "image/bmp"

%>

Then you should strip off the initial 78-byte header of the OLE object, so you need to find a way to manage the byte stream. The ADO Recordset's GetChunk() method will do this: it reads the specified portion of a long binary field moving the internal pointer forward. The following code snippet

<%

    Const OLEHEADERSIZE = 78

    nFieldSize = rs("photo").ActualSize

    oleHeader = rs("photo").GetChunk(OLEHEADERSIZE)

    imageBytes = rs("photo").GetChunk(nFieldSize - OLEHEADERSIZE)

    Response.BinaryWrite imageBytes

%>

reads, in the first place, the actual size of an image field called photo (rs is the recordset) and then executes two successive calls to GetChunk. The first call merely moves the internal field pointer forward 78 bytes. The second reading actually extracts the image bytes and binary-writes them to the Response object.

The Weak Point of the ASP-proxy Approach

The only difference between this approach and the one demonstrated in my previous article is that now we have to account for the OLE header that Access and FoxPro use to store images. Despite the obvious pros, storing web images in a database does present a couple of cons that you need to be aware of. For one thing, you should make use of a proxy ASP page that actually takes care of retrieving the content of the image. Such a page has a fairly standard structure. For example: suppose you need to retrieve the record of an employee, which includes both notes and a picture. You might want a page that displays them at the same time, possibly making a single query to the database since the image and the other information are part of the same record. With the approach I demonstrated in the previous article you would have to use two queries instead - one for the personal notes and one for the image. The image requires a separate roundtrip because of its specific content type (image/gif, image/jpeg and so forth).

With the current stage of the HTML/ASP technology there's no way to avoid ASP creating a proxy page in order to pull an image from a database, but we can rewrite the proxy and so make it access an existing recordset instead of executing a brand new query.

Improving the ASP-proxy Page

The strategy here is to use the ASP Session object to store all the data the proxy page may need. Basically, it needs the bytes of the image and the content type. I designed the page (theImg.asp) to retrieve this information from two session variables called ImageBytes and ImageType respectively:

<% 

    ' theImg.asp proxy page 

    response.Expires = 0

    response.Buffer  = True

    response.Clear

    response.contentType = Session("ImageType")

    response.BinaryWrite Session("ImageBytes")

    Session("ImageType") = ""

    Session("ImageBytes") = ""

    response.End

%>

The page that sets the session variables also retrieves the recordset and displays the image. In order to do this, the page must know the format of the image in the database, and extract the right slice of the bytes. The following function takes a recordset field and a string denoting the type of the image field and fills the session variables:

Function SetImageForDisplay(field, contentType)

    OLEHEADERSIZE = 78

    contentType = LCase(contentType)

    select case contentType

        case "gif", "jpeg", "bmp"

           contentType = "image/" & contentType

           bytes = field.value 

        case "ole"

           contentType = "image/bmp"  

           nFieldSize = field.ActualSize

           oleHeader = field.GetChunk(OLEHEADERSIZE)

           bytes = field.GetChunk(nFieldSize - OLEHEADERSIZE)

    end select

    Session("imageBytes") = bytes

    Session("imageType") = contentType

End Function

I defined four possible values as the parameter contentType: gif, jpeg, bmp , ole . In the first three cases, the content type is simply the string given by the value prefixed by image/. Should the argument hold the string ole instead, then the function has to strip off the first 78 bytes from the field value and set the content type to image/bmp.

Putting It All Together

The download that comes with this article demonstrates displaying a page with all the information about an employee. It is based on the Access 97 Northwind database (I'm using a DSN of NW). The following code prepares the recordset and fills out the mentioned session variables:

<%

   sql = "select * from Employees"

   Set oRS = Server.CreateObject("ADODB.Recordset")

   oRS.CursorLocation = 3

   oRS.Open sql, "DSN=NW"

   SetImageForDisplay oRS("photo"), "ole"

   Set oRS.ActiveConnection = Nothing

%>

To display the image, you can just use the code in the getEmpInfo.asp file:

<img src="theImg.asp"</img>

This method does, of course, works but it has a serious drawback. Unless you arrange a very particular storage scheme, you will loose the names of the original images when you put the images in a database - they will all be retrieved as theImg.asp. Every time you search for another employee and name, you get a new image but the code saves it with the same name and overwrites previous images held in the local cache. If you click the back arrow of the browser, you'll get the right information for the employee redisplayed, but the wrong picture - it will always be the last one shown, because each HTML page refers to theImg.asp. To work around this, you should use a unique name for the ASP page rendering the image. The easiest solution is adding a dummy parameter that makes each full page name unique. For example, you could add a field with employee information using this code:

<img src="theImg.asp?temp=<%= Request.Form("empLastName")%>"</img>

What About Multiple Images?

This will work fine as long as you only want to display one image field at a time. It is, however, easy enough to allow multiple images. The more obvious solution consists of adding a new argument to SetImageForDisplay, the name of the session variable for the image. I used a special naming convention: I added _image and _type as a suffix to each name passed, to obtain the bytes and the content type for any image. For example:

SetImageForDisplay oRS1("photo"), "ole", "empPhoto"

SetImageForDisplay oRS2("logo"), "gif", "compLogo"

The first image relies on two session variables like empPhoto _image and empPhoto_type while the latter counts on compLogo_image and compLogo_type. This is how the associated code looks:

<img src="theImg2.asp?varName=empPhoto&temp=<%= Request.Form("empLastName")%>">

<img src="theImg2.asp?varName=compLogo&temp=<%= Request.Form("imgCode")%>">

where varName is simply the name I've chosen to pass an argument to the ASP page. You'll find this code in getEmpInfo2.asp. Notice the use of the temp parameter that merely serves to keep the full name of the ASP page unique.

Coming Soon…

As you might expect, the solution shown in getEmpInfo2.asp is more general than the previous one, and supersedes it. These are the steps which will allow you to display images pulled out from a database:

• Get the recordset, including the image fields. You must know which format the images in the database are in (raw GIF as in PUBS, OLE objects as in NorthwindAccess, or raw JPEG or whatever).
• Set the session variables to store image bytes and the content type. This information will be used by the ASP page linked to the IMG tag. Use the SetImageForDisplay function seen above.
• Put an IMG tag where you need the image, and set its source to the standard ASP page theImg.asp.

There will be a further article about image and database management with ASP sometime soon. This one will examine the choices you have when you want to realise an archive of database images.

Click here to download this articles support material.