Real World Web
by Jay Lorenzo Once just found on high-end sites, dynamically generated Webs are quickly becoming a mainstay of current Web development. Database-driven designs can allow Web developers to deliver new content faster, and at the same time deliver customized content to users, without the need for maintaining multiple HTML document versions.
This capability has been technically possible since the beginning of the Web, but has not been widely implemented due to the lack of integration tools. Web developers were forced into writing implementation-specific tools, which took up more time and resources than most sites could afford.
Within the past 12 months, there has been an emergence of new tools that integrate database functionality with Web services without requiring external CGI gateway programs. Tools such as Netscape LiveWire, NeXT WebObjects, Allaire Cold Fusion, and Bluestone Sapphire/Web have made it much easier to implement database-served systems. In this column, we are going to take a look at some of the possibilities available for database integration with Microsoft Internet Information Server (IIS), which is becoming a popular platform for database delivery. Although most of the work I am involved with focuses on SQL Server 6.5, it is important to note that any ODBC-compliant database will work for the issues we discuss here.
For our example, let's consider what it takes to create a product section for an online shopping site that sells fans as one of its product lines. As with standard Web design, it is always a good idea to create design templates that can construct the documents that you intend to publish. Once this is accomplished, verify how that document design can be implemented in HTML, leaving placeholders for the information that will be supplied by the database.
Most of the tools currently available use this concept in one form or another, usually resulting in a hybrid document that contains both HTML and server-side tags that, when executed, output a completed HTML document. The mechanism supplied by Microsoft in IIS 1.0 and 2.0 is called the Internet Database Connector, or IDC.
The IDC is implemented as DLLs that are loaded by the server into memory at startup. The advantage of this approach is that there is no need to run a separate program each time a database request is made, which results in much lower overhead, and fast response times. For a more detailed explanation of how IDC works, take a look at Chapter 8 of the Installation and Administration guide. An HTML version of this file can usually be found on your system at [IIS InstallDirectory]\iisadmin\htmldocs\08_iis.htm.
IDC Generated Documents
The IDC implementation requires a minimum of two documents for each content source you create. The first file is a text file that is referred to as an .idc file. This file is responsible for generating the SQL request necessary for processing transactions with the database. Once the database has processed the request, the result set is returned to a .htx file, which is used to format the output. Both the .idc and .htx files are located in the server's CGI directory.
Let's take a look first at the .idc file, which will take the GET or POST INPUT values as criteria for the search or transaction we wish to make. It specifies an ODBC Datasource (which indicates the server and database to be used), a login/password combination for access to the database, the output template we wish to use, and the SQL commands to be executed. Here's a quick look at an simple .idc file called fan.idc, which will be used in the example for this column.
For background, let's assume that we had a link in an existing document that had a URL of http://www.digitalrain.net/cgi-bin/fan.idc?id=1003 attached to the picture of a ceiling fan. Let's also assume we have an existing database with a DSN (DataSource Name) of fandb, set up with the following fields: ProductID, ProductName, ProductDesc, UnitPrice, Image, SaleComment, CurrentStock, and Weight.
Here's the contents of the fan.idc file:
+SELECT ProductID, ProductDesc,
UnitPrice, Image, ProductName,
+Where ProductID = %id%
As the .idc file indicates, it expects the output to be processed through a template called fan.htx. Note that the SQL statement is prepended with a '+' for each line of SQL used, and the 'id' INPUT value specified by the GET is decoded by the IDC and retrieved as %id%. Once the SQL command is executed, the result set will be inserted into the fan.htx file. Let's take a look at that file:
<IMG SRC = "<%Image%>"
<FORM METHOD = "POST" ACTION = "/cgi-bin/order_item.idc">
<INPUT NAME = "ProductID" VALUE = "<%ProductID%>" TYPE = "HIDDEN">
<INPUT NAME = "Qty" VALUE = "1" SIZE = "4">
<INPUT TYPE = "SUBMIT" VALUE = "Add this item">
As you can see, this file contains placeholders for the database fields that are being returned. The placeholder for the ProductName field, for example, is specified as <%ProductName%>, which we will use both in the title and description field of the newly created page. Also note how we are, in effect, returning an input form with the document, allowing the user at this site to specify the quantity to be ordered.
One of the strengths of database-driven designs is that you can respond to user input with timely information. In the example above, we return a catalog page and an embedded form to allow the product to be ordered. This could be easily done with static HTML, but any time there was a change to the product (say, for example, the price for all fan products is discounted 25% for a week), it would mean that all of the documents would have to be updated.
By using a database-driven design, a simple stored procedure (a compiled set of SQL statements) can change those prices in the database, instantly affecting every subsequent request.
Limits of IDC
IDC is an easy to use tool, but it does present some limitations, particularly when you get into more complex designs: IIS 1.0, for example, only allows single SQL queries to be performed, and is limited in the number of HTTP environment variables that it can decode. IIS 2.0 corrects most of these deficiencies, allowing both batch and multiple SQL queries to be performed, but it does require that your operating system be Windows NT 4.0.
Particularly noteworthy of IIS 2.0 and beyond is the ability to create "connection pools," essentially allowing the Web developer to keep a connection open to a database, much like Netscape does with LiveWire. This is a much-desired feature when you are trying to maximize throughput, as it is not necessary for IDC to reconnect the ODBC datasource each time you have a request. And unless you plan to create your own ISAPI filters and extensions, IDC has a very limited syntax with respect to conditional logic.
Active Server Pages
Recently, Microsoft has created a new server-side layer that they refer to as Active Server Pages, for use with IIS 3.0. Within this framework, a Web developer uses a scripting language embedded in pages, much like the idea that is embodied in .htx files. Unlike .htx files, the Web developer has access to a wide variety of options. You can set up JScript, VBScript, or any ActiveX enabled component to be executed by the server in response to user interaction.
In this scenario, database components are referred to as Active Data Objects. Within each active server page (which uses an extension of .asp), you will create a database object, and then open the database object based upon the Datasource Name, much as you did with the .idc file. Note that this approach combines both data source and the formatting of results into a single page.
The real advantage of using ASP over IDC is an issue that goes beyond database access. With ASP, you immediately gain access to a fuller scripting environment and other Active Server components (which include objects for persistent cookies and browser specific functions), without any additional coding.
To get an idea of the added functionality gained, take a look at the enhanced version of the fan page at www.digitalrain.net/realworld/iisdb/fan-asp.html.
Reprinted from Web Developer� magazine, Vol. 3 No.1 Jan/Feb. 1997 (c) 1997. All rights reserved.