DBMS, September 1996
A few months back I reported that Borland International was working on utilities that would enable developers to use their existing Borland development tools to implement World Wide Web-oriented applications. In late May, the company shipped WebTools for dBASE, an add-on product that facilitates the creation of relatively sophisticated Web-based database applications. (Borland's Intranet Tools for Visual dBASE Professional includes both DeltaPoint WebTools and the Visual dBASE Web Experts. You can download the combined package from Borland's Web site at http://www.borland.com.)
WebTools for dBASE effectively leverages some of the most powerful capabilities of dBASE for Windows, including its object-oriented language and its fairly transparent support for client/server operations through the Borland Database Engine. Read on for an in-depth introduction to WebTools. Along the way, we'll take a little side trip to discover how to use the object-oriented programming techniques underlying WebTools to create a reusable code library for retrieving data from client/server systems as well as dBASE native tables.
At the core of WebTools is a class library that provides the functionality necessary to output tables or queries to static Web pages. It also lets you build search pages that permit entry of search criteria to enable database publishing based on the surfer's requirements. You can build on search programs built with the WebTools class libraries to create programs that a Web server calls through its Windows Common Gateway Interface (WinCGI) API to update data, retrieve data from multiple data sources, and perform up-to-the-minute calculations and return the results. In addition to the class library, the WebTools package has several other components. The Wtserver executable keeps the dBASE runtime engine loaded into the server's memory so that dBASE executables called through WinCGI don't have to load it. The publish and search experts make the creation of basic Web database applications a matter of responding to a series of dialog boxes. The substantial amount of documentation does a good job of describing both the concept and the mechanics of developing Web database applications with WebTools. Finally, I was able to use the copious library of examples as the basis for developing very practical custom applications.
I should note that all of the components of WebTools were developed by Bill French, who has been developing very useful utilities for years (including the Evolve macro library for the MultiEdit programmer's editor). A couple of years ago, Bill recognized that the Web might offer a great opportunity, and he developed a Web site management tool called QuickSite, which DeltaPoint published earlier this year to a good deal of acclaim. QuickSite is worth checking out: It maintains entire Web sites in database tables and generates the required HTML code on demand. (For a limited time, Borland is bundling QuickSite with Visual dBASE Professional and other Borland development tools.) Bill is an excellent technical writer and lecturer in his own right; his presentation on object-oriented programming at a developer's conference a few years back turned on light bulbs in the heads of many people in the room, including yours truly.
Creating an interactive query form that lets users enter search criteria and returns results of the search to their Web browser is conceptually a little more complicated, although it's still simple to implement. Naturally enough, HTML refers to the area of a Web page that includes controls that gather user input as a "form," and it provides the "FORM element type declaration" as a means to define forms. Within areas defined as forms, you can present text input boxes, text editing regions, check boxes, radio buttons (option groups, in HTML nomenclature), list boxes, and command buttons.
A <FORM> tag indicates the beginning of a form region, and the tag indicates the end. (See Listing 1.) Almost all of the time, you would dispatch a form by providing a "submit" button for the user to click on. When a user clicks the submit button, it calls the program specified by the form action parameter through the Web server's API, commonly known as CGI. The WinCGI is a Windows-specific CGI supported by several of the currently available Web servers. WinCGI programs extend Web server capabilities by allowing the Web server to call other services as needed and have those services format their results as an HTML page.
When it calls the action program, the WinCGI builds a parameter file that looks very much like a Windows .ini file and contains the contents of the input regions defined within the form as well as information about the Web server environment. It passes the name of the .ini file to the action program and then waits for the action program to create a response file, which almost always comes back in the form of an HTML file. When it sees the response file, WinCGI returns it to the Web surfer who issued the request. By the way, most Web server software lets the site administrator adjust the period at which the CGI will time out waiting for a response from the action program.
With dBASE WebTools, you supply a standard dBASE executable that you build using the WebTools class libraries as your action program. When a user enters a query and clicks on the submit button, the CGI calls the dBASE program to perform the query task that you defined in your dBASE program. Your dBASE program formats the query result as an HTML page, and the CGI then picks it up and returns it to the Web server proper, which in turn sends it off to the surfer who initiated the query. Of course, you can run through the entire code, debug, compile, and build process in dBASE to create the executable that will be called by the CGI, but you don't have to. You can simply use the search expert, which - like the publish expert -steps you through a series of dialogs that gather the information that the expert needs. The expert uses your responses to write the HTML code for the query page and the dBASE code to perform the search. The expert also compiles and links the dBASE code and the WebTools class library into an executable. It's left to you to figure out where to put it. In fact, this is the trickiest part of the process because it requires some understanding of how Web servers search for files. Suffice it to say that you want to put the HTML query form in the server's documents directory and the executable in the server's executables directory, or else specify them explicitly on the pages that call them.
The WebTools documentation recommends using O'Reilly's WebSite 1.1 Web server. I downloaded an evaluation copy and it chugged away very unobtrusively in the background, which I think is the best thing you can say for Web server software. I also tried WebTools with the Microsoft Internet Information Server (IIS), which turned out to be a nonstarter because Microsoft's IIS only supports 32-bit WinCGI programs, and dBASE outputs only 16-bit executables. We've historically had problems with Microsoft's IIS and CGI programs ever since they appeared because they prefer to use their proprietary API. I did not try a Netscape server (I have one available to me but have not yet gotten around to it); I'm fairly confident that it would have worked all right.
The WebTools class library is provided in the dBASE program WebTools.prg, which contains four classes - CGIForm, CGISession, HTMLPage, and HTMLEngine - as well as a library of supporting functions. The HTMLEngine class consists of a series of methods that write HTML code to an output file. You can use it as the basis for automating any HTML writing task. WebTools never instantiates HTMLEngine directly; rather, it bases the CGIForm and HTMLPage classes on it.
Programs that publish data from tables or queries use the HTMLPage class. When you create an object from this class, the object creates an HTML file with the name that you pass as a parameter, and it prepares that file to accept output from the HTML methods inherited from the HTMLEngine class. Listing 2 is an example of a program built with the HTMLPage class. Listing 2 illustrates the steps involved in writing an HTML document, whether you do it at the keyboard or automate it. As you can see, automating it by invoking methods of an HTML generation class lets you manage the page at a much higher level than is possible when you write HTML code directly. It also insulates you from having to remember the details of HTML tag syntax.
This sample also shows how the HTML method calls integrate with Xbase Data Manipulation Language (DML) to make a very natural database publishing program. Now that dBASE for Windows-local SQL can execute against a database server or against local dBASE .dbfs, you can use the same code regardless of database back end. Interestingly, you can mix local in-line SQL with traditional Xbase DML, using SQL for set creation and processing and Xbase DML for displaying and updating the contents of the fields in individual rows. When connected to a SQL server, the server responds to both types of DML. Borland's Database Engine is very effective at mapping record-at-a-time commands into your server's set-based world.
The CGIForm class is the workhorse of WinCGI processing: It does all the work of creating the CGI response file. When a program creates an instance of this class, it parses the .ini file passed by WinCGI and assigns the values it contains to member variables of the object instantiated from the CGIForm class. It then creates the output file with the name specified in the input file and prepares it to receive output. The program can then populate the output form by calling the HTML output methods inherited from the HTMLEngine class. The CGISession class provides the environment in which the object created from the CGIForm class operates. In fact, that object is created as an object type member of the CGISession class. CGISession is actually derived from dBASE's built-in form class, and it provides the capability to open a window to display the results of the CGI process for debugging purposes. The assignment of the CGIForm object to the CGIForm member object of the CGISession demonstrates an interesting and effective object-oriented programming technique. It results in a class that derives its behavior by inheriting from two parent classes, thereby simulating multiple inheritance without encountering any of the conflict resolution difficulties that true multiple inheritance can present.
For my final trick, I "enhanced" one of the examples that was supplied with WebTools. In this example, the first HTML form lets a user select a record from a table; that record is then presented in an editable form and the user is permitted to make changes. The only substantive change I made was to point the example at the authors table that comes in SQL Server's example PUBS database. Once you've set up an alias for a database in the Borland Database Engine configuration screen, a program can interact with the tables in that database as if they were dBASE tables in a directory. First, use set database to
SELECT customer, ptdsls, salesman, commission
Two other key elements of dBASE support for client/server development are its robust implementation of transaction processing commands and the mechanism for calling stored procedures. When initiating a transaction with the begintrans( ) function, the first parameter indicates the BDE alias of the database in which the transaction is to be performed, and the second indicates the transaction isolation level. You can program transactions for local tables by specifying an alias that points to a directory in which dBASE or Paradox tables are stored. The transaction isolation level parameter indicates whether the transaction should be permitted to read the results of other transactions executing simultaneously. dBASE uses the same mechanism to register and call stored procedures as it does to register and call API functions located in DLLs. You register the function with the command extern sql function(parametertypelist)
To leverage the most powerful capabilities of the dBASE language, I wrote the beginning of a class library to support database access. Embedding SQL syntax in a class library lets me write code that is very consistent, avoiding the inconsistent style that you see in Listings 3 and 4. Listing 5 shows how I would rewrite the first two queries from Listing 3 with methods from my class library. Doing so gives me the flexibility, for example, to create an HTML output object as a member of my SQL class and thereby completely integrate SQL with the HTML output approach described above. Using a class-based scheme for executing SQL also lets me create a system in which I subclass dialect-specific implementations that I can call with common code from any application. All in all, I am surprised by how easy it is to become familiar with Visual dBASE. Now if Borland would only get Visual dBASE to support OCX controls, we'd really be able to develop completely Internet-enabled applications.
Borland International Inc., 100 Borland Way, Scotts Valley, CA 95066; 800-245-7367, 408-431-1000, or fax 408-431-4122; http://www.borland.com.
Company: <INPUT Type ="text" NAME="FirstFld" size=35>
<INPUT TYPE="submit" VALUE=" Submit">
Table of Contents - September 1996 | Home Page
and relies on BDE or dBASE developers to perform navigation. To begin with, as you have seen in my CGI example, dBASE permits using either SQL or dBASE command syntax against either a local database or a SQL server. This capability applies to both data definition and data-processing operations. I did discover a trick that made working against either type of table transparent: Create a BDE alias for local tables just as you must do for server tables. Once you have done this, you can issue the set database to
from :SQLServer:Customer a, :dBASEtables:slspeople b
where a.slsprsn = b.slsprsn.
Tom Spitzer is managing consultant for application solutions in the San Francisco office of AmeriData Consulting. You can email Tom at tspitzer@ameridata.com.
FIGURE 1
--This web page presents an up-to-the-minute summary of sales order activity. When I request this page, a series of database queries generates the required statistics, and the WebTools CGI class then writes them out to a response file. When it sees the response file, the Web server's CGI interface displays it as the current form.
LISTING 1
<FORM Method="POST" Action="/CGI-WIN/SRCHCUST.EXE">
<PRE>
Enter Search criteria for
Contact: <INPUT Type ="text" NAME="SecondFld" size=20>
</PRE>
(<i>Start search</i>)
<INPUT TYPE="reset" VALUE=" Reset">
(<i>Clear form</i>)
</FORM>
LISTING 2
SELECT name, street, city, state, zip ; && Issue query that yields
FROM customer WHERE state = "CA" && Result we want to publish
oPage = new htmlPage("CACust.htm") && create an HTML page object
*****Create the HTML*****
oPage.putTitle("California Customer List") && insert a title
oPage.putHeader("California Customer List", 1) && insert the header
oPage.putLine() && insert a horizontal line
oPage.putLineBreak() && insert another line break
&& for readability
oPage.putBeginBody() && begin the body...
SCAN && look at every record
oPage.putLine() && insert a horizontal line
oPage.putHeader(trim(Customer->name), 2) && insert the name in large
&& letters (heading style 2)
oPage.putBeginUList() && begin unordered list
oPage.putListItem("Address: " + trim(Customer->street))
oPage.putListItem("City/State/Zip: " + trim(Customer->city) + ", "
+ trim(Customer->State) + " " + trim(Customer->Zip))
oPage.putEndUList() && end unordered list
oPage.putLineBreak() && insert another line break
&& for readability
ENDSCAN
oPage.putLine() && insert a horizontal line
oPage.putAddressLink(wtEMailName, wtEMailAddress)
oPage.putLastUpdate() && add an updated last line
oPage.putEndBody() && close the body block
oPage.putEndHTML() && close the html block
oPage.saveHTML() && close/save html output file
LISTING 3
LISTING 4
LISTING 5
Copyright © 1996 Miller Freeman, Inc. ALL RIGHTS RESERVED
Redistribution without permission is prohibited.
Please send questions or comments to mfrank@mfi.com
Updated Friday, September 20, 1996