Richard Knudson
One of the best applications of Internet technology is the ability to make your databases available to anybody with a Web browser. Richard shows you how to do this with Access databases using Microsoft’s Internet Database Connector, which is part of the Internet Information Server.
In the May 1996 issue of Smart Access I gave you an overview of how to develop Internet Information Server-based applications and briefly discussed the Internet Database Connector (IDC). In this article I’ll provide a more detailed treatment of the IDC and show you how to create both static and dynamic database publishing applications. In the examples I show, an Access database resides on a Web server running IIS, and clients can access that database using any Web browser.
There are a number of different ways to create interactive Web applications with Microsoft’s Internet Information Server (IIS):
IIS supports the Windows CGI, the Windows-specific implementation of CGI (Common Gateway Interface). This is the “traditional” approach to creating server-based programs that interact with a Web server to create dynamic HTML documents.
IIS also exposes its native API, the Internet Server API, or ISAPI for short. Even though this is its native API, it isn’t proprietary; rather, it’s being touted by Microsoft as an open standard that third-party Web servers can support, much as ODBC provides an open database API. You can create server-based Web programs by writing to ISAPI in C++, Delphi, or other languages. Alternatively, you can create Visual Basic 4.0 applications to do this, since ISAPI supports an OLE automation interface and VB 4.0 can create OLE automation server applications.
The Internet Database Connector (IDC) is included with IIS and is actually an ISAPI application created by Microsoft specifically to provide easy to use yet powerful database support for Web server-based applications. This is what I’ll focus on in this article.
The IDC allows you to access any ODBC-compliant server-based database from a Web browser. The IDC is actually contained within a DLL program, HTTPODBC.DLL, that’s installed on your Web server when you install IIS. IDC applications aren’t technically difficult to create; the most complicated thing you’ll have to do is construct a SQL statement to send to your database. However, you need to create three different files to support an IDC application, and sometimes coordinating all of the different pieces can seem confusing. These are the files:
The HTML file
The IDC file
The HTX file
The HTML file is the HTML document that is used by your browser to send information requests to the Web server and display any results that are returned. When you run an IDC application, your initial HTML document calls a file with an IDC extension located in an executable directory on a server running IIS.
The IDC extension file is a specifically formatted text file located on an IIS Web server, and it contains three required pieces of information: an ODBC datasource, a so-called template file that will format and process the results of your query, and the SQL statement that contains the query you’ll send to the database.
The HTX file is the template file pointed to by the IDC file. Its job is twofold: to process results returned by a database query or stored procedure, and to format those results, combining them with static HTML if necessary, before they are sent back to the Web browser. HTX stands for “HTML extension,” and as you’ll see in a moment, this is an apt description since Microsoft has extended standard HTML commands to do such things as loop through result sets, handle If/Then/Else logic, and work with variables. At first this alarmed me, since I worried that proprietary HTML extensions might limit these applications to browsers supporting the extensions. But don’t worry: all of the “extensions” are processed on the server by the IDC-by the time the results are sent back to the client, all the client sees is nice generic HTML code.
Figure 1 provides an overview of how these files interact with IIS and the IDC to create interactive Web-based database applications.
Figure 1. An overview of an IDC application.
All of the samples I discuss have to do with the fictional Acme Technical Education Company and demonstrate how Acme might make information on the various public classes it offers available via an Internet application. If you want to run these examples on an Internet Information Server without changing the code, follow these steps:
Create a directory (name it whatever you like) on your IIS machine and copy all of the sample files from the Developer’s Disk into that directory.
In the Internet Service Manager utility that comes with IIS, add that directory to the Directory list in the Directories tab.
Edit its properties by clicking the Edit Properties button with that directory selected, and give it the alias name of “acme.”
Make it an executable directory by selecting the Execute check box.
These steps create an executable directory in which you can place programs that IIS will know to execute when they’re called from a browser. In this case, the executable programs will be the Internet Database Connector’s IDC files. The alias name you assigned in step 3 makes it a “virtual” directory; that is, you can call it from a browser by its alias name, without having to use the fully qualified directory path. This is useful if you’re interested in security issues, since it means that examining the URL gives a potential hacker no information about your server’s directories.
In all of my code samples I call server-based programs using the URL http://localhost. This URL works if you are running locally-if you’re running your examples on a separate Web server, simply substitute the name of the server for that URL.
I’ll discuss two examples in this article, one simple and one more complicated. Both start from the same “home” page, which I’ll call ACME.HTM (see Figure 2).
Figure 2. The Acme home page.
The simple example I’ll explain in this section is run by clicking on the first link, on the text that says “Browse Class Areas.” The HTML code behind that link looks like this:
To browse a list of all of our class areas, click here: <A HREF="http://localhost/acme/acme1.idc?"> Browse Class Areas</A>
This code calls the server-based IDC file, ACME1.IDC. The “?” after the filename is necessary, and instructs IIS that the program can take parameters. The IDC file is shown here:
Datasource: acme Template: acme1.htx SQLStatement: + SELECT description, classarea + FROM classareas + Order By classarea
This IDC file contains the three required components I discussed earlier: Datasource, Template, and SQLStatement. While I’ve named the HTX file in this example using the same root name as the IDC file (ACME1), it isn’t required, but I’ve found it easier to keep track of all of the pieces by always doing so. The HTX file is shown here. If you’re familiar with the basics of HTML, you shouldn’t have much trouble creating HTX files-in this example, I’m simply using a standard HTML table to format the results:
<HTML> <HEAD> <TITLE>Acme Train</TITLE> </HEAD> <CENTER> <H1>Acme Class Areas</H1> </CENTER> <TABLE BORDER=1 CELLPADDING=1 WIDTH=75%> <TR> <TD><STRONG>Class Area</STRONG></TD> <TD><STRONG>Class Description</STRONG></TD></TR> <%begindetail%> <TR><TD><%classarea%></TD> <TD><%description%></TD></TR> <%enddetail%> </TABLE> <HR> </BODY> </HTML>
Note the <%begindetail%>... <%enddetail%> block of code. This is how the Internet Database Connector forces a loop through all of the rows in a result set. Within this particular loop, the reference to the fields from the database table are <%classarea%> and <%description%>. These references must match the name of the corresponding field from the table.
Probably the most interesting aspect of this HTX file is that it shows how you can intermix standard HTML commands (like the ones I use here to create a table) with HTX-specific commands (like the ones to loop through the query’s result set). This ability means you can create some pretty sophisticated applications using this approach, as I’ll demonstrate in the next section.
In this section, I’ll discuss the second and more complicated of the two IDC applications that are accessed from the Acme home page in Figure 2. Here are some of the points this example illustrates:
How to create a query-by-form application with the IDC.
How to create a “drill-down” application, in which one HTML document runs an IDC process that creates a second HTML document, which in turn calls another IDC process, and so forth.
How to pass arguments to an IDC application from a form.
First, I’ll take you through the application’s functionality and show you how a user might see it. Next, I’ll take you through some of the most important code to show you how to create something like this yourself.
The example is started with the following call to the ACME2.IDC file from the ACME.HTM page:
To select from a list of our class areas, and see detail on specific classes, click here: <A HREF="/acme/acme2.idc?">Class Area Query-by-Form </A><P>
Running this IDC process generates a second HTML document, this one a form with a drop-down list of Acme’s different class areas. From this list, a user can select an area to see more information (see Figure 3).
Figure 3. A simple query-by-form interface with the Internet class area selected.
When the user clicks on the Go button, the form is submitted. The following <FORM> tag specifies how the form is posted to the server:
<FORM METHOD = "POST" ACTION="acme2a.idc">
As you can see, this kicks off a second IDC process, this time running a file called ACME2A.IDC, which in turn refers to a template file called ACME2A.HTX. After this process runs, the user will see something like the screen shown in Figure 4.
Figure 4. The results of a QBF query.
Finally, if a user wants to see detailed information about one of these classes, he or she can click on the class code. This will begin a third (and, in this case, final) IDC process, which will run a file called ACME2B.IDC and pass the results back to the client through the similarly named template file, ACME2B.HTX. Here’s the line of HTML code that starts this final IDC process:
<TD WIDTH=20%><A HREF="acme2b.idc?classcode=I1010"> I1010</A></TD>
The <TD> tag identifies this as a table data element (that is, a cell). And the “classcode=I1010” after the question mark passes that class code as an argument to the ACME2B.IDC file. Figure 5 shows what the user will see after this process runs.
Figure 5. The results of a drill-down IDC application.
Now that you’ve seen how the application works, let’s take a behind-the-screens look at its most important components to shed some light on how you might go about creating your own powerful IDC applications. I’ve found IDC applications to be simple from a syntax and coding standpoint, but somewhat difficult from a “keeping track of all the different little pieces” standpoint. Figure 6 will give you a feel for how all of the pieces in this application fit together.
Figure 6. The different components of the Acme drill-down IDC application.
Let’s examine the most important of these components, the ACME2.IDC file that fires off the first query for this example:
Datasource: acme Template: acme2.htx SQLStatement: + SELECT description, classarea + FROM classareas + Order By classarea
Here’s the ACME2.HTX template file referenced from this IDC file:
<HTML> <HEAD> <TITLE>Acme Train</TITLE> </HEAD> <CENTER> <H1>Acme Class Areas</H1> </CENTER> Select Acme course area of interest: <FORM METHOD = "POST" ACTION="acme2a.idc"> <SELECT NAME="ClassArea" SIZE=1> <%begindetail%> <OPTION VALUE=<%classarea%>> <%description%> <%enddetail%> </SELECT> <INPUT TYPE="Submit" NAME="Go" VALUE="Go"> </FORM> <HR> </BODY> </HTML>
This is probably the most interesting code I’ve shown so far-it constructs the selection list of available class areas and creates the form you saw in Figure 3. This is a great illustration of how server-specific code with extensions is used to create nice generic HTML code for the client. In particular, here’s the block of code that runs on the server to generate the combo box from data stored in the classareas table:
<SELECT NAME="ClassArea" SIZE=1> <%begindetail%> <OPTION VALUE=<%classarea%>> <%description%> <%enddetail%> </SELECT>
It creates the following HTML code that generates the selection list you saw earlier:
<SELECT NAME="ClassArea" SIZE=1> <OPTION VALUE=AC2> Microsoft Access 2.0 <OPTION VALUE=AC7> Microsoft Access 7.0 <OPTION VALUE=BKO> Microsoft BackOffice ... <OPTION VALUE=WIN> Microsoft Windows </SELECT>
Remember that the HTML <OPTION> tag requires the VALUE attribute to work properly. In this example, I substitute in the value of the classarea field, so that after the user selects a value from the list, that field value can be passed as an argument to the next IDC process. That next process was specified by the ACME2.HTX file’s <FORM> tag:
<FORM METHOD = "POST" ACTION="acme2a.idc">
This calls another IDC file, ACME2A.IDC:
Datasource: acme Template: acme2a.htx SQLStatement: + SELECT ClassCode, ClassDescription, + '$'+Str(Cost) as ClassCost, Length + FROM curriculum + WHERE ClassArea = '%classarea%' + Order By ClassCode
Since this IDC file was called from the HTML form generated by the ACME2.HTX file, the value of any form fields will be passed automatically as parameters. That’s how I construct the dynamic WHERE clause in ACME2A.IDC: the text enclosed by percent signs will be interpreted by the IDC as an argument from the form that called this file. The single quotes are necessary to enclose a text or character field. So at runtime, this example would generate a WHERE clause like this:
WHERE ClassArea = 'Internet'
One other trick to note here is how I formatted the Cost field. Since Cost is stored as a currency field type in the ACME.MDC database, I need to do something so the result isn’t displayed like 795.0000. So in this case, I used the Str function to strip off the decimals, preceded it with a dollar sign for formatting, and used an “As” keyword to rename the formatted column.
You need to be careful when using functions with an ODBC database. Just because a database like Access has an ODBC driver does not mean you can pass every function Access supports through that driver. For example, if you try to use the Format function to duplicate the results I got, you’ll get an ODBC driver error. As far as ODBC’s concerned, not all functions are equal!
Here’s a section of code from the ACME2A.HTX template file referenced in the ACME2A.IDC file:
<TABLE BORDER=1 CELLPADDING=1 WIDTH=100%> <TR> <TD><STRONG>Class Code</STRONG></TD> <TD><STRONG>Description</STRONG></TD> <TD><STRONG>Cost</STRONG></TD> <TD><STRONG>Length</STRONG></TD> </TR> <%begindetail%> <TR><TD WIDTH=20%> <A HREF="http://localhost/acme/acme2b.idc? classcode=<%classcode%>"> <%classcode%></A></TD> <TD WIDTH=50%><%classdescription%></TD> <TD WIDTH=15%><%ClassCost%></TD> <TD WIDTH=15%><%length%></TD></TR> <%enddetail%> </TABLE>
This is the code that generates the screen from Figure 4. Remember that this allowed you to drill down to the subsequent screen with detailed information about a specific class. The approach used here is a good contrast to the one I used in the previous ACME2.HTX template file. In that HTX file I used a form to collect information, whereas here I use a hyperlink, passing what amounts to a WHERE clause after the “?.” You can create flexible applications using either, or in this case both, of these approaches.
In this article I presented a rather detailed treatment of how to create Internet Database Connector applications. The examples I showed were created using an Access database on the server, but the code would be similar, if not identical, going against any other server-based ODBC database. The only caveat to this statement has to do with the supported ODBC driver functionality, and whether you need to exploit any database-specific functionality on the server.
The examples I showed all used SELECT statements to create a query interface, but you can use other SQL statements supported by your database. In particular, experiment with using an INSERT statement to create a “guestbook” type application. For example, Acme might use such functionality to allow online registration for its public classes. I know of at least one education firm that is doing this, and it works great!
The Internet Database Connector is the most flexible and powerful approach I’ve seen for creating custom Web front-ends to server-based databases, and I recommend it highly. To nit-pick a bit, one enhancement I’d really like to see would be some server-based administrator tools (perhaps with a wizard interface?) to make it easier to create and track all the different components of an IDC application. Microsoft’s dbWeb product allows you to create code-free Web QBF applications, but it’s a low-end product, and doesn’t give you the complete flexibility of the Internet Database Connector. I’d like to see something like the dbWeb interface used to create true IDC applications that you could then tweak to customize. Developers-give ‘em an inch, and they want a mile! s
Microsoft’s Internet Strategy: An Update
In the May 1996 issue of Smart Access I presented an overview of Microsoft’s Internet strategy and discussed a number of different ways of creating dynamic server-based applications. Since five human months are equivalent to about a year and a half in “Internet months,” I thought I’d provide an update on the latest from Microsoft in the area of interactive (read: “Internet and intranet”) technologies.
Microsoft’s marketing description of its strategy is short: “embrace and extend.” It looks to me like there’s plenty of substance to this claim. For example, Internet Explorer 3.0 (IE 3) is close to release, and it will be the first browser to support many of the World Wide Web consortium’s latest specifications, such as style sheets and complete HTML 3.0 tables. Another example of how Microsoft is aggressively embracing existing technologies is its support for JavaScript in IE 3. JavaScript is Netscape Navigator’s native scripting language, and it simply works in IE 3. For developers, it’s the extend part that’s most exciting; in particular, support for VBScript and ActiveX in IE 3 will make it the first browser to be useful as a serious online application development platform.
OK, so what’s ActiveX all about? This is an umbrella technology that’s the foundation of much of Microsoft’s interactive strategy, so let’s start there. Based on the Component Object Model (COM) specification, ActiveX is a collection of technologies that includes ActiveX controls, active documents, ActiveX scripting for both clients and servers, and code signing to address security concerns. To many developers, ActiveX controls will be the most visible and immediately useful part of all this, because these are the OLE controls that many of us are already using. There are some minor differences in the new ActiveX control specification (mainly to make them smaller and more suitable for distribution in a low-bandwidth environment), but in general, if you know how to work with OLE controls, you know how to work with ActiveX controls.
Here’s a case in point: a developer at my company has created an IE 3-based application that uses the Sheridan Calendar Widgets OLE-er, ActiveX-control to provide calendaring functionality to a Web client. This application uses VBScript to manipulate the object model (properties, methods, and events) of the Calendar control, just as you would in VB or Access.
In order to be a serious client-side development platform, IE 3 will need some more sophisticated authoring tools than Notepad. Available now for download from www.microsoft.com is a pair of related tools, ControlPad and the HTML Layout Editor. These improve on the current state of affairs by letting you visually lay out IE 3 pages, access the property dialog boxes of ActiveX controls, and so forth. They fall short of a complete IDE however; look for the release later this year of Internet Studio to fulfill the requirements of high-end authoring of interactive IE 3-based applications.
The mid-level authoring tool is Microsoft FrontPage. Available now in its first Microsoft release (1.1), FrontPage is an excellent no-code-required Web site management and authoring tool. It uses wizard technology extensively to let you easily create and manage Web sites, but doesn’t support high-end developer features like ActiveX and VBScript.
Besides the Internet-centric tools such as Internet Studio and FrontPage, every significant Microsoft product has or will soon have built-in HTML and ActiveX authoring capabilities. For example, there’s a cool tool called dbWeb you can download for free from www.microsoft.com and automatically (using wizards, no code or SQL required) create a query-by-form Web page for any ODBC-compliant database, including Access. This is neat, but the tool would be even better if it were directly integrated with a database such as Access. Look for something like this to happen in the next release.
What about the server side of the equation? Microsoft Internet Information Server (IIS) will continue to be the flagship Microsoft Web server product and will be released in its 2.0 incarnation later this year. IIS will be included as part of NT 4.0, much as IE will be integrated with Windows. To show you how far Microsoft is going to integrate its Web tools with its operating environments, a “peer” version of IIS will be included with the Windows NT 4.0 Workstation product so that any machine in an NT workgroup can act as an intranet server. IIS will soon be joined by other server offerings to support such features as increased security and firewalls, online commercial transactions, and multimedia services.
Besides the Internet server products themselves, there are some other server-side technologies that are equally important to developers. In particular, server-side scripting will be released in the not too distant future and will extend to an IIS server functionality similar to what VBScript provides in IE 3. That is, a subset of good old VBA will be available for creating dynamic Web pages, querying databases, or manipulating interesting ActiveX controls¾but all on the server, without having to distribute code and run it on client machines.
And finally, let’s not forget the almighty Java. Microsoft’s implementation of Java should be a very important product: great IDE (DevStudio), fast compiler, just-in-time compiler for faster runtime applet execution, and tight integration with COM so you’ll be able to both create and use COM objects in Java. Lest you think this isn’t important to you the Access developer, objects like DAO are exposed via COM. This means you could use things like DAO or RDO directly in a Java applet for distributed database applications. This will be cool, to use a technical term! The only problem with the currently alpha-stage product is its name. Visual J++? Yuck! Say it ain’t so, Microsoft!
So there’s a quick tour of some of the most important pieces of the Microsoft Internet steamroller. For those of us with a commitment to a good working knowledge of Microsoft developer and database tools, these are going to be interesting times! -Richard Knudson
Richard Knudson founded The Information Management Group (IMG) in September 1987 to help corporate clients better utilize the rapidly expanding power of desktop computers. Specializing in corporate and work group database applications, IMG provides consulting, training, and development services to its customers. Richard is a principal author of the IMG Visual Basic and Access training suites. He is the president of the Chicago Access Users Group and is currently helping customers implement interactive Internet applications. 312-222-9400, richardk@imginc.com, www.imginfo.com.
To find out more about Smart Access and Pinnacle Publishing, visit their website at:
Note: This is not a
Microsoft Corporation website.
Microsoft is not responsible for its content.
This article is reproduced from the October 1996 issue of Smart Access. Copyright 1996, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call (800)788-1900 or (206)251-1900.