Previous Page TOC Next Page Home


19

Databases and the Web

Databases are at the root of all business computing today. At some point, you are going to want to integrate a company database with your Web site. Or, perhaps when you have a large Web site, you'll want to create a database that will let clients search the text of your HTML files.

There are several ways to achieve database and Web site integration. If you have read Chapter 11, "Introduction to Web Forms and CGI Scripts," you are no doubt thinking of writing a CGI application that will suit your needs. But what if you are not a CGI programmer and you want to connect an off-the-shelf database, such as Microsoft Access, to your Web site? Or what if you want to give people the ability to search your Web site for the page that they are looking for and you don't have the time to learn Perl? Well never fear; this chapter is going to show you how to handle both of these situations, and more, without learning complex programming languages.

WAIS

WAIS, which stands for Wide Area Information Server, is a tool for sharing information on the Internet. Because WAIS uses the Z39.50 protocol, you might hear the two terms used synonomously.

There are a couple of ways to use WAIS (pronounced ways). One of the techniques is extremely simple and works basically like this: You build a WAIS index of all the HTML files at your site and include the <ISINDEX> tag in your home page. It's that simple, and your users will love you for it.

The European Microsoft Windows NT Academic Centre (EMWAC) has developed several excellent freeware programs for Windows NT. We featured many of their servers in our companion book Web Site Construction Kit for Windows NT. Programs that are written for Windows NT on the Intel platform will usually run on Windows 95 also. This is because both Windows NT and Windows 95 support the Win32 API, which enables programs to call operating system functions.

The WAIS Toolkit was developed by EMWAC to complement their Windows NT Web server, which is called EMWAC HTTPS. When you read the EMWAC WAIS Toolkit User's Guide, you might see the HTTPS mentioned, and vice versa. The HTTPS User's Guide contains information about setting up the WAIS Toolkit.

The EMWAC WAIS Toolkit included on the CD will help you create a database of all the text at your Web site so that users can search it by keyword. The creators of HTML designed the <ISINDEX> tag with this feature in mind. <ISINDEX> will cause the Web server to invoke a program named WAISLOOK to search a WAIS database and return links to the pages containing the search keyword. (The WAIS database is also referred to as an index.)

Now we'll talk about the steps involved in turning the YourCo sample site into a searchable site. First, you need WAIS.

Installing the WAIS Toolkit

Wouldn't you know it? The EMWAC WAIS Toolkit works very well with Folkweb and Purveyor. Here are the steps to install the WAIS Toolkit.

There are several misguided steps in the WAIS Toolkit readme files that these instructions attempt to clarify and correct.
  1. The WAIS Toolkit is distributed in four versions for the different architectures that Windows NT supports. As discussed above, most programs written for Windows NT on Intel are also compatible with Windows 95. From the CD \apps directory, select the appropriate ZIP file for Intel because that is the only processor on which Windows 95 runs: wti386.zip.

  2. Unzip the file from the CD-ROM to your hard drive. This should leave you with the following files:

    WAISINDX.EXE: The WAISINDEX program

    WAISLOOK.EXE: The searching program

    WAISSERV.EXE: The Z39.50 searching program

    WAISTOOL.DOC: WAISTOOLKIT manual in Word for Windows format

    WAISTOOL.WRI: WAISTOOLKIT manual in Windows Write format

    WAISTOOL.PS: WAISTOOLKIT manual, in PostScript ready for printing

    READ.ME: Summary of new features

  3. If you have installed a previous version of the toolkit, remove it by deleting the old files or by moving them to another directory (which is not referred to by the PATH environment variable) for deletion after you have validated that the new version works correctly.

  4. Decide which directory you are going to put the tools in and move the unzipped .EXE programs there. Ensure that the directory is on the path so that the commands may be executed from the command line.

  5. Determine which version of the toolkit you have. To do this, at the Windows 95 DOS Prompt, type these commands:

    waisindx -v

    waislook -v

    waisserv -v

    and the version number for each program will be displayed. In fact, two version numbers will be shown for WAISINDX and WAISSERV; the first refers to the version of the freeWAIS code from which the programs were ported, and the second is the number of the Win32 version. Here, we are describing version 0.7 for Windows NT. If the programs report a later version number, you will find an updated manual in the files you unpacked from the ZIP archive.

Creating a Simple WAIS Database

Here are the steps for creating a WAIS database of the HTML files at your site.

First get into the home directory of your Web site. Assume it's d:\http.

  1. Make d:\http the current directory.

  2. Execute waisindx, giving it parameters as shown below:

    waisindx -d index -t html *.htm*
  3. Observe the messages from waisindx to check that there are no errors. Note that the name of the executable file was shortened from waisindex to waisindx for compatibility with older versions of Windows that only allowed eight character filenames.

  4. Execute a DIR INDEX.* command on the d:\http directory to check that waisindx has created the seven index files, named index.*.

Here is a description of the files created by waisindx:

Using ISINDEX with WAIS

Now that the WAIS index files are created, you need to modify your HTML code to take advantage of it. This is where the HTML <ISINDEX> tag enters the picture. Remember, the HTTP server is designed to invoke WAISLOOK whenever it receives an ISINDEX request from the client.

Create a new search page named index.htm with the <ISINDEX> tag. You can load the sample file shown in Listing 19.1. The filename is \site\index.htm.


Listing 19.1. Index.htm is a sample HTML file that uses <ISINDEX>. 

<html><title>Search the YourCo Web Site</title>

<body>

<center>

<img alt="YourCo Logo" src="LOGO.GIF">

<h2>Search the YourCo Web Site</h2>

</center>

<p>To search the YourCo Web site just enter your search term(s) below.<isindex>

<hr size=5>

<i><a href="http://www.yourco.com/"><img src="RETURN.GIF">Back to YourCo Home

Page.</a></i>

<p>

<center>

This page, and all contents, are Copyright (C) 1995 by YourCo

Inc., San Diego, California.

</center>

</BODY>

</HTML>

Now we need to provide a link on our sample home page to the new index file just created. Listing 19.2 is a slightly modified version of the file default.htm from the sample site we created for YourCo in Chapter 13. This file is also included on the CD as default2.htm in the \site directory. You can just rename it as default.htm if you wish, assuming you haven't already customized the default.htm file from Chapter 13. If you have customized default.htm from Chapter 13, you should consider applying those changes to this file as well.


Listing 19.2. Default.htm modified to link to index.htm. 

<HTML>

<HEAD>

<TITLE>YourCo Inc. Home Page</TITLE>

</HEAD>

<BODY>

<IMG SRC="Logo.gif">

<H1>Welcome to YourCo!</H1>

Welcome to YourCo!  YourCo is the world's leading supplier of

something. We have been in business since 1994, and have had a 

Web presence since today.

<P>

Our newest and most exciting product is

<A HREF="product.html">Product here</A>. Select 

<A HREF="order.htm">here</A> to order your

own copy of Product here. You can also

<A HREF="custreg.htm">register for our customer newsletter</A>, 

sent out by E-Mail every

six weeks.<P>

We are pleased to provide the following additional information:

<P><UL>

<LI><A HREF="Release.htm">YourCo Press Releases</A><P>

<LI><A HREF="Lit.htm">YourCo Marketing Literature</A><P>

<LI><A HREF="Intro.htm">Introduction to our Products</A><P>

<LI><A HREF="Train.htm">YourCo Training Services</A><P>

<LI><A HREF="Consult.htm">YourCo Consulting Services</A><P>

<LI><A HREF="index.htm">Search the YourCo Web Site</A><P></UL> 

<!-- this line is new link -->

<H2>Comments or Problems</H2>

Again, thanks for visiting YourCo's WWW server. We hope to hear

from you again soon.<P>

For information on YourCompany's products and services, please send

e-mail to <a href="mailto: info@YourCo.com"><i>info@YourCo.com</i>

</a>, for an

automated reply<br>

or use our <A HREF="feedback.htm">feedback form</A> for other 

inquiries.<P>

This page, and all contents, are Copyright (C) 1995 by YourCo

Inc., San Diego, California.

</BODY>

</HTML>

Congratulations, now your site will be searchable by keyword. Use your Web browser and give it a try.

ODBC

Microsoft Open Database Connectivity (ODBC) is a standard programming interface for application developers and database systems providers. Before ODBC became a de facto standard for Windows programs to interface with database systems, programmers had to use proprietary languages for each database they wanted to connect to. Now, ODBC has made the choice of the database system almost irrelevant from a coding perspective, which is as it should be. Application developers have much more important things to worry about than the syntax that is needed to port their program from one database to another when business needs suddenly change.

Through the ODBC Administrator in Control Panel, you can specify the particular database that is associated with a data source that an ODBC application program is written to use. Think of an ODBC data source as a door with a name on it. Each door will lead you to a particular database. For example, the data source named Sales Figures might be a SQL Server database, whereas the Accounts Payable data source could refer to an Access database. The physical database referred to by a data source can reside anywhere on the LAN.

The ODBC system files are not installed on your system by Windows 95. Rather, they are installed when you setup a separate database application, such as SQL Server Client or Visual Basic 4.0. When the ODBC icon is installed in Control Panel, it uses a file called ODBCINST.DLL. It is also possible to administer your ODBC data sources through a stand-alone program called ODBCADM.EXE. There is a 16-bit and a 32-bit version of this program, and each maintains a separate list of ODBC data sources.

From a programming perspective, the beauty of ODBC is that the application can be written to use the same set of function calls to interface with any data source, regardless of the database vendor. The source code of the application doesn't change whether it talks to Oracle or SQL Server. We only mention these two as an example. There are ODBC drivers available for several dozen popular database systems. Even Excel spreadsheets and plain text files can be turned into data sources. The operating system uses the Registry information written by ODBC Administrator to determine which low-level ODBC drivers are needed to talk to the data source (such as the interface to Oracle or SQL Server). The loading of the ODBC drivers is transparent to the ODBC application program. In a client/server environment, the ODBC API even handles many of the network issues for the application programmer.

The advantages of this scheme are so numerous that you are probably thinking there must be some catch. The only disadvantage of ODBC is that it isn't as efficient as talking directly to the native database interface. ODBC has had many detractors make the charge that it is too slow. Microsoft has always claimed that the critical factor in performance is the quality of the driver software that is used. In our humble opinion, this is true. The availability of good ODBC drivers has improved a great deal recently. And anyway, the criticism about performance is somewhat analogous to those who said that compilers would never match the speed of pure assembly language. Maybe not, but the compiler (or ODBC) gives you the opportunity to write cleaner programs, which means you finish sooner. Meanwhile, computers get faster every year.

CGI Databases with Cold Fusion

To write a CGI application that would interact with an off-the-shelf database would take quite a bit of programming expertise. Why take the time to write such an application or pay someone else to do it when perfectly good ones already exist? We have included a demo version of a product called Cold Fusion, which is a CGI application that will allow full Web integration with any 32-bit ODBC database application. The demo is a completely operational version; it's only limitation is that it will stop working 30 days after installation.

Cold Fusion can be used to create a wide range of World Wide Web applications including customer feedback, online order-entry, event registration, searching of catalogs, directories and calendars, bulletin-board style conferencing, online technical support, and interactive training.

You create Cold Fusion applications by combining standard HTML files with high-level database commands and a powerful CGI program that is precompiled. This method of developing Web applications is an order of magnitude faster, more robust, and more flexible than first generation, code-intensive techniques.

Cold Fusion applications can be developed very rapidly because no code (beyond simple markup tags) is required. The applications are also robust because all database interactions are encapsulated in a single industrial-strength CGI script.

Cold Fusion applications are also very flexible because all formatting and presentation is done using standard HTML files that can be modified and revised at any time (as opposed to having to edit and recompile source code).

Installing Cold Fusion

Installation of the Cold Fusion demo is a snap. Just copy the file cfuseval.exe from the \apps directory on the CD to a temporary directory on your hard disk. Then execute the self-extracting install program in your temporary directory. The Cold Fusion installation process will handle the rest.

The Cold Fusion demo application includes a complete online tutorial in HTML format that will guide you through developing several examples. You may want to spend some time with the Cold Fusion online tutorial and examples.

If the Cold Fusion installation of ODBC components does not complete successfully, it is probably because an ODBC library was in use during setup. In this case, restart Windows and double-click the ODBC Setup icon in the Cold Fusion Program Group to complete the installation of ODBC. To avoid this problem, it is often a good idea to exit all running application programs before you install new software.

CGI Database Access with No Programming

Let's say that you have a company mailing list and it's in the Microsoft Access *.MDB file format. You want people browsing your Web site to be able to add their names to the mailing list. With Cold Fusion you can create a form that will enable people to fill out a Web form and have the data automatically entered into your Access MDB file. We have included all the files necessary to accomplish this on the CD in the \supp directory. Here is a description of the files:

Here are the steps to add record insert functionality to the database at your sample site:

  1. Create a directory named mlist under D:\http.

  2. Copy the files mlist.htm and mlist.mdb to the mlist directory.

  3. Copy default3.htm to the D:\http directory and rename it default.htm. It's okay if it overwrites the file by the same name from your sample site in Chapter 13, unless you have customized the latter. If so, please be sure to save it.

  4. Run the Cold Fusion Administrator (fusionad.exe) and create one or more ODBC data sources for MS Access. See Figure 19.1. For more information on what ODBC is and how it works, see the Cold Fusion User's Guide.

    Figure 19.1. The Cold Fusion Administrator.

  5. Choose the Manage button to bring up the Data Sources dialog shown in Figure 19.2.

    Figure 19.2. The Cold Fusion Data Sources dialog.

  6. Choose the Add button to bring up the Add Data Source dialog, as shown in Figure 19.3.

    Figure 19.3. Adding a data source.

  7. Select the driver for the Access database and then choose OK. In the dialog box that appears, enter MS Access for the Data Source Name. See Figure 19.4.

    Figure 19.4. Naming a data source.

  8. Choose the Select button to enter the path to the mlist.mdb file from step 2. See Figure 19.5. Choose OK to close the Select Database dialog. Choose OK again to close the ODBC Microsoft Access Setup dialog. Choose Close to close the Data Sources dialog.


Figure 19.5. Selecting the path to the database file.


That's all there is to it. Your sample site now has a link to a page that will automatically add people to your mlist.mdb file containing your mailing list. Now is a good time to break out your Web browser and add a few names to the list. Of course, you will need to use Microsoft Access, or a similar Access database program, to view, modify, or print out any information from the .mdb file. The next step is to edit and search the database from the Web.

Inserting and Updating Data with Cold Fusion

Cold Fusion's most basic functionality (inserting form data into database tables) is implemented by the addition of hidden configuration fields to HTML forms. If you are already familiar with HTML forms, this technique is extremely easy to learn. If you need a refresher course on HTML forms, please refer to Chapter 9.

To insert or update data, you create an HTML form containing the fields in the database table that you want to insert or update. You then add three hidden fields to the form which indicate what you want Cold Fusion to do with the data entered by the user. These hidden fields are as follows: 

Field Name


Purpose

DataSource

Name of the ODBC data source containing your table

TableName

Name of the table you want the form fields written to

NextPage

A URL indicating which page the user should be sent to if the submission is successful

Additional hidden fields can be specified to validate the user's entries (for example, required, numeric, date, and range-checked).

When this form is submitted to the Cold Fusion CGI program (DBML.EXE), the data entered by the user is added to the specified table and the user is routed to the specified next page.

Queries and Dynamic Pages with Cold Fusion

Cold Fusion enables you to dynamically generate HTML pages based on user queries. These queries are submitted to the Cold Fusion CGI program (DBML.EXE), which then (based on a template file specified in the query) generates the output to be sent back to the user.

The key to dynamic page generation is a small (but powerful) set of database-oriented markup tags. These tags are collectively referred to as DBML (Database Markup Language). DBML tags are very similar to HTML tags except they are database-oriented. Learning to use the DBML tags is extremely simple. Almost all of the core functionality of Cold Fusion is encapsulated in these four tags: 

Tag


Purpose

DBQUERY

Submits an SQL query to the database

DBOUTPUT

Displays the result of a query, freely intermixing result set fields and HTML tags

DBTABLE & DBCOL

Displays a preformatted table containing the result set of a query

Dynamic pages are created using template files, which are composed of a mix of HTML and DBML tags that define how the user's request should be processed and what type of output should be returned.

The DBML tags are used to specify how you want Cold Fusion to interact with the database, as well as where you want to display the results of your queries. For example, you might specify that you want the SQL query SELECT * FROM Customers sent to the database and the results returned as a preformatted table.

For more information about the fundamentals of Structured Query Language (SQL), see the section titled Quick Guide to SQL in Chapter 23, "CGI with Visual Basic and SQL."

The HTML tags are used both for implementing the non-database driven parts of your output (for example, page header and footer) as well as for specifying how you want the results of your queries formatted. For example, you might specify that you want a field bolded or a horizontal rule drawn between each record displayed.

Expanding the Database Interface with Cold Fusion

To add more functionality to the mail list entry, you can search and update the mailing list from a Web browser too. To accomplish this, you need three more files. Here are the files you can copy from the \supp directory of the CD:

Here are the steps to add database search and update functionality:

  1. Copy the file srch.htm to the mlist directory you created.

  2. Copy default4.htm to the D:\http directory and rename it default.htm. Again, unless you have customized the previous version, it should be okay to overwrite it.

  3. In the directory in which you installed Cold Fusion, create a directory named mlist under the Cold Fusion template directory. The path should be something like c:\cfusion\template\mlist.

  4. Copy the file mlist.dbm to the directory you just created.

Fire up your Web browser and go to your sample site home page to try out the new link. See if you can search and update the mailing list database.

Depending on the nature of your data, you might immediately realize that you would not want a link like this available to the general public. If this is the case, you'll want to place password protection on your HTML pages. Many Web servers, including Purveyor and FolkWeb, enable you to password-protect your pages.

Other Search Engines

In addition to Cold Fusion, several other ODBC and Web integration tools are now available. Here are a few links that you may want to check out.

What's Next?

Before we get back to the topic of CGI and advanced Web site programming in Part V, we still have to cover a few important topics which come under the heading of Web site maintenance. In particular, the next chapter will show you what to do when your site is queried for a file called robots.txt and provide you with lots of tips about server security.

Previous Page TOC Next Page Home