|Programmer to Programmer (TM)|
|Home | Today's Article | Search | Feedback | Write For Us | Suggest an Article | Advertise|
|Jul 19, 1999|
|By Dino Esposito|
|enter the discussion|
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.
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
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
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.
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
Response object to
response.Expires = 0
response.Buffer = True
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
Const OLEHEADERSIZE = 78
nFieldSize = rs("photo").ActualSize
oleHeader = rs("photo").GetChunk(OLEHEADERSIZE)
imageBytes = rs("photo").GetChunk(nFieldSize - OLEHEADERSIZE)
reads, in the first place, the actual size of an image field called
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 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.
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
' theImg.asp proxy page
response.Expires = 0
response.Buffer = True
response.contentType = Session("ImageType")
Session("ImageType") = ""
Session("ImageBytes") = ""
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
contentType = "image/bmp"
nFieldSize = field.ActualSize
oleHeader = field.GetChunk(OLEHEADERSIZE)
bytes = field.GetChunk(nFieldSize - OLEHEADERSIZE)
Session("imageBytes") = bytes
Session("imageType") = contentType
I defined four possible values as the parameter
ole . In
the first three cases, the content type is simply the string given by the value
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
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
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
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>
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
the name of the session variable for the image. I used a special naming
convention: I added
_type as a suffix to
each name passed, to obtain the bytes and the content type for any image. For
SetImageForDisplay oRS1("photo"), "ole", "empPhoto"
SetImageForDisplay oRS2("logo"), "gif", "compLogo"
The first image relies on two session variables like
empPhoto_type while the latter counts on
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")%>">
varName is simply the name I've chosen to pass an argument
to the ASP page. You'll find this code in
the use of the
temp parameter that merely serves to keep the full
name of the ASP page unique.
As you might expect, the solution shown in
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:
SetImageForDisplayfunction seen above.
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.
|enter the discussion|
Related Articles on ASPTodayPulling Images From A Database
Access to Data Sources Through ADO
Using ADO to Access An AS\400 Database
Benchmarking Different Approaches to Updating a Database
Downloading and Uploading BLOBs from a SQL Database Using a Browser
Downloading and Uploading BLOBs from a SQL Database Using a Browser. Part II of II
Serving up Ads with AdServer
Tip of the Day in ASP
Related LinksBook: ASP 2.0 Programmer's Reference
Book: Professional Active Server Pages 2.0
Tutorials: ActiveServerPages -- Database Tutorials
Article: 4GuysFromRolla -- Listing the Tables in an Access MDB Database Using ASP
Article: ZDNet -- Microsoft's quiet little database, by John Taschek
Article: PowerASP -- How to get a parameterized query working with an access database.
If you would like to contribute to ASPToday, then please get in
touch with us by clicking here.
ASPToday is a subsidiary website of WROX Press Ltd. Please visit their website. This article is copyright ©2000 Wrox Press Ltd. All rights reserved.