As surely as the Intranet is changing the face of client/server application development, you can bet that organizations all over the world are striving to build Web databases. Nearly every client/server application you can imagine is based on a back-end database located on a server. And nearly everywhere you look, there is a backlog of requests for application databases that need to be built and put into service to help automate office processes.
One reason that Web technology is getting so much attention on the Intranet is that it has the potential to help alleviate the application development backlog by standardizing and simplifying the database front end. (I'm referring to our hero, the Web browser, of course. ) Conveniently, the back end is simplified too. Using ODBC (Open Database Connectivity), IIS, and some simple HTML scripts, you can provide access to any legacy database system. When compared with the traditional approach of custom programming in third-generation languages, Web databases may substantially reduce implementation and maintenance costs.
As with so many other computer applications' vendors, commercial database vendors are racing to provide Web-accessible front ends to their packages. If you've struggled to build useful, user-friendly database applications using the tools your database vendor has provided or using custom programming, the idea of using a Web browser and fill-in forms as an alternative to building user interfaces from scratch is an attractive option.
In this chapter, you'll learn about a few commercial database vendors' Web products. I'll explain ODBC and the raw fundamentals of SQL programming. I'll also show you how to create user-friendly custom database applications using only IIS and HTML with no additional programming.
This chapter does not substantially cover designing and developing relational database applications. Consult your database package's documentation for that information. This chapter assumes that you are thinking of a database application you would like to access using your Web browser. Of course, you may find once you start accessing your database with your Web browser, you'll want to change the database's design. This is no different from the traditional iterative database application development process, in which you and the users try the application for awhile to see how you want to change it.
This chapter is mostly about the subject of creating full-blown client/server database applications on the Web. But before diving too deeply into that subject, this section reviews the ideas presented in Part III, "Setting Up Office Applications." Chapters 12 through 15 showed how easy it is to set up any type of document, including a database, for Web browser access using MIME. In some cases, this simple concept may be all that you need to accomplish the task at hand.
In Chapter 15,"Other Client Applications on the Intranet," you learned how to access Microsoft Access database applications through the Web helper application mechanism. You can configure your Web server to serve complete Access databases, just as it serves any other file on the server. Your customers' Web browsers can then use their own copies of Access as a Web browser helper application to load the databases for data search and retrieval and/or export to other applications.
Note that such helper application access to a database application should be considered read-only because the Web browser downloads a temporary copy of the database to the local system. All queries made by your customer are based on the temporary copy, and any changes that he might attempt to make will not be reflected in the master copy on your Web server. Similarly, updates made to the master server won't be propagated to any client unless the client reloads the database from the Web server. Thus, you'll want to limit the capabilities of such an arrangement to running queries, generating reports, and exporting data from the application.
This limitation is more than offset by other capabilities, however. For example, if your company uses other programs, such as Microsoft Office or its individual components, you'll be able to use their capabilities to move data from one application to another. You can import information in Access databases, for example, into Word or Excel (and vice versa).
Whatever database package you use on your Intranet-and whatever bells and whistles it provides for developing database applications, entering queries, and generating reports-database access boils down to two broad processes:
Whether the user hand-edits SQL queries or fills in an on-screen query or data-entry form, the objective is the same: to pass the query or new data to the database back end. Similarly, when the database spits out the results of a query or data entry, an application has to receive it and generate human-readable output (on-screen or on paper) or machine-readable output in some specific format.
Web access to these databases involves the same processes, with important differences:
This section briefly lists a few of the commercial Web database products available for Windows NT. If you are faced with a new client/server database project, and you don't want to engineer it yourself, any of these Web tools will help. You will still have to determine the database model and the user interface (using HTML). You can find out more about these Web database products, and in some cases even download a trial version, by visiting these URLs:
FoxPro users should look at a new product from the Aegis Group called FoxWeb. This software tool interfaces Windows Web servers with FoxPro data and programs. FoxWeb overcomes the limitations of other Windows CGI approaches, which read and write temporary files to pass environment variables between processes. It works by running multiple, background Visual FoxPro instances simultaneously, each one of which can handle CGI interactions. CGI environment variables are placed into FoxPro arrays and objects for manipulation. All programming is done in FoxPro rather than in an external scripting language like Perl, so your investment in FoxPro programming can be both preserved and leveraged. You can even store reusable HTML code directly in FoxPro databases for easy retrieval, with intelligent branching capabilities.
Aegis claims substantially faster database access compared to ODBC database transactions, although ODBC database applications can also access FoxPro databases, as noted later in the section on ODBC. FoxWeb requires version 3.0 of Visual FoxPro. FoxWeb includes login/password security features.
ExperTelligence, Inc. offers WebBase for all Microsoft Windows platforms. This package is a 32-bit HTTP server with built-in hooks for accessing databases without the use of CGI scripting.
As a Web server, WebBase can serve conventional HTML documents in response to Web browser requests. Besides this function, however, the package supports embedded SQL code in special HTML documents, which, when accessed, can contact database applications directly to run queries or data-entry commands. WebBase HTML extensions also include a macro language featuring intelligent decision-making constructs like if-then and case branching, as well as forRow and forIndex looping. A number of other useful functions are also provided, such as string-comparison/matching, math, date handling, and other logic. These features enable customized responses to Web browser requests based on user name, IP address, browser type, and the like.
WebBase enables the session state to be maintained throughout a user's session and has login/password security built in. Any ODBC database (see the next section for a discussion of ODBC) is supported (for example, Microsoft Access, Excel, SQL Server, FoxPro, dBASE III and IV, Paradox, Btrieve, as well as UNIX database servers running Sybase and Oracle).
The package can also search fielded text files as a database. Although WebBase can function as a Web server, you can also run a traditional Web server for better HTTP performance, either on the same computer or a different one, because WebBase doesn't provide all the functions of full-featured Web servers. WebBase runs on all Intel Windows platforms.
All the previously mentioned database products support 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.
| Note |
The ODBC system files are not installed on your system by Windows NT. Rather they are installed when you set up a separate database application, such as SQL Server Client or Visual Basic. When the ODBC icon is installed in Control Panel, the icon uses a file called ODBccP32.CPL. You also can administer your ODBC data sources through a stand-alone program called ODBCADM.EXE (16-bit) or ODBCAD32.EXE (32-bit). The 16-bit and 32-bit versions maintain separate lists of ODBC data sources. The 16-bit data sources can be used only by 16-bit programs. The same goes for 32-bit data sources and 32-bit programs (unless the programmer uses an advanced technique known as thunking). On Windows NT, there is a further classification of data sources called system data sources. These sources can be used only by 32-bit NT services, such as IIS. |
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, regardless of whether it talks to Oracle or SQL Server. (I only mention these two as an example. ODBC drivers are 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 kind of a catch. The only disadvantage of ODBC is that it isn't as efficient as talking directly to the native database interface (although Microsoft is planning to make ODBC the native interface of SQL Server). ODBC has had many detractors make the charge that it is too slow. Microsoft has always claimed that the critical factor in ODBC performance is the quality of the driver software that is used. In my humble opinion, this claim is valid. The availability of good ODBC drivers has improved a great deal recently.
The criticism about ODBC performance is somewhat analogous to those who said that compilers would never match the speed of pure assembly language. Of course not! But the compiler (or ODBC) gives you the opportunity to write cleaner programs, which means you finish sooner and are ready to enhance your program or optimize the section of code where it truly spends most of its execution time. Meanwhile, computer hardware gets faster every year. (As a parody of the personal career tragedy that can occur due to the ever-declining percentage of application programs being developed without any direct use of assembly language, the Microsoft Day at the Movies in April 1996 included a humorous skit showing two jobless, die-hard programmers who wanted to reinvent the Win32 common dialogs in assembly code. As I recall, they couldn't see why anyone would prefer to write one line of "slow" compiled code when pages and pages of ultra-fast assembly code could be written instead.)
The basics of CGI in this section are relevant to Web database application interfaces, and you'll want to bear them in mind as you work your way through the rest of this chapter:
Writing 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? I have included on the CD-ROM a trial version of a product called Cold Fusion, which is a CGI application that allows full Web integration with any 32-bit ODBC database application. The trial version is a completely operational version; it's only limitation is that it will stop working 30 days after installation. At which time you are encouraged to buy the product if you find it useful.
You can use Cold Fusion to create a wide range of Intranet applications including company schedules, customer feedback, online order entry, event registration, searching of catalogs, directories and calendars, bulletin-board style conferencing, online technical support, and interactive training. If you have browsed the World Wide Web and come across any Windows NT servers, you probably have seen the logo saying, "Powered by Cold Fusion"; it is a popular product. Cold Fusion is sold by Allaire at http://www.allaire.com/.
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 HTML) 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).
The installation of the Cold Fusion demo is a snap. Just copy
the file cfafeval.exe from
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.
| Note |
The file cfafeval.exe on the CD-ROM actually includes two products: Cold Fusion 1.5 and Allaire Forums. Allaire Forums is a Web collaboration product mentioned in Chapter 27, "Collaboration on Your Intranet." These evaluation products are provided through the courtesy of J.J. Allaire. |
The Cold Fusion demo application includes a complete online tutorial
in HTML format that guides you through the development of several
examples. You may want to spend some time with the Cold Fusion
online tutorial and examples.
| Note |
If the Cold Fusion installation of ODBC components does not complete successfully, an ODBC library was probably in use by Windows or another application 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, you should exit all running application programs before you install new software. |
Suppose a fictitious ABC Corporation has a company mailing list and it's in the Microsoft Access *.mdb file format. Let's say that the new V.P. has decided that employees should be able to add their names to the mailing list to volunteer their free time to promote company products. With this stalwart example in mind, you will no doubt be able to think of ways you can adapt this application for your own purposes. Using Cold Fusion, you can create a form that people can fill out and have the data automatically entered into your Access MDB file. A description of the files for this project is as follows:
Figure 16.1: The ABC Intranet Mailing List page for propaganda distribution.
Listing 16.1. The mlist.htm
file uses Cold Fusion as a CGI database application.
<HTML>
<HEAD>
<TITLE>Intranet Mailing List</TITLE>
</HEAD>
<BODY>
<H1>The ABC Corporation Intranet Mailing List</H1>
<HR>
<P>
Occasionally, ABC will send out snail mail containing brochures of
our new products. If you would like to get on the list and volunteer
to distribute these materials to your neighbors, just fill out the
form below.
<HR>
<FORM ACTION="/scripts/dbml.exe?Action=insert" METHOD="POST">
<!-- Cold Fusion configuration fields -->
<INPUT TYPE="hidden" NAME="DataSource" VALUE="Mailing List">
<INPUT TYPE="hidden" NAME="TableName" VALUE="MLIST">
<INPUT TYPE="hidden" NAME="NextPage" VALUE="/mlist/thanks.htm">
<!-- Data entry fields -->
<PRE>
First Name:<INPUT NAME="FirstName" size=28>
Last Name:<INPUT NAME="LastName" size=28>
Address:<INPUT NAME="Address" size=45>
City:<INPUT NAME="City" size=15>
State:<INPUT NAME="State" size=5>
Zip:<input NAME="PostalCode" size=10>
E-mail:<INPUT NAME="EmailAddress" size=35>
Phone:<INPUT NAME="Phone" size=35>
Extension:<INPUT NAME="Extension" size=7>
<INPUT Type="submit" Value=" When Done, Click Here! ">
<INPUT Type="reset" Value="Clear Entries">
</PRE>
</BODY>
</HTML>
To add record insert functionality to the database at your sample site, follow these steps:
Your 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.
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 5, "What You Need to Know About HTML."
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 that 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.
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 and 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.
| Tip |
For more information about the fundamentals of Structured Query Language (SQL), see the following section titled "Overview of Structured Query Language." |
The HTML tags are used both for implementing the nondatabase-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 displayed record.
Depending on the nature of your data, you might decide 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. Most Web servers, including IIS, enable you to password-protect your pages. (See Chapter 10, "Intranet Security in Windows NT," for more information about Intranet security.)
Most database tasks can be accomplished with just four simple SQL statements:
What follows are several short and sweet examples of each type of statement. In each case, it is not important to type in uppercase, but I have marked the SQL keywords in uppercase for readability. I use the terms table1, field1, and field2 to represent placeholders for the object names that you would use in your database.
The following are some examples of the SELECT statement:
| Note |
The asterisk wildcard character is specific to the Access dialect of SQL. Standard SQL uses the percent (%) character instead. Another special character that differs among SQL dialects is the date delimiter. Access SQL surrounds dates with pound signs (#), while ODBC merely requires the date to be in one of several formats (with no special delimiters). |
Now that you know several varieties of SELECT, the DELETE statement is easy. The following is an example:
DELETE FROM table1 WHERE field1 = 24
This statement will delete all rows from the selected table that match the optional WHERE clause.
The UPDATE statement is a little trickier. It can be used to modify one or more rows of a selected table:
UPDATE table1 SET field1 = 99 WHERE field1 = 44
This statement will change the data in the field1 column for all rows that currently have a field1 value of 44. Other field=value pairs may be included as long as they are separated by commas.
The INSERT statement is also a little different from the others. Whereas the other statements operate on existing data, INSERT is used to add a new record:
INSERT INTO table1 (field1, field2) VALUES (99, 'abc')
Assuming that field1 and field2 are the only required fields, this statement creates a new row in the selected table. The order of the values must match the order of the selected column names. For example, 99 will be placed in field1, and 'abc' will be placed in field2.
This chapter focused on Web interfaces to relational database packages. In addition, SQL and ODBC were discussed, because they form the pillars of most database projects. I talked about several vendors (but it was by no means an exhaustive list) of commercial database products for Windows NT and the Web. The chapter surveyed a fairly representative sample, some of them in detail, to at least give you a firm idea of what's possible in this rapidly growing field.
Chapter 17, "Understanding ActiveX Technologies," continues to explore the advanced Internet technologies that Microsoft is building on top of IIS. Although this chapter dealt with the creation of Web-accessible, general-purpose databases, you may find Chapter 21, "Indexing Your Intranet with WAIS," to be of similar interest as it looks at the subject of searching your Web.