Creating an
HTML Table Response Page for theWeb
by John Staub, President  Staub & Associates, Inc.
The author would like to thank Michael Joyce, Mike Trout, John Creed, Greg Neid, Peter Rorlick (dBVIPS) and Jean-Pierre Martel for their invaluable editorial assistance.

Why an HTML Response?

When building a Query/Response application for the web, it is fairly easy to use a report (.rep) for a response page. dBASE on the web is already blazingly fast, but we wanted to make it just a bit faster.

What is a Query/Response?

The purpose 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. In dBulletin # 8, we provided examples of how to customize a query/response app, how to subclass webclass.cc, and how to include a method of determining if the value you are searching for exists in the table. Using that same example, we are going one step further. Eliminate the report and stream back an HTML response page with the data in HTML tables. Please refer to dBulletin #8 to get the background details on our initial subclassing methods.

Why bother?

Visual dBASE on the web is already fast, but being programmers, we are always looking for ways to make things run a bit faster/better. In our query/response example, the user enters a value to search for, a program runs and branches out to:

1.  Determine if the value exists in the table. This requires a table lookup.
2.  If the value does not exist in the table, a SorryPage is streamed out.
3.  If the value exists in the table, a variable is set to a particular value, the subclass method returns to the program, and the report is run.

Sounds pretty straight forward...Until we realized we were running an sql statement against a 25 Mb table twice.... Once to determine if the value exists, and again in our report. Our logic was that if we could eliminate one of the above, our app would be just that much faster.

Assumptions

We are using methods unique to VdB 7.5.  The methods discussed in this article will not work in VdB 7.01. Also, this article assumes you have built a query/response (or similar application), have a lookup method to determine if the value your user is searching for exists, and if so, the user is presented with a dBASE report for a response page.

Subclassing Webclass.cc

In the attached file, certnet.cc, there are 4 methods: SorryPage(), NoRecordFoundPage(), IsValueThere(), and ResponsePage().  The SorryPage() method is simply a cut/paste from Webclass.cc with some minor formatting changes and is used to redirect the user if they previously bookmarked the response page; NoRecordsFoundPage() contains special instructions for the user if the value they are searching for is not found, IsValueThere() checks the table to determine if the value the user is searching for exists, and ResponsePage() is presented to the user if the value is found in the table.  Examine Cert.prg (also attached) to see how our program flows.   If the user enters a value, we call the method IsValueThere() at the line oCGI.IsValueThere(mZeroRecordsFound) The following code runs:
 
 
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
   // call response page here
   Class::ResponsePage()
else
   mZeroRecordsFound = true
endif

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

   

Examine the lines starting with if r.first().  If the value is found, we will be at the first row of the records containing our value. Now we call the ResponsePage(), and stream out data in HTML tables with the following:
 
 
Function ResponsePage

/////// Stream out header
this.StreamHeader('DoD Certification Lookup System Response')

with (this.fOut)
   ////// Body tag (starts body of page)
   puts('<BODY>')
   puts('')
   puts('<P>')
   puts('<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%">')

   ////// Header inside chrome band across page
   puts('    <TR>')
   puts('        <TD COLSPAN="2" BGCOLOR="#E4E4E4">')
   puts('             <H3><FONT COLOR="#999999">Your Search Results for ' + cSearch + '</FONT>')
   puts('        </TD>')
   puts('    </TR>')

   ////// Blank Line
   puts('    <TR>')
   puts('        <TD WIDTH="9%">&nbsp;</TD>')
   puts('    </TR>')
   puts('</TABLE>')

   puts('<p align="center"><big>DoD Fire &amp; Emergency Services')
   puts('Certification Program</big></font><br>')
   puts('<big>Lookup System</big></font></big></p>')
   Puts(' ')
   Puts(' ')
   puts('<p align="center">To return to the search page, use the "Back" button on your web browser. To print a copy of this certification record, use the "Print" button on your web browser.</center></p>')
puts('<p align="center"><font color="#FF0000">PLEASE DO NOT BOOKMARK THIS PAGE IN YOUR BROWSER.</center></font color></p> ')

   puts('<div align="center"><center>')
   puts('<TABLE BORDER = "0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%">')
   puts('<TR>')
   puts(' <TD WIDTH="20%"><strong>'+ "Title" + '</strong></TD>')
   puts(' <TD WIDTH="20%"><strong>'+ "Level" + '</strong></TD>')
   puts(' <TD WIDTH="20%"><strong>'+ "Seal #" + '</strong></TD>')
   puts(' <TD WIDTH="20%"><strong>'+ "Date Certified" + '</strong></TD>')
   puts(' <TD WIDTH="20%"><strong>'+ "T-t-T" + '</strong></TD>')
   puts('    </TR>')
   puts('</TABLE>')
   puts('</center></div>')
   puts(' ')
   puts(' ')

   // now insert the fields from the query based upon the number of
   // records found
   for n = 1 to r.count()
      puts('<div align="center"><center>')
      puts('<TABLE BORDER = "0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%">')
      puts('<TR>')
      puts(' <TD WIDTH="20%">'+ r.fields["TITLE"].value + '</TD>')
      puts(' <TD WIDTH="20%">'+ r.fields["CERTLEVEL"].value + '</TD>')
      puts(' <TD WIDTH="20%">'+ r.fields["SEALNO"].value + '</TD>')
      puts(' <TD WIDTH="20%">'+ r.fields["CRTMLD"].value + '</TD>')
      puts(' <TD WIDTH="20%">'+ r.fields["TTT"].value + '</TD>')
      puts('    </TR>')
      puts('</TABLE>')
      puts('</center></div>')
      r.next()
   next
   puts(' ')
   puts(' ')
   puts('</body>')

endwith

////// Close out HTML structure
this.StreamFooter()

return

   

Starting at the line for n = 1 to r.count(), we build a dynamic HTML table based upon the number of records returned by our search.  But note we now only conduct the lookup once. In our old method, we would return to Cert.prg and run our report, which runs exactly the same sql statement that we already ran in the IsValueThere() method.

Another way to format your SQL statement

While we are only working with a 25 Mb table (148,000) records, the sql statement we use may not be the most efficient way to go on larger tables. On larger tables, you could use the SetRange() method like this: (assuming your table is indexed on a character field named SSAN and the index name is SSAN)
 
 
q.sql := 'select * from afcert.dbf'
q.active = true
r = q.rowset
r.indexname := 'SSAN'
r.setrange(cSearch)
   

the rest of the code goes below here

Drop a bit of JAVA script on your response pages.

If you want to provide your users a “Click here to run another query” type hotlink, but don’t want your page counter to increment each time they go back to run another query, try a bit of Java script in your response page.   We did the following:
 
 
// inserted just before the Stream Out Header
local cRecover
cRecover = "Click here to run your search again"
   

Then, at the bottom of your page, place the following code:
 
 
puts(' <TD WIDTH="91%"><CENTER><a href="javascript:history.go(-1)"><FONT SIZE="3" FACE="Arial,Helvetica">'+cRecover+'</font></center></TD>')
   

You may or may not have to define cRecover as we did in this example.  Our response page is subclassed from WebClass.cc, and does not include the parameters for cRecover.

Is this response page method faster?

It’s hard to say...The response seems to be faster, but dBASE on the web is so fast that timing our response has been a bit difficult. One thing we have noticed is that we now have more control over the appearance of our response page, and while watching the server using NT Task Manager, when we have several concurrent users, demands on the processors seem to have been reduced.

To download Certnet.cc and Certnet.prg,  click here
(they are inside a 4Kb zipped file)


For more information on  Staub & Associates, Inc., please visit http://www.staubassociates.com
For information on hosting your dBASE applications or other web sites, please contact wrightd_dBulletin_@dcs-fl.com (take out “_dBulletin_”).