dBASE on the web?

You Bet!!  There are so many advantages of putting dBASE on the web, I'm afraid I'll miss some of the most important ones.  Let's see... OOP... OODML... Custom Classes... inheritance... small, tight executables (the executable for the DoD site mentioned in this article is only 105K)... built-in Report Classes... Web Wizards that streamout dBase code... compatibility with the major backend database engines... leveraging your existing dBASE programming knowledge... SPEED, SPEED and more SPEED... use the same tables and indexes your application uses (no need to convert the data to another format)... pre-built classes in 7.5 (a lot of the work has been done for you already).

Another important aspect of dBASE on the web... turning your customer's existing data systems into information systems for their customers.  Data to Information...

dBASE on the web?...  You Bet!!

7.5 now includes Web Classes

In dBASE 7.01, if you wanted to return an error page, a sorry page, etc, you had to manually code them.  7.5 takes much of the work out of this for you.  In this article, we subclass WebClass.cc (see certnet.cc available in the download files) to add a method of our own to see if the value being searched for exists and to  customize the SorryPage that is used in our example for two different purposes.  The web classes in dBASE 7.5 add tremendous power and flexibility to programming and deploying applications on the web.  I recommend you study both the sample application and the web classes that come with 7.5 to better understand the power behind these new classes.

The web classes are not the only new features in 7.5.  Significant improvements have been made in other areas as well.  When a query was run in 7.01, a temporary file was created to handle the response output.  Unfortunately, this file was not deleted after the response page was displayed.  This has been corrected.  Although temporary files are still created, when using IIS if the Current_User or IIS_User has delete permissions, these files are automatically deleted for you.  Additionally, in 7.01,  if a user bookmarked the response page and later returned to it via the bookmark, you could end up with several instances of vdb7run.exe running simultaneously.  This caused us headaches in that eventually, the server would quit responding.  These two problems are corrected in 7.5.

Other new features include the StdIn/StdOut Method, and many others.  The help file contains more information on new features than I can go into in this article.

What is a Query/Response?

The object of the Query/Response Wizard is quite simple... take data from a standard table, create a “lookup” page that allows users to search for a particular value, and return a response page based upon the search.   You might ask how that helps your client...  The first dBASE web application I built was for the U.S. Department of Defense (DoD).  To make a long story short, I built and currently maintain a dBASE 7.01 (soon to be 7.5) application for the Air Force Civil Engineering Support Agency (AFCESA) at Tyndall AFB, FL.  Along with the International Fire Service Accreditation Congress at the University of Oklahoma, AFCESA is responsible for issuing and maintaining professional certifications for all DoD fire fighters.  There are over 47,000 personnel records (active and retired) in the system with 148,000 + related certification records.  These professional certifications have a significant impact on job eligibility, promotions, work assignments, etc., so it is easy to see that the people holding these certifications want to ensure their records are current.  The office that maintains these records was constantly inundated with phone calls from fire fighters, potential employers and others wanting or needing to verify someone's certifications.  In early July 1999, AFCESA received funding (and permission) to post this information on the Internet.  Since the data was already in dBASE tables, it was only natural to use dBASE's native ability to post this information on the net.  The site (http://www.dodffcert.com) went “live” on August 8, 1999 and has received over 22,000 visitors and over 200,000 query requests.  An average day sees anywhere from 800 to 1800 queries.  The office that maintains the certifications realized a substantial reduction in phone calls... freeing the people in that office to focus on their primary duties.  Relate that to dollars and it is easy to see how dBASE has allowed this office to take data and present information to users while decreasing their workload.

When the DoD site was first demonstrated, there were quite a few skeptics... “dBASE on the web, yeah right”...  We were aware of this sentiment so we built two demo sites, one in dBase and one using .asp pages and ODBC drivers.  Both sites connected to the same database.  During one demonstration, we had to prove to the client we were connected to the internet.  We actually interrupted one meeting to go to a different computer, log on the net, go to both sites and run queries.  They didn't believe our laptop was connected to the web.  The dBASE site consistently returned results in 2-3 seconds... the .asp site with ODBC drivers took 2-3 MINUTES to return the same query.

What's needed to begin

1.  Visual dBASE 7.5 (for the examples used in this article)
2.  A web server or web server capabilities (Note: See the web wizards help file regarding which servers are currently supported)
3.  A standard dBASE report
4.  A standard dBASE table
5.  You must know the URL for your web server/site (normally http://localhost... or perhaps a locally assigned URL such as http://10.10.10.100) and the folder structure for your web server.  In this article, we will assume the folder structure on your web server is d:\inetpub\wwwroot\mysite... (Note: if localhost does not work on your computer, you can substitute 127.0.0.1 for localhost)

Assumptions

We use an NT server running IIS.  We only use the C drive for the operating system.  All of the web sites, data, etc., are on other drives.  We are currently hosting dBASE apps built in 7.01 as well as 7.5.  Additionally, we are using BDE 5.10.  There are several other web sites on this server, some using simple html pages, some using .asp pages, some using ODBC drivers, etc.  While dBASE is an important consideration, we must be as flexible as possible to meet our customers needs.  This article also assumes you have installed and have working a suitable web server.

First the report

In building the report, I strongly recommend you use a BDE alias identifying the path to your database/table(s).

The Q/R uses a public variable named cSearch to accept the search criteria from the .htm page and pass that information to your application.  This necessitates a modification to the SQL statement in your report.  In our report, our query sql statement is: sql = [select * from afcert.dbf WHERE SSAN ="]+cSearch+["]   (Note: afcert.dbf is the name of our table, SSAN is the keyfield in the index).  To change your report, simply open it up in the source editor, make the necessary changes, and save.  I always copy and paste the new SQL statement above the ** END HEADER -- do not remove this line  statement in the report.  This way, if I open the report in the designer, make changes and save them, I can simply copy and paste the statement back into the query.  If you use this approach, don't forget to remark out the statement above the header.

Also, when selecting Output for your report, you can specify 5 - CGI Response.  If you forget to specify the output, the program created by the web wizard will handle this for you.

On to the Development

Startup Visual dBASE and navigate to your web\Wizards folder.  Select the Programs tab in the IDE, and you see webwizard.prg.  Double click this file (or right click and select Run) and you're off and running.  Your screen should look something like this:

Click on the Query And Response button.  You will get a screen reminding you that you need to know certain information regarding the paths to your web folders, cgi-bin directory, your URL and one .rep file.  Click on Next and you will see:

Since we host multiple web sites on our server, the paths displayed here may not match your setup.  You will need to replace “mysite” with the actual folder name and “myform” with the name of your application.  The .prg and .exe files created by the Wizard will be named whatever you replace “Myform” with.  The URL to CGI statement can look like the example or perhaps http://www.mysite.com/cgi-bin.

The next several steps in the wizard take you through building the .htm page for your site.  These are pretty self-explanatory so we won't go into any detail here.

Now that I've got it, what do I do with it?

Now is a good time to test your application.  Using the examples we outlined above, start your browser and set the address line to http://10.10.10.100/mysite/myform.htm (substituting your address as necessary).  Run a few searches using values you know exist in the table.  Then run a search with a value you know does not exist in the table.  Your result should be a blank report.  Not quite what you would want to present to the customer.  This is where you can tap into your existing knowledge of dBASE and begin using the web classes in 7.5.

Personalizing your web app by Subclassing WebClass.cc

The first thing we wanted to do was to present the user with a SorryPage if their search returned no records found.  This is where we began to explore subclassing WebClass.cc.  Looking up values in a table is a fairly straightforward process, but we also wanted to customize the SorryPage method in WebClass.cc.

One of the greatest advantages of developing in dBASE is inheritance.  In order to use WebClass.cc and make changes to the SorryPage and add our lookup method, we created a custom control (.cc) named certnet.cc and just copied the SorryPage method into it, added the necessary class... endclass statement, made our changes to the SorryPage method, and added our lookup method.  So our certnet.cc looks something like this (to review all of our code, please see certnet.cc contained in the download files):

// example of the Class statement in certnet.cc

Class CertnetCGISession of CGISession from "Webclass.cc"
  the code for the SorryPage method is here
  the code for IsValueThere method is here
EndClass

The lookup method

If you do not make changes to the code created by the wizard and no records are found in your search, a blank report is presented.  This is not quite what we wanted our users to see so we created a “lookup” method so we could call the SorryPage if no records were found.  Our lookup code (in certnet.cc) is as follows:

/* function IsValueThere tests to see if records matching the
variable cSearch exist in the table and sets
mZeroRecordsFound to true if none are found

Called by: Certnet.prg
*/

function IsValueThere(mZeroRecordsFound)

db = new database()
db.databasename = "CERT"
db.active = true

q = new query()
q.database = db
q.sql = [select * from afcert.dbf WHERE SSAN ="]+cSearch+["]
q.active = true

r = q.rowset

if r.first()
  mZeroRecordsFound = false
else
  mZeroRecordsFound = true
endif

db.active = false
q.active = false
release object db
release object q
return mZeroRecordsFound
// end of IsValueThere

Notice we are passing the parameter (mZeroRecordsFound).  We accomplished this by going back to the .prg file, creating a local variable named mZeroRecordsFound, setting it's value to false, and then calling this method.  The call to the method is:

oCGI.IsValueThere(mZeroRecordsFound)

Note the last line of the lookup code above.  We return the value of mZeroRecordsFound.  Now we have to tie this in so we can call the SorryPage if no records are found.

The SorryPage in WebClass.cc provides a good generic starting point, but we wanted something a bit more unique.  All we really did with the SorryPage method was change some of the fonts, centered the text, etc. Our certnet.cc is included in the download file so you can compare it against the method in WebClass.cc.

Making the necessary changes in the .prg file

Since the program file created by the web wizard is a standard .prg file, you can easily edit the code through the Source Editor.

We now have a method to determine if the users search value exists in the table, and if so, the standard response page is presented.  Now we can use the value of mZeroRecordsFound to present a SorryPage if the value does not exist.  First, we need to change the call to the methods in WebClass.cc.  In the .prg file created by the wizard, there are already calls to WebClass.cc.  We replaced these calls with our own, so our calls are now:

Set proc to Certnet.cc additive
oCGI = new CertnetCGISession()

This now allows us to use our custom methods as well as the methods contained in WebClass.cc.  Now, just below the following if...endif statement in the .prg:

if oCGI.isKey('SEARCH')    // if key exists
  cSearch = oCGI['SEARCH'] // set value of cSearch
endif

We added another if...endif, so our .prg looks like:

if cSearch <> " "  // cSearch is not empty
  local mZeroRecordsFound
  mZeroRecordsFound = false
  oCGI.IsValueThere(mZeroRecordsFound)
  if mZeroRecordsFound = true
    oCGI.SorryPage('Sorry, no records were found for ' + cSearch, ;
    "DoD Fire Certification Lookup System", ;
    "Press the back button on your browser and try your search again,";+
    "or email the DoD Administration Center from the previous page")
  else
////// Instantiate report and set property for CGI output
    set procedure to "D:\INETPUB\FF2\CERT.REP" additive
    r = new CERTREPORT()
    r.Output = 5 // CGI Response to StdOut
////// Render report to the CGI Response file
    r.render()
  endif  // mZeroRecordsFound = true
else    // cSearch is empty
// send a variable with a clickable link to certnet.cc SorryPage,
// our subclass of webclass.cc
public cURL,cVisitOurSite
  cURL = "http://www.dodffcert.com"
  cVisitOurSite = '<A HREF=" '+ cURL +'" >Please click here to run a query</A>'
  oCGI.SorryPage('It appears you bookmarked this page', ;
  "DoD Fire Certification Lookup System", ;
  " "+ cVisitOurSite)
endif

catch (exception e)
  oCGI.errorPage(e)
endtry

r = null  // Clean up
////// all done
Quit

Take a close look at the line directly under the if mZeroRecordsFound = true.  This is the call for the SorryPage method contained in Certnet.cc.  Near the top of the .prg file you'll notice that the wizard has already Set Procedure to Certnet.cc additive and used a couple of functions already.  The SorryPage() method accepts three parameters: cMsg, cSubTtl, and cRecover, in that order.  So if mZeroRecordsFound is returned as true, we display a highly customizable SorryPage.  Much neater than just an empty report.  One other comment.  The Set Procedure line for the report contains a hard-coded path.  That path statement is only required by the compiler to ensure the report is built into the executable.

As mentioned earlier, one of the problems we experienced with our version 7.01 applications is that users had a tendency to bookmark the response page.  Using their bookmark to return to that page caused another instance of vdb7run.exe to be loaded.  After several instances of users returning via the bookmark, the server would quit responding.  This problem was fixed in Version 7.5.  However, users can still bookmark the response page. Since the response is a dynamic page (i.e. the response page is built based upon each users search and is not a “stored” or static page), users would simply get a “Sorry, No records found” response.  We decided to take this one step further in the hopes that providing a "bookmark" SorryPage would deter users from bookmarking the response page, and provide a more accurate response if they did.  We also wanted to give the user a clickable link to the web site on this “bookmark” SorryPage.  All within a character variable being passed to a custom control that will be displayed in your browser!!

Now that you have modified the .prg file, you must rebuild the executable.  You accomplish this by creating a project, adding the .prg, .rep, Certnet.cc  and WebClass.cc, rebuilding and recompiling.  It's that simple.

You now have a fairly complete Query/Response application with nice response page for display,  a customized SorryPage if the user's search value was not found, and a SorryPage if the user bookmarked the response page and returned via the bookmark.  Not only does the “bookmark” SorryPage text differ from the “value not found” SorryPage, but it also contains a clickable link so your visitors can go to the site and run a query.

What do all these changes mean to the end result?

Assuming the user searched for 123456789 and the value was not found, our SorryPage now looks like this:

And the SorryPage for those users that bookmarked the response page looks like this:

Although we only subclassed the SorryPage method, you could subclass each and every method in WebClass.cc to suit your needs.  Your imagination is the only limit.

Contacting the Webmaster for assistance

There is a built-in method for assigning a Webmaster email address in WebClass.cc.  In your program file, just below the line:

oCGI.Connect()

you can identify an email address similar to: (Note - this must be a valid email address)

oCGI.setWebMasterAddress("webmaster@mydomain.com")

Should your users receive an error page, they can simply click on this link to send the webmaster an email message.

Modifying the .htm page

You can use any standard .htm editor to modify the first page of your application.  A word of caution:  Be extremely careful in modifying any of the code used in the “form”.  Unless you are familiar with html, we recommend you leave this for another time.  That said, you can insert your own graphics, text, links and other links, etc., till your hearts content.  One thing you must edit prior to moving your application to the web is the call to your executable in the .htm page.  Notice the URL called by the Submit button.  If you are running your application locally, as in our example, you will need to edit this line to accurately reflect your web site/structure on the net.  In our example, we use http://10.10.10.100, your call may be to http://www.mysite.com/cgi-bin/myform.exe .

How fast is all this?

The table we use for the DoD site currently contains over 148,000 records.  Standard response time (depending on your connection, etc.) is under 2 seconds.  Even with the additional overhead of looking up the search value to see if it exists, etc., dBase on the net is blazingly fast.  Oddly enough, it seems the more concurrent users we have, the faster the response.  During an average weekday, we generally see between 4 and 21 concurrent users at any given time.

What actually needs to go on the web server?

Provided your hosting service has the necessary runtime files and BDE, the only files you need to put on the web server are:

1.  Your table(s)
2.  Your .htm page and your .exe
3.  Any image files you may need or use

Also, ensure your host creates the required alias in the BDE.

Where to put your files on the web server?

There has been a lot of discussion in the dBASE news groups on folder preferences.  Keeping in mind we host multiple sites, for our applications and sites we apply the KISS rule.  The .htm file(s) go in the root folder, the executable goes in a cgi-bin folder under the root folder, images go in an image folder under the root, and data generally goes in a database folder under the root.  We do not necessarily advocate any particular site structure; this one just works well for us.

Running 7.01 and 7.5 web apps

We host web apps developed using both 7.01 and 7.5.  However, we only keep the 7.5 runtimes on the server.  The 7.01 developed apps run fine using the 7.5 runtimes.