Turning a scripting language into an extensible powerhouse
Al is a Dr. Dobb's Sourcebook contributing editor. You can find Al on the Web at http://www.al-williams.com/awc/.
Not long ago, building a dynamic web page required you to write CGI scripts -- not very difficult, but a bit clumsy. With the introduction of Microsoft's IIS 3.0 (Internet Information Server), you can use Visual Basic (VB) to provide dynamic content -- well, sort of. In this article, I'll show how you can transform a boring web page to an interactive powerhouse using IIS 3.0, VBScript, and server-side ActiveX components.
Microsoft uses the term "VBScript" to describe the Basic language system in both Internet Explorer (IE) and IIS. However, the "V" in VB is supposed to stand for "Visual." Real VB is visual -- you create programs visually using a variety of drag-and-drop tools, wizards, and other modern conveniences.
VBScript, however, is a different story. Yes, VBScript programs are Basic. However, there is little that is visual about building them. For IE, you can use ControlPad or InterDev to give you a simplistic visual interface (one that is not much better than notepad for HTML). For server-side (IIS) scripts, you have to wing it with an ASCII text editor.
Still, in most cases, writing a server-side VBScript is better than creating a CGI script or ISAPI program. Not only is server-side scripting easier, but it is quite powerful. If you run into something that VBScript can't do easily, you can always create an ActiveX object (in VB or C++) that you can use from inside your script. With the proper objects, there is almost nothing a script can't do.
Basic Script Basics
To use server-side scripting, place your HTML in a file with an .ASP extension. These files are just like HTML files with three exceptions:
- IIS handles some server-side includes (SSIs) in these files.
- You can use <% and <%= tags to mark blocks of Basic code.
- You can use the RUNAT=SERVER option in <SCRIPT> and <OBJECT> tags to indicate that these tags apply to the server.
SSIs are nothing new. If you want to include one file in another, just use an include. For example, if the file COPY.INC contains the text in Example 1(a), then you might write an .ASP file like Example 1(b). The effect is that the copyright notice appears at the bottom of the page as though it were coded in the main file.
The interesting part is when you use the <% tag to create scripts. All statements in one .ASP file that appear between <% and %> tags are in the same VB module. The <%= tag is the same as <%, except it forces its value into the HTML stream. Consider a page that shows the Fibonacci numbers in Example 2(a). Notice that you can freely intermix HTML and VBScript code. You can even subject HTML to an if statement, as in Example 2(b).
VBScript and ActiveX
By itself, server-side VBScript is interesting, but not earth shattering. The real power of VBScript is its relationship with ActiveX. Scripts can access built-in ActiveX objects (see Table 1) to control IIS. Scripts can also create ActiveX objects and use them. These aren't usually the visual ActiveX objects you use on HTML pages. These aren't useful because they display on the screen. (You typically don't want to display things on the server's screen.) Instead, these are ActiveX components that generate HTML.
Microsoft supplies several components with IIS that you can use (see Table 1). There are other objects available as well. The Microsoft Personalization System, for example, allows you to recognize users and customize your script based on their preferences.
If you can't find a control you want to use, you can always write your own. In the past, you had to write sophisticated C++ code to create ActiveX objects. Now, Visual Basic 5 makes it easy to write them. You can write some objects with VB4, but it is a bit more difficult.
In this article, I'll show you how to use VBScript to connect to a database. The database in question (available electronically; see "Availability," page 3) tracks sightings of "the King" (a near-mythical figure spotted in various locations) and uses two VBScripts: DDJRPT.ASP (see Listing One and Figure 1which lets you enter data into the database; and DDJFIND.ASP (see Listing Two and Figure 2), which retrieves the data. To successfully create these two scripts, you need to understand more about IIS built-in objects and database access objects (DAOs).
Inside IIS
IIS provides several objects you can use to control its operation (see Table 2). For the purposes of the database example, you'll be most interested in the Request object, which contains data passed in as part of the query string, form data, certificate data, or cookies. It can also return server variables. In this case, you want the data from the input form, so you'll use the Request object to retrieve it.
The object contains collections for each type of data, so if you want to know the value of the Name field in a form, you could write: Thename = Request.Form("Name"). However, the Request object will search all of its collections by default. Suppose you write: Thename = Request("Name"). The object first searches for the variable in the URL's query string (the portion after the question mark). If it can't match the variable ("Name") there, it searches the form, then the cookies, then the client's certificates, and finally the internal server variables. The only time you need to specify a collection directly is if you think there might be a conflicting variable name. For example, if there is a query string entry called "Name," you'd need to explicitly ask for the Form collection to read a field value.
Reading the server variables can be useful, as well. The script in Listing One shows how you can encapsulate a form and the script that process the form in the same file. The trick is simple: Just check the server to see if the current request has any content (that is, form data). You can find out by examining the Content_Length server variable. If the variable is zero, then show the form. Make sure the submit button posts the data back to the same .ASP file. When the data is sent back to the form, Content_Length will be nonzero and you can process the data in the else part of the if. You can see the entire arrangement in Listing One.
Database Details
If you've programmed with Microsoft's DAO objects in VB or C++, you won't find any surprises with the database objects IIS uses. You open a connection to your database by creating an ADODB.Connection object. Then you have two choices:
- Call the Execute function to execute a SQL statement. This returns a record set.
- Create a RecordSet object and open a table. Then you can manipulate the rows in the table directly using the RecordSet.
Both methods have their place. If you are comfortable with SQL, you'll no doubt prefer the first method. However, getting all your arguments quoted and formatted properly can be a nightmare. Opening a table with a RecordSet object is simple, although there may be some performance penalty relative to executing SQL.
Listings One and Two show both methods. When inserting data into the database, Listing One uses a RecordSet. First, the code opens a table using the Open member of the RecordSet. Next, it creates a new record by calling AddNew. After that, you can treat any field in the database as a member of the RecordSet's default collection. For example, to set the Before field to zero, the code uses the line: RS("Before")=0. If it is more convenient, you can also reference the fields by number starting with index 0. This is often useful when using computed values in a SQL statement, or if you are writing code that has no prior knowledge of the fields in use.
Finally, the code calls Update to write the data to the database table. You can find a list of useful RecordSet members in Table 3.
Listing Two takes the SQL approach to generate an HTML table that contains the contents of the database. This still involves a record set, but in this case, the connection object creates the record set in response to the Execute statement (see Table 4 for common connection members). After the script calls Execute, it is a simple matter to create an HTML table. The code that does this is in two parts. The first part reads the field names from the database (the first field's name is in RS(0).Name, for example) and emits them as the table's headers.
The second portion of the script loops through each row in the table and writes out the fields. Because the script doesn't know how many fields you are using (or their names), it is quite general purpose. The only thing specific about this piece of script is that it chops off the last field. This allows the script to use Select * in the Execute statement and still not display the last field (the entry time). To make the code truly general purpose, you could change each for loop to terminate at RS.Fields.Count-1.
Odds and Ends
You can see a good use of server-side includes in both Listings One and Two. These scripts include ADOVBS.INC. This file ships with IIS and allows you to use symbolic constants instead of integers in many of the database calls. For example, instead of writing <% rs.Open "Tbl", Conn, 3, 3 %>, you can write <% rs.Open "Tbl", Conn, adOpenStatic, adLockOptimistic %>.
Another interesting point is the location of the VBScript code. Notice that the include statement comes before the <HTML> tag. This is perfectly legitimate. VBScript isn't HTML. It is more like a template that the server uses to generate HTML. Putting script outside the HTML is not uncommon. Don't confuse VBScript with HTML.
Summary
Although ASP won't completely eliminate CGI scripts or ISAPI programming, it certainly goes a long way toward making those tools obsolete (under IIS, anyway). Once you get used to server-side scripting you won't want to go back and write CGI or ISAPI -- it will seem much too difficult. Server-side scripting is an excellent example of how ActiveX can play a major role in future programming systems. ActiveX can turn a simple scripting language into an extensible powerhouse. Your own custom ActiveX objects can even access the built-in objects from Table 2.
Listing One
<!--#include file="adovbs.inc" --><HTML>
<HEAD>
<TITLE>Report Your Sightings!</TITLE>
</HEAD>
<BODY>
<!-- decide if data is present
If data is present, process
Otherwise, show form -->
<% if Request("Content_Length")=0 then %>
<H1>Report your King Sightings Here!<BR>
</H1>
<FORM NAME="KingForm" ACTION="ddjrpt.asp" METHOD="POST">
<PRE WIDTH=132>
<FONT SIZE=2>Date: </FONT>
<INPUT NAME="KDate" VALUE="" MAXLENGTH="8" SIZE=8>
<FONT SIZE=2 FACE="Courier New">
I've seen the King before: </FONT>
<INPUT TYPE="CHECKBOX" NAME="Before">
</p>
<FONT SIZE=2 FACE="Courier New">Location: </FONT>
<INPUT NAME="Loc" VALUE="" MAXLENGTH="128" SIZE=64>
<FONT SIZE=2 FACE="Courier New">
</p>
<FONT SIZE=2 FACE="Courier New">Your name: </FONT>
<INPUT NAME="Name" VALUE="" MAXLENGTH="128" SIZE=64>
<FONT SIZE=2 FACE="Courier New">
</p>
Comments:
</FONT>
<TEXTAREA NAME="Comment" ROWS=3 COLS=80>
</TEXTAREA>
</PRE>
<INPUT TYPE=SUBMIT NAME="Enter" VALUE="Enter" >
</FORM>
<%else %>
<% ' Using record set which is easy, but an Insert would
' have better performance (see DDJFIND.ASP)
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "KingSight","guest",""
set RS=Server.CreateObject("ADODB.RecordSet")
Conn.BeginTrans ' start a unit of work
rs.Open "Sighting", Conn, adOpenStatic, adLockOptimistic
rs.AddNew ' new record
rs("Date")=CDate(Request("KDate"))
if Request("Before")="on" then
rs("Before")=-1
else
rs("Before")=0
end if
rs("Location")=Request("Loc")
rs("Name")=Request("Name")
rs("Comment")=Request("Comment")
rs("subtime")=now
rs.Update
Conn.CommitTrans
rs.Close
Conn.Close
%>
<H1> Thanks for your report </H1>
<P>Thank you very much!
<A HREF=ddjfind.asp>Click here to view recent sightings!</A>
<% end if %>
</BODY>
</HTML>
Listing Two
<!--#include file="adovbs.inc" --><HTML>
<HEAD>
<TITLE>Find the King</TITLE>
</HEAD>
<BODY>
<H1>Recent King Sightings: </H1>
<!-- Create a connection to the database -->
<% Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "KingSight","guest",""
' Execute SQL select
SQL="Select * from Sighting order by date desc"
set RS = Conn.Execute(SQL)
%>
<P>
<TABLE BORDER=1>
<TR>
<!-- print headers -->
<!-- last field is hidden -->
<% For i = 0 to RS.Fields.Count - 2 %>
<TD><B><%= RS(i).Name %></B></TD>
<% Next %>
</TR>
<!-- print table (except for last field -->
<% Do While Not RS.EOF %>
<TR>
<% For i = 0 to RS.Fields.Count - 2 %>
<TD VALIGN=TOP>
<% if RS(i).Name<>"Before" then %>
<%= RS(i) %>
<% else %>
<% if RS(i)=0 then %>
N
<% else %>
Y
<% end if %>
<% end if %>
</TD>
<% Next %>
</TR>
<%
RS.MoveNext <!-- go to next record -->
Loop
RS.Close
Conn.Close
%>
</TABLE>
<P>
Be sure to add your sightings by clicking
<A HREF="ddjrpt.asp">here</A>.
</BODY>
</HTML>
DDJ
Copyright © 1997, Dr. Dobb's Journal
