The Visual FoxPro Internet Search Wizard


The Visual FoxPro 5.0 Internet Search Wizard

M. W. Dunn, Microsoft Corporation

The WWW Search Page Wizard is a Microsoft® Visual FoxPro wizard that constructs an HTML document (.HTM file) as its output, giving you the ability to query Visual FoxPro data with a Web browser.

Table of Contents
The Wizard Components
How the Components Work Together
Common Scenarios
Wizard Requirements
Wizard Steps
Wizard Output
The Visual FoxPro Information Server

The Wizard Components

The WWW Search Page Wizard has three components:

The Visual FoxPro WWW Search Page Wizard
A Common Gateway Interface (CGI) Script
The Visual FoxPro Information Server

The WWW Search Page Wizard
The WWW Search Page Wizard behaves like any other wizard that ships with Visual FoxPro, but it constructs three ASCII text files instead of a Visual FoxPro object:

An HTML page with a form input area and a submit button
An Internet Database Connector file (.IDC)
An HTML extension file (.HTX)

The Common Gateway Interface (CGI) Script
The CGI script is an executable windows application written in the Visual C++® development system that is a two-way translator between an HTTP service and the Visual FoxPro Information Server.

Diagram

The Web page transmits data to the HTTP service through a Web browser, which passes the data to a new instance of the CGI script. The CGI script writes the data to a file and signals the Visual FoxPro server to open and read the file. The CGI script then waits in a timed-out do while loop, waiting for a signal file to be created by the Visual FoxPro server.

// Wait for Server to ACK
time(&begintout);

do {
tempFile = fopen(ackFileName,"r");
time(&chktout);
Sleep(250);
} while ((tempFile == NULL) && (difftime(chktout,begintout) < 60));

The Visual FoxPro Information Server

The Visual FoxPro Information Server, the backbone of these components, is a Visual FoxPro executable application that runs in the background. When it receives the notification that a new query has been sent, it opens the data file. Based on the information in the file, it runs the query and creates an HTML return page. Then it creates an acknowledgment file to signal the CGI script to read the new file contents and stream it back to the Web server and the client's Web browser.

How the Components Work Together

The search page, and the .HTX and .IDC files generated by the wizard, work with the Visual FoxPro Information Server when you initiate a search.

First, you request the page you created with the wizard by providing a URL (Uniform Resource Locator) such as HTTP://yourserver/homedirectory/page.htm. The URL allows the Internet to find the appropriate server, which, in this case, is yourserver.

Next, the HTTP request is processed by the Internet Service, such as a Windows NT® HTTP service daemon, and the HTML page is returned to your machine. The Web browser interprets the HTML text stream, which displays the search page you constructed with the WWW Search Page Wizard.

After you fill in the search criteria and choose the Search button on the page, an action request, in this case a call to the CGI script residing on the Internet server, is sent back to the Internet service provider. The server executes the script as requested.

The CGI script creates a data (.DAT) file, containing information on what the Visual FoxPro server is supposed to do, and an associated semaphore (.ATN) file.

The Visual FoxPro server, meanwhile, is polling the file system looking for .ATN files. When one is found, the associated .DAT file is read and the request, currently an SQL Select statement, is processed.

Once the data has been collected, the Visual FoxPro server creates a return HTML document containing the data, as well as the template information provided during the search page creation. After the page has been generated, the Visual FoxPro server creates another .DAT file, containing the HTML stream representing the result page created in the WWW Search Wizard, and a semaphore (.ACK) file, indicating that the work is complete.

The CGI script, which has been polling for the .ACK file, picks up the HTML in the .DAT file and hands it off to the HTTP service. The HTTP service then returns the result page to your machine and you see the results of your search.

Handling Multiple Requests
Each Web page request spawns its own copy of the CGI script. Since the CGI script is usually 4K or less, there is no noticeable penalty for multiple requests. Each CGI script creates a unique file name for the .DAT and .ATN files. The Information Server, in turn, creates a result and .ACK file with the same name. This is how the script knows when its own, and not someone else's, .DAT file is available.

The interoperability between these three components provides the ability to publish and query data on the Internet. The Visual FoxPro WWW Search Wizard allows you to easily create HTML documents based on Visual FoxPro data and provides one of the fastest desktop database engines as the backbone.

Common Scenarios

Imagine the following scenarios:

You have a database containing a "yellow pages" type listing of restaurants in the local area. The restaurateurs pay you a monthly fee to show their names on your Web page. By storing the information in a Visual FoxPro database, you can easily access the appropriate restaurants by location, type of food, or other properties, such as whether they are a member of a chain. However, you don't know anything about creating Web pages and how to get the appropriate information displayed back to the Internet client.

The WWW Search Page Wizard walks you through the production of an HTML page painlessly. Further, the wizard creates the necessary SQL statements for the search without your needing to code. The wizard also creates the files necessary for creating the return page as well.

You have several salespeople, scattered around the world who work from their homes. To access data conveniently, you would like to publish the data on the Internet. Since the directory is secured at the HTTP level, you are fairly confident that with the proper passwords, strangers would not have access. Unfortunately, the salespeople would like to be able to manipulate whatever data is returned. Further, they don't all have Visual FoxPro. In fact, some might want to use Visual Basic or Microsoft Access for data access.

The WWW Search Page Wizard gives you the option of including a data download check box on the initial search page. When the salesperson views the initial search page, he or she can choose to download the data, rather than just viewing it, by selecting the check box.

Of course, the salesperson can always view the results before determining whether or not to download the data. If he or she wants the data locally, the search can be rerun with the download option selected.

Since the data downloaded across the Internet is text, the browser will attempt to display it. The effect on the client side of the download, then, is a splash of tab-delimited data across the screen. When the download is complete, the client can use the browser's Save As option to create a tab delimited text file. This file can then be imported into any product that can read text files. This includes such products as Microsoft Excel, Visual Basic®, Visual FoxPro, Microsoft SQL Server™, Microsoft Access, and so on.

Wizard Requirements

The wizard and the Visual FoxPro Information Server have the following requirements:

An HTTP service, such as the one provided by Windows NT or Internet Information Server.

A licensed copy of Visual FoxPro 3.0b or later residing on the HTTP server.

The HTTP server must contain a designated root directory. A default directory, on which the home page resides, is also recommended.

The search pages, as well as the data sources, need to reside on the server. The following files are needed by the server. Except for FOXTOOLS.FLL, these files are installed in the Visual FoxPro TOOLS\INETWIZ\SERVER directory.

File Name Description

FOXTOOLS.FLL A library that ships with Visual FoxPro and is required by the Information Server application.

INETWIZ.DOC This file.

MYCGI.C Source code for the CGI script.

SERVER.PRG Contains the procedural code used to update the query statistics table, generate the return HTML page and page error handling.

SERVER.SCX
SERVER.SCT The server information form. The code within this form searches for and processes .DAT files created by the CGI file, executes the page generation code, handles initial error conditions, and displays the current server processing information.

SPECROOT.SCX
SPECROOT.SCT Server configuration information form. This information includes the file locations for the HTTP server, the .IDC script files and the data file(s).

SERVER.PJX
SERVER.PJT The project containing all of the components for the Visual FoxPro Information Server.

SERVER.APP The application (.APP) file built using the Visual FoxPro Information Server application.

VFPCGI.EXE The CGI script used to pass information to and from the Visual FoxPro Information Server.

VFPCGI.MAK Makefile for the CGI script.

An additional file, VFPIS.INI, is created the first time you run the Information Server. It stores information about the name and path of the HTTP root and, optionally, the name and path of the script (.HTX and .IDC) files.

The Search Page Wizard requires the following files, installed by default in the Visual FoxPro TOOLS\INETWIZ directory.

File Name Description

TEMPFLDS.DBF
TEMPFLDS.CDX
TEMPFLDS.FPT A table (with associated index and memo files) that is used to determine the location of the result fields within the template.

TEMPSTYL.DBF
TEMPSTYL.CDX
TEMPSTYL.FPT A table (with associated index and memo files) that is used by the WWW Search Page Wizard to create the .HTX template language used for the search result page.

WWWPAGE.APP The file that contains the WWW Search Page Wizard. While this is a standard Visual FoxPro Wizard, it is not currently available through the wizard menu option.

The WWW Search Page Wizard is not directly connected to the wizard menu option. Therefore, you do not need to have the wizard application (WWWPAGE.APP) loaded onto the Visual FoxPro drive. However, you will need a copy of Visual FoxPro 3.0b or later in order for the wizard to execute properly.

Although the wizard source code is not available, the Visual FoxPro team will, given enough interest, provide details on how to convert the input information into HTML format.

Wizard Steps

Wizards are meant to make complex objects simple to create. However, there are times when you might wish you had a better understanding of what was going on underneath. If you are a typical developer, you would open the object and explore the method code. Unfortunately, wizard code is not accessible for such research. Therefore, this section is provided at a slightly more technical level than most wizard explanations so that you have an opportunity to see how it works.

Running the Wizard
The WWW Search Page Wizard is not currently accessible through the Visual FoxPro menus. To run the wizard, type the following commands in the Command window:

CD HOME( ) + '\TOOLS\INETWIZ'
DO WWWPAGE.APP

The wizard presents the following steps.

Step 1 - Table Selection
In this step, you choose any Visual FoxPro table as the basis for a search query. To see more files, click Browse.

Note You should always choose a table located somewhere on the HTTP server. If you don't, there is a high probability that the data will not be located during the search.

Step 2 - Search Key Selection
The result of using the generated WWW search page is a parameterized query based on the search field selected in this step. When the user enters a value on the search page, that value is used as the parameter in the query.

For example, if you select the Company field from the Customer table that shipped with the Visual FoxPro samples, the wizard would construct the following SQL SELECT statement:
Select * From Customer Where Company = ...

Note The wizard currently constructs queries that use only the = operator, not the LIKE operator. Therefore, you cannot enter wildcard characters, such as * or %, in the Search field of the Web page. Using the wildcards with the = operator returns an empty result set.

The wizard only allows you to choose an index expression. This ensures that the query performed will be fully optimized. For instance, if you were to choose the expression Upper(company), the generated query would look like this:

Select company, cust_id, country ;
From customer ;
Where Upper(company) = Upper(%searchparam%)

The left side of the operator, Upper(company), will make the query Rushmore-optimizable since it matches an index expression. Repeating the expression around the user input, Upper(%searchparam%), ensures that whatever the user entered will also match the expression.

Step 3 - Search Page Title and Description
The Search Page Title entry will appear at the top of the page when viewed from a Web browser. The title should describe what your page is for. In the wizard output, this information is embedded in the HTML tags <TITLE> and </TITLE> as follows:

The description is the short paragraph that follows the title and informs the Web page visitor of both the purpose of the site and how to implement the search. If you require the Web page user to enter their search request in a particular manner in order to match values with your data (such as all capitals), then the description should include this information.

The description information will be centered below the title as a separate paragraph as follows:

<HEAD>
<TITLE Welcome to the Visual FoxPro WWW Search Page</TITLE>
</HEAD>
...
<P>
<CENTER>
<P>You can use this page to search any table understood by the Visual
FoxPro application. Be sure to tell the site visitor if there are special requirements
for the search condition, such as entering all capital letters.</P>

Step 4 - Search Page Setup

In this step, you choose the .GIF or JPEG images you want to appear as the header and background images. These images are optional.

The header image appears between the title and description. The background image will be tiled as the background for the page.

<HEAD>
<TITLE>Welcome to The Visual FoxPro WWW Search Page</TITLE>
</HEAD>
<BODY BACKGROUND= "img/SMFOX.GIF">
<CENTER><h1>Welcome to The Visual FoxPro WWW Search Page</h1></CENTER>
<CENTER>
<IMG SRC= "img/SMFOX.GIF">
</CENTER>

Note Be careful when choosing the background image. Large or busy images do not present well when tiled.

The image files you specify can be located anywhere on your network. However, the wizard will create a central repository for all referenced images as part of the page generation process. This central repository is an IMG subdirectory. For example, if you tell the wizard to place the output files on C:\SERVER, the wizard will look for a C:\SERVER\IMG directory where it will store the header and background images for the search and result pages. If the IMG directory does not exist, one will be created.

The last option on the Search Page Setup page controls downloading. When selected, the search page will contain a check box that allows the client to download the data as a text file rather than just viewing the information.

Step 5 - Result Fields Selection
This step contains a listing of all of the fields in the selected search table. The order in which you select the fields also dictates the order in which the fields will be presented on the return page.

Currently, you can only use a single template that allows up to 5 fields. In the future, you will be able to choose between multiple templates, including ones you have created yourself, by using the information in the TEMPFLDS and TEMPSTYL tables.

The information from this step is used to create the .HTX file. This file will be used by the Information Server when creating the HTML stream required for returning the results. Notice that the names of the selected fields appear as variables (inside % ... % delimiters) in the .HTX body itself:

Step 6 - Result Page Setup
Since a Web results page is not really very different from a search page, you can place both a header and background image on this page as well. The placement of the images is the same as that for the search page images: the header image will appear between the title and description and the background image will be tiled throughout the entire page.

On this page you can also specify the maximum number of rows returned on the result page. The default value is set to 10. This is a typical number of return entries for a Web search page.

Note In this Alpha release of the wizard, the number of return entries is the total for the page. In future releases, the wizard will allow you to continue reviewing the result set.

When the .HTM file is created by the Information Server, the information from this page will take on the following format:

<HTML>
<HEAD>
<TITLE>Visual FoxPro Query Return Page</TITLE>
</HEAD>
<BODY BACKGROUND="img/SMFOX.GIF">
<CENTER><IMG SRC="img/SMFOX.GIF"></CENTER>

<HEAD>
<TITLE>Visual FoxPro Internet Data Server</TITLE>
</HEAD>
<BODY>
<CENTER><H1>Search Results</H1>
<H2>VFP Internet Data Server</H2>
<HR>
</CENTER>

Step 7 - Finish
The Finish command button displays the Save As dialog box to ensure that you name the file.

Wizard Output

The WWW Search Page Wizard generates three files: an .HTML file, an .IDC file and an .HTX file. All of the files share the same base name and are located on the drive and directory specified in the Finish step of the wizard.

The .HTM File
The .HTM file created by the wizard is a standard Web page. While HTML details are beyond the scope of this white paper, there is one major point of interest: the FORM ACTION statement.

Typically, the FORM ACTION statement just references the name of the CGI script to execute. In the HTML generated by the wizard, however, notice that the FORM ACTION statement does more than just post a request for execution:

This action requests the HTTP service to call the CGI script, VFPCGI.EXE, with a parameter that identifies the .IDC file to be used by the Visual FoxPro server: IDCFile=VFPPAGE.IDC. This .IDC file needs to reside in the Information Server directory, or whatever you have specified as the HTTP root.

A complete example appears below:

<HTML>
<HEAD>
<TITLE>Welcome to The Visual FoxPro WWW Search Page</TITLE>
</HEAD>
<BODY BACKGROUND="img/SMFOX.GIF">
<CENTER><h1>Welcome to The Visual FoxPro WWW Search Page</h1></CENTER>
<CENTER><IMG SRC="img/SMFOX.GIF"></CENTER>
<P>
</BODY>
<P>
<CENTER>
<P>
You can use this page to search any table understood by the Visual FoxPro application.
Be sure to tell the site visitor if there are special requirements for the search condition, such as entering all capital letters.
<FORM ACTION="vfpcgi.exe?IDCFile=VFPPAGE.IDC" METHOD="POST">
<INPUT NAME="SearchParam" VALUE="" >
<INPUT TYPE="SUBMIT" VALUE="Search">
<br>
<br><INPUT NAME="ReturnAsFile" TYPE = checkbox>Return data as file.<br></CENTER>
<hr><center>
<img src="img/smfox.gif">Generated by the Visual FoxPro WWW Search Page Wizard<br>
</center><hr>
</FORM>
</BODY>
</HTML>

The .IDC File
The .IDC file is the workhorse in this series of wizard-generated files. The .IDC file is based on a Microsoft file format typically used with Internet Information Server.

The standard .IDC components are a data source, a template file, the SQL statement to be used and the maximum number of rows to be returned. For example, the .IDC file might appear as follows:

Datasource:
Template:VFPPAGE.HTX
SQLStatement:
+SELECT Company, Contact, City, Region, Country, Phone
+ FROM 'CUSTOMER.DBF'
+ WHERE Upper(City) = Upper('%SearchParam%')
Maxrecords: 10

This Alpha release of the wizard uses a fixed data source, Visual FoxPro. Therefore, the data source specification in the example is left blank.

The Template parameter references the .HTX file to be used to format the return page. This is the .HTX file generated by the wizard.

The SQLStatement parameter contains the SQL SELECT statement constructed by the wizard. The components are based upon the selections you made in Steps 1, 2 and 5 of the wizard.

The parameter referenced by %SearchParam% is the input value taken from the .HTM file. When the .HTM file is an active client page, SearchParam will contain the actual value entered by the user.

Note that if a Visual FoxPro function has been used, such as Upper(), the SQLStatement parameter includes the function on both sides of the comparison. This ensures that both Rushmore optimization will be in effect for the search (Upper(City) ) and that matches can be found (Upper(%SearchParam%)). In fact, in order to avoid null search results caused by case sensitivity, you should considered using theUpper()function for string comparison indexes.

The value of the final parameter, MaxRecords, was derived from the spinner value found in Step 6 of the wizard.

The .HTX File
The last of the wizard-generated files, the .HTX file, is the template used to create the return page.

The .HTX file is a Microsoft-extended version of the HTML 3.0 standard. This means that there a few extended tags, such as NOBR for "no break." On the whole, however, the contents of the .HTX are fairly straightforward:

<HTML>
<HEAD>
<TITLE>Visual FoxPro Query Return Page</TITLE>
</HEAD>
<BODY BACKGROUND="img/SMFOX.GIF">
<CENTER>
<IMG SRC="img/SMFOX.GIF">
</CENTER>

<HEAD>
<TITLE>Visual FoxPro Internet Data Server</TITLE>
</HEAD>

<BODY>
<CENTER> <H1>Search Results</H1> <H2>VFP Internet Data Server</H2><HR>
</CENTER>
<PRE>
<%BEGINDETAIL%>
<NOBR><B><%Company%></B>: <%Contact%>, <%City%></NOBR>
<%ENDDETAIL%>
</PRE>
<HR>
<DL>
<%BEGINDETAIL%>
<DT><B><%Company%></B>
<DD><%Region%>
<DD><%Country%>
<DD><%Phone%>
<BR>
<%ENDDETAIL%>
</DL>
<HR>
</BODY>
</HTML>

The Visual FoxPro Information Server

Once you have used the wizard to create the required files, you are ready to start the Information Server and let the world access your data.

Running the Information Server
To start the Information Server, make sure that you are defaulting to the server's directory and then run the Information Server application from the Command window:

CD\ServerPath
DO SERVER.APP

If you choose, you can run the source code rather than the application by entering the following from the Command window:

CD\ServerPat
DO SERVER.PRG

The VFPIS.INI File
Before the server can begin executing, it requires certain pieces of information. This information is stored in a VFPIS.INI file located on the Visual FoxPro default directory. The .INI file contains the path of the HTTP root directory (the server root) and the Scripts root directory. The Scripts directory is where Visual FoxPro will look for the .IDC and .HTX files if no path is specified. To browse for either directory, choose the dialog button. Finally, the PATH edit region allows you to enter the path to be searched for the requested data. This entry takes the same format as the Visual FoxPro SET PATH TO where each path is separated by a semicolon.

If there is no VFPIS.INI file, the server displays the following form:

Once the VFPIS.INI file has been located and the contents stored into memory, the server form appears:

The Server Processing Sequence
The list box on the form displays the current action taking place within the server. These actions include reading the .IDC file, executing the query, generating the .HTX file and so on. The typical processing sequence is as follows.

First, the Init event of the main Server form establishes a few fundamentals for the server environment, such as creating the directory into which the semaphore files are placed, creating a cursor that will be used to log the events against the server, and setting up the FoxTools library.

Once the form and its objects have been instantiated, a Timer object takes control. As with any timer object, its importance lies with the code executed within the Timer method. The execution interval for this Timer object is set to every 100 milliseconds.

The method code itself looks for .ATN files in the semaphore directory. When the files are found, they are added to the process cursor and then deleted from the drive. Once the cursor has been updated, control is turned over to a user-defined method that actually performs the Search Page request.

Within the processing method, Visual FoxPro reads the contents of the .DAT file whose name matches the .ATN file, deletes the .DAT file, and then calls the GenPage procedure located in the SERVER.PRG program.

The GenPage procedure parses the .DAT file string into intelligible components. These components include whether there was a "ReturnAsData" request, the maximum number of rows returned, and most importantly, the name of the .IDC file to use for processing.

Once the .IDC file is located, information such as the data source, query statement, etc., are loaded into variables for faster processing. Once the data has been collected, the procedure looks for the referenced .HTX file and replaces the appropriate variables, such as %company%, with actual data. When this process is completed, the HTML return page is also finished and ready to send back to the client.

Along the way, GenPage will also insert a row into the QueryLog.DBF. This table contains one row for each Web page query. The information stored includes when the query was executed (TimeStamp), the .IDC file used and the query parameters.

Program control is now returned to the server form's user-defined method and the newly created HTML stream is written out to a .DAT file bearing the name of the original .DAT file. The server application then writes out an .ACK semaphore file with the same name to inform the CGI script to pick up and transmit the return page.

That task complete, the server checks the process cursor for more work. If there is another request, the cycle begins again. If there are no pending requests, the server just checks every 500 milliseconds for a new task.

Meanwhile, the CGI script, which has been polling for an .ACK file, picks up the .DAT file containing the HTML return page and sends the contents of the .DAT file off to the HTTP service, who routes it back to the requesting client.

The data request, access and return cycle is now completed, as the Web page display contains the resulting data.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

File Name	Description
FOXTOOLS.FLL	A library that ships with Visual FoxPro and is required by the Information Server application.
INETWIZ.DOC	This file.
MYCGI.C	Source code for the CGI script.
SERVER.PRG	Contains the procedural code used to update the query statistics table, generate the return HTML page and page error handling.
SERVER.SCX SERVER.SCT	The server information form. The code within this form searches for and processes .DAT files created by the CGI file, executes the page generation code, handles initial error conditions, and displays the current server processing information.
SPECROOT.SCX SPECROOT.SCT	Server configuration information form. This information includes the file locations for the HTTP server, the .IDC script files and the data file(s).
SERVER.PJX SERVER.PJT	The project containing all of the components for the Visual FoxPro Information Server.
SERVER.APP	The application (.APP) file built using the Visual FoxPro Information Server application.
VFPCGI.EXE	The CGI script used to pass information to and from the Visual FoxPro Information Server.
VFPCGI.MAK	Makefile for the CGI script.

File Name	Description
TEMPFLDS.DBF TEMPFLDS.CDX TEMPFLDS.FPT	A table (with associated index and memo files) that is used to determine the location of the result fields within the template.
TEMPSTYL.DBF TEMPSTYL.CDX TEMPSTYL.FPT	A table (with associated index and memo files) that is used by the WWW Search Page Wizard to create the .HTX template language used for the search result page.
WWWPAGE.APP	The file that contains the WWW Search Page Wizard. While this is a standard Visual FoxPro Wizard, it is not currently available through the wizard menu option.