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.
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.
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.
In the attached file, certnet.cc,
there are 4 methods: 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,
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:
db = new database()
q = new query()
db.active = false
Examine the lines starting with
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:
/////// Stream out header
////// Header inside chrome
band across page
////// Blank Line
Fire & Emergency Services')
// now insert the fields from
the query based upon the number of
////// Close out HTML structure
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'
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
cRecover = "Click here to run your search again"
Then, at the bottom of your page,
place the following code:
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)