Serving Images from SQL Server to a Web Browser

by Jia Wang

Microsoft SQL Server has the capacity to store images and large amounts of text in a SQL database. The data types for the image and the text are image and text respectively. If you develop a front-end application, either by using Visual Basic or Visual C++, running under the windows operating system, you can easily retrieve the image data from the database, put it into a file, and show it on the screen. However, it's not as easy to get an image from a SQL Server to display in a web browser. Although Web Assistant and the stored procedures, sp_makewebtask, sp_runwebtask, which are packaged with Microsoft SQL Server 6.5 can convert the data in the SQL database to an HTML file, better solutions exist. One of the problems with the Web Assistant is that it doesn't accept the column of image data type. Furthermore, sp_makewebtask has so many parameters that it's difficult to use and it's also difficult to customize web pages. We've come up with a solution other than sp_makewebtask to load images from a SQL database to a web browser. We developed a web project by using an ActiveX DLL and Microsoft Active Server Pages (ASP). The DLL is responsible for opening the connection to the SQL Server database where the images are stored, retrieving them and saving them to temporary files. The ASP will communicate with the DLL and get the location of the temporary image files. As a result, when a visitor knocks your web site, the image will be downloaded to his browser. In this article, we'll describe the steps to set up a SQL database. Then, we'll show you how to create the ActiveX DLL using Visual Basic 5.0 and Active Data Object 1.5 (ADO). Finally, we'll discuss the process for creating the ASP Web Project, connecting the SQL database, and writing the ASP file.

Setting up the SQL database

The database used for this article is very simple, containing only one table, named Products, representing different types of products in a grocery store. The first step in setting up the database is to create a database device in the Microsoft SQL server. Although you can write SQL scripts to set up the database, it's much easier if you use SQL Enterprise Manager. Open the Enterprise Manager and highlight the Database Devices in the tree view. Right-click the mouse and choose New Device from the floating menu. At the Name field, type Grocevb_dev and give the size of this device 2MB. Then, click the Create Now command button. Subsequently, do the same to create another database device as a log file. We'll name this device Grocevb_log and give it 1MB. Next, you need to build a new database. Highlight the Databases in the tree view and right-click. Then, choose New Database from the floating menu. In the dialog box for creating new databases, type the database name as grocevb, and choose Grocevb_dev as the data device and Grocevb_log as the log device from the pull-down lists. Afterwards, click the Create Now command. We'll ignore the issues of database security, database backup, user access, etc., because they're not directly relevant to the topic of this article.

The database creates only one table named Products. Several columns exist in the table. Among them, two columns named prod_image and prod_imagetype have to be mentioned particularly. The data type of the prod_image column is image, which contains the image for a particular product. The prod_imagetype column stores the type of images, like .gif or .jpg, which are the two image types most commonly used over the Internet. The primary key of this table is prod_id, which is non-clustered and indexed. Thus, each image has a unique prod_id. The SQL scripts for creating the Products table is given in Listing A. Only four products have been put in the table, for the purposes of this article.

Listing A: SQL script for creating the products table


/** Used to create the product table for ImageLoader VB DLL **/
CREATE TABLE Products
(
	prod_id	int   CONSTRAINT pk_prod PRIMARY KEY NONCLUSTERED IDENTITY(1, 1),
	categ_id 	int   NULL   CONSTRAINT fk_prod REFERENCES categories(categ_id),
	prod_name	char(80)   NOT NULL UNIQUE   NONCLUSTERED,
	prod_unitprice	money   NOT NULL,
	prod_unitquant	char(15)   NOT NULL,
	prod_desc	varchar(255)   NULL   DEFAULT ('unknown'),
	prod_imagetype	char(3)   NOT NULL,
	prod_image	image   NOT NULL,
)
GO

Creating the ActiveX DLL

Using VB 5.0, we'll develop an ActiveX DLL, which will allow us to connect to the SQL database, retrieve the image data, and save them into files in a directory. To create a DLL, follow these steps after opening the Visual Basic application. First, from the File menu, click New Project. Next, in the New Project dialog box, double-click the ActiveX DLL icon. Visual Basic automatically adds a class, named Class1, to the new project. Then, in the Properties window, double-click the Name property and change it to clsImageLoader, and leave the instancing property as MultiUse, the default value. Finally, from the Project menu, click Project1 Properties to open the Project Properties dialog box. Select the General tab, and change the project name to ImageLoader. The other fields may be left as they are. Click OK to finish the setup of the ImageLoader project. Five Let properties, a Get property (read-only property), and two methods are written in the class. While the Let properties pass the information required by the DLL, the Get property provides the image file location generated in the DLL. The two methods are used to open the connection to the database to create the image file. Table A summarizes the functions of the properties and methods. Of course, more sophisticated features could be added into the class depending on the requirements of the project.

Table A: Properties and methods coded in the ActiveX DLL

Property/Method Function
DbName Name of database required to open the connection to the database.

TableName, NameOfImageColumn, NameOfImageTypeColumn, NameOFImageIdColumn Information required to open a recordset.

ImageFile (Read-only) Returns the image file name and location to the container.

OpenConnection (method) Opens the connection to the database.

CreateTempImageFile (method) Retrieves the image data from the database and save it into a directory.

Property/Method	Function
DbName	Name of database required to open the connection to the database.
TableName, NameOfImageColumn, NameOfImageTypeColumn, NameOFImageIdColumn	Information required to open a recordset.
ImageFile (Read-only)	Returns the image file name and location to the container.
OpenConnection (method)	Opens the connection to the database.
CreateTempImageFile (method)	Retrieves the image data from the database and save it into a directory.

There are other private subs in the class. They are used to initialize and terminate the class, open a recordset, create the image file, and to handle any possible errors.

Note that Microsoft Active Data Object (ADO) 1.5 is used to connect the SQL database. Other alternative data access methods can also be used, such as Remote Data Object (RDO). In the project, you must reference ADO. You can do so by clicking the Project menu and then selecting the References menu item. From the References dialog box, check the item Microsoft ActiveX Data Objects 1.5 Library, and then click OK.

The complete source code for the ImageLoader class is shown in Listing B. The definition of variables used in the class is given in the code itself, together with other comments to help understand the context.

Listing B: The ImageLoader class


Private mAdoConn As New ADODB.Connection
Private mAdoRst As New ADODB.Recordset
Private mstrDbName As String
Private mstrTableName As String
Private mstrImageColumnName As String 'Name of image column.
Private mstrImageTypeColumnName As String 'Name of image type column.
Private mstrImageIdColumnName As String 'Name of Image Id column.
Private mstrFileName() As String 'Array containing the file name and path.
Private mlngImageId() As Long 'Array containing the image Id.
Private mlngNumberOfFiles As Long
Const BLOCKSIZE = 102400

Public Property Let DbName(ByVal strVal As String)
   mstrDbName = strVal
End Property

Public Property Let TableName(ByVal strVal As String)
   mstrTableName = strVal
End Property

Public Property Let NameOfImageColumn(ByVal strVal As String)
   mstrImageColumnName = strVal
End Property

Public Property Let NameOfImageTypeColumn(ByVal strVal As String)
   mstrImageTypeColumnName = strVal
End Property

Public Property Let NameOfImageIdColumn(ByVal strVal As String)
   mstrImageIdColumnName = strVal
End Property

Public Property Get ImageFile(ByVal ImageId As Integer) As String
Dim intPos As Integer
Dim blnFindId As Boolean
Dim i As Integer
   
   blnFindId = False
   
   For i = 0 To mlngNumberOfFiles - 1
      If mlngImageId(i) = ImageId Then
         intPos = 5 + Len(ImageId) + 3
         ImageFile = Right(mstrFileName(i), intPos)
	 'reformat the location of file.
         blnFindId = True
      End If
   Next i
   
   If blnFindId = False Then
      Err.Clear
      Err.Raise vbObjectError + 23, "Get ImageFile", "Can't find image file!"
   End If
         
End Property

Public Sub OpenConnection()
'**********************************************************
'Purpose: Open a database connection.
'**********************************************************
On Error GoTo Error_handler
   
   If mstrDbName = "" Then GoTo Error_handler
   
   If mAdoConn.State = adStateOpen Then mAdoConn.Close
  
   mAdoConn.ConnectionString = "DRIVER={SQL Server};SERVER=(local);
	UID=sa;PWD=;WSID=JIA;DATABASE=" & mstrDbName
   mAdoConn.ConnectionTimeout = 15
   mAdoConn.Open
   
   Exit Sub

Error_handler:
   Call HandleError
End Sub

Public Sub CreateTempImageFile(ByVal ImageId As Integer)
Dim strImageType As String
Dim i As Integer
'**********************************************************
'Purpose: Open a recordset, retrieve the binary data and put
'   it into a file. Note that file name is characterized by the
'   image id.
'Input: the image id.
'**********************************************************
   If mAdoConn.State = adStateClosed Then Exit Sub
   
   Call OpenRecordset(ImageId)
   
   If mAdoRst.State = adStateClosed Then Exit Sub
   
On Error GoTo Error_handler
   
   For i = 0 To mlngNumberOfFiles - 1
      'Check if the image file is already there.
      If mlngImageId(i) = ImageId Then Exit Sub
   Next i
   
   mlngNumberOfFiles = mlngNumberOfFiles + 1
   
   ReDim Preserve mstrFileName(mlngNumberOfFiles) 'Redimension the array.
   ReDim Preserve mlngImageId(mlngNumberOfFiles) 'Redimension the array.
   
   mlngImageId(mlngNumberOfFiles - 1) = ImageId
   
   strImageType = mAdoRst.Fields(mstrImageTypeColumnName) ' Get the image type.
   
   mstrFileName(mlngNumberOfFiles - 1) = App.Path & "\images" & _
      "\image" & LTrim(Str(ImageId)) & "." & strImageType 
	 'Image file name and location.
   
   Call ReadFromDB(mAdoRst.Fields(mstrImageColumnName), _
      mstrFileName(mlngNumberOfFiles - 1), AdoRst.Fields
	(mstrImageColumnName).ActualSize)
         
   Exit Sub
   
Error_handler:
   Call HandleError
   
End Sub

Private Sub OpenRecordset(ByVal ImageId As Integer)
Dim SqlText As String
'**********************************************************
'Purpose: Open a recordset.
'Input: the image id.
'**********************************************************
On Error GoTo Error_handler

   If mAdoRst.State = adStateOpen Then mAdoRst.Close
   
   SqlText = "SELECT " & mstrImageColumnName & "," & _
      mstrImageTypeColumnName & " FROM " & mstrTableName & _
      " WHERE " & mstrImageIdColumnName & "=" & ImageId
       
   Set mAdoRst.ActiveConnection = mAdoConn
   
   mAdoRst.Open SqlText, , adOpenStatic, adLockReadOnly 'Open recordset.
               
   Exit Sub
   
Error_handler:
   Call HandleError

End Sub

Private Sub ReadFromDB(fld As ADODB.Field, ByVal DiskFile As String, _
                     FldSize As Long)
Dim NumBlocks As Integer
Dim LeftOver As Long
Dim byteData() As Byte   'Byte array for LongVarBinary.
Dim strData As String    'String for LongVarChar.
Dim DestFileNum As Integer
Dim pic As Variant
Dim i As Integer
'**********************************************************
'Purpose: Retrieve the binary data and put it into a file.
'Input: the image column, file name/location and data size.
'**********************************************************

   If Len(Dir(DiskFile)) > 0 Then 'Remove any existing destination file.
      Kill DiskFile
   End If
   
   DestFileNum = FreeFile
   Open DiskFile For Binary As DestFileNum
   
   NumBlocks = FldSize \ BLOCKSIZE
   LeftOver = FldSize Mod BLOCKSIZE
   
   Select Case fld.Type
       
   Case adLongVarBinary 'For image data type.
   
      byteData() = fld.GetChunk(LeftOver)
      pic = fld.GetChunk(LeftOver)
      Put DestFileNum, , byteData()
      
      For i = 1 To NumBlocks
         byteData() = fld.GetChunk(BLOCKSIZE)
         Put DestFileNum, , byteData()
      Next i
      
   Case adLongVarChar 'For text data type.
      For i = 1 To NumBlocks
         strData = String(BLOCKSIZE, 32)
         strData = fld.GetChunk(BLOCKSIZE)
         Put DestFileNum, , strData
      Next i
      
      strData = String(LeftOver, 32)
      strData = fld.GetChunk(LeftOver)
         Put DestFileNum, , strData
      Case Else
         Err.Clear
         Err.Raise vbObjectError + 22, "Read from DB", 
	"Not a Chunk Required column!"

   End Select
   
   Close DestFileNum
   
End Sub

Private Sub HandleError()
Dim adoErrs As ADODB.Errors
Dim errLoop As ADODB.Error
Dim strError As String
Dim i As Integer
'**********************************************************
'Purpose: Handle the possible errors.
'**********************************************************

   If mAdoConn.State = adStateClosed Then GoTo Done
   
   i = 1
   Set adoErrs = mAdoConn.Errors

   For Each errLoop In adoErrs  'Enumerate Errors collection
      
      With errLoop
         strError = strError & vbCrLf & "   ADO Error   #" & .Number
         strError = strError & vbCrLf & "   Description  " & .Description
         strError = strError & vbCrLf & "   Source       " & .Source
         i = i + 1
      End With
   Next

Done:

   Err.Raise vbObjectError + 21, "", strError
   
End Sub

Private Sub Class_Initialize()

   mlngNumberOfFiles = 0

End Sub

Private Sub Class_Terminate()
Dim i As Integer
  
On Error GoTo Error_handler
     
   	If mAdoRst.State = adStateOpen Then mAdoRst.Close  
	'Close the recordset.
      
	If mAdoConn.State = adStateOpen Then mAdoConn.Close 
	 'Close the connection.
      
	Set mAdoRst = Nothing
	Set mAdoConn = Nothing

   	Exit Sub
   
Error_handler:
   Call HandleError
   
End Sub
   
Error_handler:
   Call HandleError
   
End Sub

The next step is to compile the DLL. From the File menu, click Make ImageLoader.dll. Visual Basic will ask you where to put the DLL and subsequently compile it. To use the ImageLoader.dll in ASP files, you must register it in the Window's Registry. You can do so by selecting Run from NT's Start menu, and typing the command line as follows:


	regsvr32  localpath\ImageLoader

Be sure to replace localpath with the full path to the DLL on your command line. Now, this DLL is ready to be used in other programs, such as ASP files. Creating the web project and setting up the database connection After registering the ImageLoader DLL, we're ready to create a web project. We use Microsoft Visual InterDev 1.0 to create Active Server pages and the data connection to Microsoft SQL Server 6.5. First, open Microsoft Visual InterDev. From the File menu, select the menu item New. In the dialog box that appears, highlight the icon Web Project Wizard and type ImageLoad in the Project Name field. Click OK and complete the next two steps to finish the Wizard.

You'll find that Visual InterDev has automatically generated a file named Global.asa in the Workspace window. This file is always created the first time any user views a page in the web application. To connect to the database in the SQL Server, select Project | Add To Project and choose the Data Connection. At this point, the Select Data Source dialog box appears and we have to define a database source name (DSN).

Click the New command button, and from a list of available database drivers, choose SQL Server and click Next. Then, type Grocevb as DSN in the input box. The rest of the steps are easy to complete.

Next, the SQL Server Login dialog box comes into view. Type the server's name, your LoginID and your password at each input box, and click the Option command button. Following this, from the list of available databases, choose Grocevb, and click OK.

Once you've finished all of these steps, you'll be back to the Select Data Source dialog box. There you can click the file name Grocevb.dsn from the DSN list and follow the steps to finish the process.

Now you can look at the code generated by the preceding steps in Global.asa by double-clicking on the file. The code defines the necessary variables to connect to the database in the SQL Server.

In order to initiate the clsImageLoader class, two lines of code must be placed at the beginning of the Global.asa. We list the code in Global.asa in Listing C. Note that clsImageLoader is the object which has a session scope.

Listing C: Global.asa


<OBJECT RUNAT=Server SCOPE=Session ID=objImageLoader 
PROGID="ImageLoader.clsImageLoader">
</OBJECT>

<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
		Session("Grocevb_ConnectionString") = "DRIVER=SQL Server; SERVER=(local); 
				UID=sa;PWD=;APP=Microsoft (R) Developer Studio;WSID=JIA; 
				DATABASE=Grocevb;UseProcForPrepare=0"
		Session("Grocevb_ConnectionTimeout") = 15
		Session("Grocevb_CommandTimeout") = 30
		Session("Grocevb_RuntimeUserName") = "sa"
		Session("Grocevb_RuntimePassword") = ""
End Sub

Sub Session_OnEnd
	set objImageLoader = Nothing
End Sub
</SCRIPT>

Creating Active Server pages

In order to connect the database and communicate with the ImageLoader DLL, we create an ASP in the web project by selecting the New menu item from the File menu. Choose the Active Server Page icon under the Files tab and give the file name in the input box. We name this page default.asp. The code containing in the default.asp is shown in Listing D.

Listing D: default.asp


<%	Set dbConn = Server.CreateObject("ADODB.Connection")
	dbConn.ConnectionTimeout = Session("Grocevb_ConnectionTimeout")
	dbConn.CommandTimeout = Session("Grocevb_CommandTimeout")
	dbConn.Open Session("Grocevb_ConnectionString"), Session("Grocevb_RuntimeUserName"), _
			Session("Grocevb_RuntimePassword")
	Set cmdTemp = Server.CreateObject("ADODB.Command")
	Set rstProduct = Server.CreateObject("ADODB.Recordset")
	cmdTemp.CommandText = "SELECT prod_id, prod_name, prod_desc, prod_unitprice, 
					prod_unitquant, prod_imagetype " & _
					"FROM Products ORDER BY prod_name"
	cmdTemp.CommandType = 1
	Set cmdTemp.ActiveConnection = dbConn
	rstProduct.Open cmdTemp, , 0, 1

	objImageLoader.DbName = "Grocevb"
  objImageLoader.TableName = "Products"
  objImageLoader.NameOfImageColumn = "prod_image"
	objImageLoader.NameOfImageTypeColumn = "prod_imagetype"
	objImageLoader.NameOfImageIdColumn = "prod_id"
	objImageLoader.OpenConnection 	
%>
<HTML>
<HEAD>
<TITLE>Example for loading images from SQL server</TITLE>
</HEAD>
<BODY bgcolor="#FFFFFF" TOPMARGIN=8 LEFTMARGIN=4>

<%Do While Not rstProduct.EOF %>
	
	<%ProdId = rstProduct("prod_id") %>

	<% objImageLoader.CreateTempImageFile(ProdId) %>
	
	<% FileName =  "images/" & objImageLoader.ImageFile(ProdId) %>

	<TABLE border=1 cellpadding=0 cellspacing=0 width=100%>

	<TABLE border=0 cellpadding=5 cellspacing=0 width=100% cols=2>

		<td width=30%><IMG ALIGN=BOTTOM SRC= <% =FileName %>>
			<p> </p>
			<font size=2>Image type: <% = rstProduct("prod_imagetype") %>
		</td>
	
		<td width=70%> <p align=center><font size=4><% =rstProduct("prod_name") %></font></p>
			<p align=center><% =rstProduct("prod_desc") %></p>
		</td>

	</TABLE>

	<p> </p>
	
	<TABLE border=0 cellpadding=5, cellspacing=0 width=100% cols=2>

		<td width=50%><p align=left>Unit quantity: <% = rstProduct("prod_unitquant") %></P>
		</td>
	
		<td width=50%><p align=left>Unit price: <% =rstProduct("prod_unitprice") %></p>
		</td>		
	
	</TABLE>
	
	</TABLE>

	<p> </p>

	<% rstProduct.MoveNext %>
<% Loop %>

</BODY>
</HTML>

Note that CreateTempImageFile method and ImageFile read-only property defined in the ImageLoader class require an image ID as the parameter. The image ID in this example is the primary key (prod_id) of the Products table. Thus, the value of prod_id must be passed to the DLL. Figure A shows the results of completing the Web project.

Figure A: Loading images directly from SQL database to the Web browser can be done with a DLL and ASP.

Conclusion

In this article, we've demonstrated a method for loading images from a Microsoft SQL server to a web browser by using an ActiveX DLL and ASP. It's been shown that this method has the advantages of fast development, easy use, and flexibility in customizing web pages. The DLL is used to connect a database, retrieve the image data, and put it into a file. The ASP page is responsible for the communication with the SQL database and the DLL. We've also shown the process of setting up the SQL database and a web project. Although the functionality of the DLL and the database in this article are simplified, the steps explained here are valid for any other powerful and complicated web project.