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%"> </TD>')
puts(' </TR>')
puts('</TABLE>')
puts('<p align="center"><big>DoD Fire
& 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 18Kb
zipped executable file)