This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

MIND


This article assumes you're familiar with Active Server Pages, Visual InterDev, SQL Server 6.0, and C++.x.
Download the code (123KB)

Delivering Web Images from SQL Server
Scott Stanfield

Sometimes a GIF is more than a GIF. Scott Stanfield shows you how to retrieve images from a database and feed them directly to an end-surfer through an Active Server Page.
Let's say you want to manage your Web site images in a database. You might want to store product pictures for your cyberbusiness, hold scanned documents for a client project, or list photos for a family reunion (see Figure 1). No matter what the final application, there are two problems to solve: how do you get the images into the database, and then how do you get them out?

Figure 1: Image List
Figure 1: Image List


      To make things more interesting, suppose you want to submit images directly from a Web browser, as shown in Figure 2. The GIF or JPEG image must travel through a Web page, onto the Web server, and into a database record.

Figure 2: Adding an Image to a Database
Figure 2: Adding an Image to a Database


      You need a fair amount of technology to solve this problem. What I'll present here is a roadmap to a complete solution using a mix of different Microsoft® products. At the end of the guided tour, you'll have a set of ASP files and a useful component to add to your own projects.
      If you want to skip ahead and see the site in action, point your browser to http://www.vertigosoftware.com/mind. (Don't upload any images you wouldn't want your mother to see!) I built this entire site on a single well-configured workstation. The following lists every product used in this article. It looks like a lot, but most of the pieces are installed by the Windows NT® Option Pack.

      You'll also need Windows NT 4.0 Option Pack, which includes the following four items:       The first task is to get images from the client to the server using an HTML <INPUT> tag and a server-side ISAPI filter from Microsoft. Then, I'll script a custom ATL component (ImageDB) written in C++ to insert the file into an ADO field object from ASP. The component will have a method to remove the temporary file.
      The second task, retrieving images from the database, uses a special ASP page that morphs itself into a binary image. It changes its MIME type to image/gif and inserts the binary image from the database straight into the output.
      There are just five simple steps to this solution:
  1. Create a Picture table to hold the images.
  2. Write the ImageDB ATL component in C++.
  3. Create a virtual upload folder for the temporary images.
  4. Install and configure the Microsoft Posting Acceptor.
  5. Use Visual InterDev to create the site and ASP files.
      Let's get started!

Configuring the Database
      I need a simple, one-table database to store the images and descriptions. For this example, I'm going to use SQL Server, but you could create a similar table in a different database like Microsoft Access.
      First, create a 30MB database called Mind using SQL Enterprise Manager. Next, use the SQL Query Tool to create the Pictures table from the following script. The Bitmap column holds the GIF image bits, Description stores a short textual description of the image, and PictureID is the primary key. 


 CREATE TABLE Pictures (
     PictureID int IDENTITY (1, 1) NOT NULL,
     Description varchar (255) NULL,
     Bitmap image NULL 
 )
If you're using Microsoft Access to create your table, make the Bitmap of type OLE Object and the PictureID of type AutoNumber.
      Normally, the next thing I'd do is create the table using the Data View tools in Visual InterDev, but I don't have a project yet. If you want to get a head start with a few images, SQL Server ships with a sample database called pubs already installed. The pub_info table stores the GIF logos for the fictitious book publishers. The following query will populate the Mind..Pictures table from pubs..pub_info: 

 insert into pictures (description, bitmap)
 select convert(varchar(255), pr_info), logo
 from pubs..pub_info

ImageDB Server Component
      The ImageDB server component mentioned earlier is a COM object built using ATL in Visual C++ 5.0. The ASP code I'll implement will use ImageDB to attach a file on the server to any column in the database (I use Pictures.Bitmap).
      The next step is to create a new Visual C++-based project using the ATL COM AppWizard. Name the project Mind and accept all the defaults on the next dialog. Then insert a new ATL Simple Object with the short name ImageDB. Again, the defaults are fine for this dialog, so press OK. Visual C++ 5.0 generates the ProgID ImageDB.ImageDB.1, while Visual C++ 6.0 generates it as Mind.ImageDB.1. While the latter is slightly more descriptive, this article will assume you are using Visual C++ 5.0.
      Add the following import directive to the stdafx.h file. This wonderful line of code instructs the compiler to parse the ADO type library and build a C++ wrapper around the interfaces. You may have to change the #import statement depending where you keep your msado15.dll file. 


 // The following must be on one line in your code
 #import "c:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename( "EOF", "adoEOF" )
      You need three modifications to the project settings. First, enable exception handling (under the C++ tab) for all configurations. Next, remove _ATL_MIN_CRT from the preprocessor definitions for the Unicode Release mode configurations. Finally, add the full path for wscript.exe as the debug executable. Later, I'll create a script file called TestImageDB.vbs to drive this component. For now, add TestImageDB.vbs as the program argument.
      The Class View tab should show IImageDB. Right-click on IImageDB to add the following two methods: 

 interface IImageDB : IDispatch
 {
     [id(1), helpstring("SetImage")] 
     HRESULT SetImage([in] VARIANT ADOFieldDispatch, 
         [in] BSTR bstrFilePath);
 
     [id(2), helpstring("method DeleteFile")] 
     HRESULT DeleteFile(BSTR bstrFilePath);
 };
      The next step is to open the ImageDB.cpp file and merge the code from Figure 3. To understand SetImage, it is important to comprehend how it will be used from ASP. An often-used piece of ASP code that adds a new record to a table looks like this:

 Set rs = Server.CreateObject("ADODB.Recordset")
 rs.Open "pictures", Application("Mind_ConnectionString"), 2, 3
 rs.AddNew
 
 rs("Description") = "Ben's Birthday Photo"
 rs.Update
 rs.Close
The recordset object is opened with the name of the table where the new record will be inserted. AddNew readies the recordset for the data, then Update commits the transaction. In between, you assign each column a value. In this case, the column is assigned the string "Ben's Birthday Photo."
      What you want to do is assign a file to the Bitmap image column, like this: 

 rs("Bitmap") = "c:\temp\benphoto.gif"   ' this doesn't work
If that line had worked, I wouldn't have written this article. Instead, you'll have to pass the recordset field object and the path name to your component's SetImage function, like this: 

 imagedb.SetImage rs("Bitmap"), path
I'll cover the ASP code in detail a little later.
      Let's examine the C++ code. SetImage uses the ReadFromFile helper function to read a file into a BSTR. Then the BSTR is converted to a VARIANT and passed to the Field.AppendChunk method. How do you get the Field object? VBScript passes the IDispatch interface pointer as the first argument to SetImage. The #include in stdafx.h builds wrapper functions for the ADO type library. All you have to do is attach the IDispatch pointer to the Field wrapper.
      It may not seem obvious that you're being passed a Field object as the first parameter. After all, the first parameter in the VBScript code doesn't reference a Field object at all. If you sift through the ADO object model, you'll find that the default Recordset object is the Fields collection. Notice the plural. The default method for any well-behaved COM collection (like Fields) is Item. Since Item takes a field name as a string, it will return the individual Field object. In this case, the index Bitmap will return a Field object attached to the Bitmap column.
      At this point in the code you have the contents of the file in a BSTR and a valid Field object. Field supports a method for adding binary data called AppendChunk. AppendChunk expects a VARIANT and you have a BSTR. So all that's left to do is construct a VARIANT of type V_BSTR and pass the variant to AppendChunk.
      Earlier versions of SetImage passed a BSTR directly to AppendChunk, but I encountered a strange bug that had me baffled for hours. Since AppendChunk was expecting a variant, the compiler silently constructed a _variant_t object. Interestingly, _variant_t has a constructor that takes a BSTR, but its implementation uses SysAllocString instead of SysAllocStringLen to copy the BSTR. SysAllocString scans the BSTR to determine its length but stops scanning if it encounters a pair of NULLs—which are common in binary files like GIFs. I had to construct a VARIANT by hand to work around this silent casting problem.
      The helper function ReadFromFile takes a file name and a reference to a CComBSTR, the ATL BSTR wrapper. The file is opened as a memory-mapped file and copied into a BSTR. Before the function returns, the local BSTR is attached to the parameter bstrData. The memory-mapped files are probably overkill for small GIF images, but the method works.
      DeleteFile is a simple method. It just calls the SDK DeleteFile function. Your ASP code should remove the temporary file since it's no longer needed.
      The next step is to compile the code as Win32® Unicode Release MinDependency—or, if you wish to debug the code, as Win32 Unicode Debug. Next, create a text file called TestImageDB.vbs (as shown in Figure 4) and save it in the same folder as the project file.
      The Windows Scripting Host will run the script using wscript.exe. If you set a breakpoint in the code and press run, the project settings changed earlier will launch wscript.exe under the debugger with the TestImageDB.vbs file. It's a lot easier than debugging the component under IIS and ASP.
      I've used "c:\temp\vs4.gif" as the argument. You should modify this to reference a GIF you have lying around on your computer. You'll probably want to make a backup copy of the GIF since the next line of script code will delete the file!
      After you run the script, you can verify it worked by running a SQL query to display the contents of the Picture table. It won't show the bitmap, but it will return the size of the image. If it matches the real file size, then everything probably worked. Run this query in ISQL: 

 select PictureID, DATALENGTH(bitmap) as Bytes, Bitmap from Pictures
      Copy mind.dll to your Web server and register it with regsvr32.exe. Alternatively, you can drag and drop the file into a new package in MTS. It's easy to monitor the performance and instantiations using the MTS status view.
      You need a special place on the server to hold the submitted images. Since the files will only be there temporarily, the Temp folder is a good candidate. By creating a virtual directory called Upload on the Web server, you can control write privileges and provide a good place for the temporary files.
      Run the Internet Service Manager, also known as the Microsoft Management Console. Right-click on Default Web Site and create a new virtual directory. A wizard will guide you through the rest of the steps. For the alias, enter "upload." Type in the path to your temporary directory (like C:\Temp) on the next page and press Next. Turn off read and script access but enable write access on the last page.
      To support clients besides Microsoft Internet Explorer (such as Netscape Navigator) you'll have to change one more setting. You need to modify the allowed authentication methods for the scripts folder. The Posting Acceptor DLL lives in the Inetpub-\Scripts folder in a file called cpshost.dll. (If you don't have it installed, you can rerun the Option Pack setup or download a version from http://www.microsoft.com/windows/software/webpost. Look for a link that's called Microsoft Posting Acceptor 1.0.) The Posting Acceptor implements RFC 1867, the form-based file upload in HTML. It's what allows a browser to submit a binary file via HTTP. You can see the details at http://www.internic.net/rfc/rfc1867.txt.
      This version of the Posting Acceptor allows only anonymous uploads, so users will be prompted for a Windows NT login and password. Since Navigator doesn't support the Windows NT Challenge/Response protocol, it will attempt to send the login in clear text. This creates a problem, since default IIS-based Web sites do not allow for clear text passwords. You can change this by displaying the properties for the scripts folder in the Management Console. Click the Directory Security tab and press the first Edit button. Enable the Basic Authentication option and press OK.

Create the Site
      Let's create several ASP pages using Visual InterDev.

  • Default.asp will show a list of pictures with hyperlinks to the actual image. Another hyperlink will allow you to add new images.
  • AddImage.asp will use a multipart form and a browse button to allow the user to locate and submit a GIF file.
  • AddImageAction.asp will use the ImageDB component to add a new record to the picture table. Because the Posting Acceptor is an ISAPI filter, it intercepts RFC 1867-compliant requests. When it finishes the file upload, it redirects control to AddImageAction.asp.
  • Picture.asp will be the gateway to the images in the database. It requires one parameter, the PictureID, passed in the URL. It will then change its content header to image/gif and use Response.BinaryWrite to emit the bits from the database straight back to the client browser.
Figure 5: Project File List
Figure 5: Project File List
      The final project file list is shown in Figure 5. Let's build the files! First, create a new Web Project in Visual InterDev 6.0. You can skip the layout options since you'll add a stylesheet later. Next, add a data connection to the database you created earlier. Right-click on global.asa to access the Create Data Connection menu. Call this connection Mind.
      Visual InterDev 6.0 stores the data connection string in an Application variable. If you're using Visual InterDev 1.0, you'll need to change the ASP pages to use the Session variable instead.
      Add a stylesheet called Styles.css to the project. Figure 6 shows the proper styles to put in it. Next, add a new file to your site called Default.asp and populate it with the code from Figure 7. This page uses a stock ADO query to get a list of pictures from the database. A simple table shows the PictureID, the description, and the size of the image in bytes. A hyperlink redirects back to this page and enables the thumbnails as shown in Figure 8.
Figure 8: Image List with Thumbnails
Figure 8: Image List with Thumbnails

      Now you need to turn the image bits into a true GIF file and send it to the client (see Figure 9). Create a new ASP file called Picture.asp and add the code from Figure 10. First, change the content type of the file from text/html to image/gif so the client interprets the stream as an image. Then a simple query inserts the image data into the page using Response.BinaryWrite.
      Now you have a script, Picture.asp, that can trick clients into seeing it as a GIF file. Anywhere in the ASP-generated HTML you would normally put an <IMG> tag pointing to a GIF file, you can substitute Picture.asp in the SRC= parameter. For example, if you normally reference a logo.gif file like this <img src="logo.gif">
      you can instead use this: 

 <img src="picture.asp?PictureID=42">
Here, logo.gif has a PictureID of 42 in the database.
      Now create a new page called AddImage.asp with the code from Figure 11. The first chunk of ASP code builds the action variable out of the path to the Posting Acceptor DLL (cpshost.dll) followed by the PUBLISH parameter. This parameter points to AddImageAction.asp, the code that will execute after the GIF image has been received by the server.
      I tagged the form element with an encoding type of multipart/form-data. This instructs the Web browser to send the form data using the RFC multipart specification. The hidden form variable TargetURL specifies where the files should be stored on the server.
Figure 9: Picture.asp Showing an Image
Figure 9: Picture.asp Showing an Image

      Finally, the last file! Put the code from Figure 12 in a new file called AddImageAction.asp. AddImageAction is where all the hard work pays off. Notice the first section of ASP code where a path string is built from a set of Form variables. These variables were sent by the Posting Acceptor. Remember, you don't actually go directly to AddImageAction.asp; the Posting Acceptor intercepts the multipart forms and redirects the server here after the file is transferred.
      The path is sent to an instance of your ATL COM object, ImageDB, through the SetImage call. The other parameter to SetImage is a recordset field object. After SetImage, DeleteFile removes the temporary file.

Conclusion
      I hope you have gained an appreciation for the impressive work you can do by combining ASP, databases, and custom server-side components. Special thanks to Scott Hernandez for aiding in the design of this solution.

From the July 1998 issue of Microsoft Interactive Developer.