|capture and display Rushmore optimization info in diagnostic tracing
As an extension of the additional Querier properties
and tracing already introduced, also include a new property,
QryP_Rushmore, containing the results of VFP's SYS(3054, 11)
function. This is a little trickier than it should be, because of
the non-standard way that VFP returns this information. (The
Rushmore optimization info is directed into the current user output
window, or VFP's main window.) Once we've captured the Rushmore
optimization information in a Querier property, it's an easy matter to
include it in Querier's diagnostic tracing message, where it can be
recorded or displayed elsewhere.
|demonstrate how to propagate BTCrit errors to querier
When BTCrit encounters an unusual error, the resulting detailed error
information needs to be propagated back to Querier, so that the
Querier.QryM_Run method (formerly named QueryCommand) can fail cleanly. Also, it is desirable
to have a way of initializing BTCrit's diagnostic options by picking these
up from Querier. Both of these things can be accomplished by making
use of target indirection in the invocation of BTCrit, which is done via
the Querier metafile field, usrparmexp. It would seem desirable to
= .T. to prevent an Abort from BTCrit leading to an Abort of Querier.
This work may require testing on the BT
server, to be sure to obtain a recent enough copy of BTCrit and
working sample configurations. The Querier test cases
include some BTCrit examples, demobt05 and demobt06, which need to be
updated and re-tested. Note that there are BTCrit sample
configuration files to go with these examples (in the Querier directory),
and these also need to be updated.
Querier's use of BTCrit is an example of a "nested" error handling situation,
i.e. where one ErrHandler-based object (Querier) invokes a second
ErrHandler-based object (BTCrit). Compare with an even more
complicated case of nesting that was dealt with in Saver's use of
Commander. Also see:
|create help pages documenting Querier in detail
The Querier facility should have detailed programmer-oriented
documentation. Old notes dealt only
superficially with how to run the demo, references to comments residing in
source code, and a crude task list. What is needed is a proper set
of help pages explaining in detail:
- what is Querier
- how to run the demo
- the metafile, querydf.dbf
- sample test cases
- error handling aspects
- diagnostics, debugging, etc.
- customizing the wrapper
- adding Querier into a form
These help pages can then be assembled into a convenient HTML Help file
(sharing the same pages), as done for XFormDBF.
|query post-processing options for DENULL and suffix-stripping
There are a couple of annoying features of VFP/SQL's JOIN behavior, which
get in the way of straightforward, efficient use of XFormDBF
against the results of a query. The simplest, cleanest way to
dispense with these problems is to introduce some Querier
extensions. Specifically, it would be useful to add a mechanism for
removing undesired NULLs and/or the "_a" fieldname suffix
automatically added by VFP in certain types of queries.
The first issue is that some types of JOINs may introduce NULL values, even though none of
the original tables contained or even allowed NULLs. Since the presence of NULLs can
significantly complicate processing, we need an option to use blank
(empty) values instead of NULLs. (This could just as well have been a
built-in option of VFP SQL, but there is no indication that Microsoft plans to
add such an option.)
I created a self-contained standalone UDF, DENULL.PRG, which removes
NULLs by altering the table structure, and this approach seems to be
efficient for large tables. The proposed change would be to
incorporate the core logic of DENULL.PRG
into a new method of Querier, rather than requiring applications (e.g. Job
Control or a wrapper function) to explicitly invoke the DENULL UDF
interface. The chief advantages of moving DENULL logic into Querier
would be improved error handling and simplification of application logic.
The second issue is
that VFP tacks an "_a" suffix onto duplicated field names in SELECT * ... JOIN
... queries, which
means that the original field name may not appear at all in the result, simply
because there is another field with the same name. For the first instance
of such duplicated field names, we might as well use the original unaltered form
of the field name, i.e. the "_a" suffix automatically supplied by VFP is unnecessary.
Again, we could use a new Querier option to fix this problem by altering
the output table structure to strip off the undesired "_a"
suffixes from field names.
Although these are really two distinct problems, the similarities
suggest that one new Querier method could deal with both options at the
same time. In addition to this method, a couple of new Querier
metafile fields should be added, making the removal of NULLs and stripping
of "_a" suffixes independent, per-query options.
Nadya suggested these changes in her posting
of 2/9/01 and further clarifications under that
|additional Querier properties and diagnostic options
Introduce some new Querier properties for capturing useful information
about the the most recently executed query. These additional
properties might include the expanded SELECT clauses, output destination,
results of the user parameters dialog, and the query's duration.
(There already exists a qryp_tally property, which contains the count of
We also need a convenient way to display and/or log these query
properties and statistics. The natural choice would be to employ
ErrHandler's tracing and logging features, including an API
for support of a user-defined logging function. (Note that Querier is an
ErrHandler subclass, so it already supports the diagnostic message dialog
and related ehp_... properties.) A new Querier trace level would control the generation
of diagnostic information after running each query.
This topic was brought up by Nadya
on 1/25/01, and it was also mentioned previously in old
text-based notes going back to at least a year before that.
- see the documented Example
of Selective Tracing
- also review more examples of tracing in other apps
E.g. see SaverForm.SVM_Release, Destroy.
- querier.h: add new macro for trace level, QUER_TRACE_ALL = 11
Choose a prime number that won't conflict with BTCrit or any other MDA apps.
Note these other trace levels already in use: SV_TRACE_ALL = 5, ERRH_TRACE_ERRHERR = 2, ERRH_TRACE_ACTIVATION = 3, ERRH_TRACE_ACTIVATION2 = 9, ERRH_TRACEDEMO_ALL = 5, BTCR_TRACE_RUN 53.
- new property QryP_CleanupExp
cleanup expression for the most recent query.
- new property QryP_Deleted
value of VFP's SET DELETED setting that was in effect for the most recent query.
- new property QryP_Elapsed
elapsed time, in seconds, for executing the most recent query.
- new property QryP_InitExp
initialization expression that was used for the most recent query.
- new property QryP_NextDest
expanded output destination clause of the SELECT command most recently executed.
- new property QryP_SelectCmd1
expanded first line of the SELECT command most recently executed by method Querier.QryM_Run.
- new property QryP_SelectCmd2
expanded second line of the SELECT command most recently executed.
- new property
results of the optional user parameters dialog, or .F. if none was used.
Note the very minor annoyance that use of Querier's diagnostic tracing causes the
status bar message generated by the SQL query to disappear. It's
possible that an adjustment of errhshow.prg
could fix this, but leave that for another time.
Another useful option would be to make available VFP's Rushmore
optimization information, as displayed by sys(3054, 11). This is a
little tricky to capture in a Querier property, because of the way that
sys(3054) operates, but it could be done (somewhat awkwardly - e.g. by
redirecting screen output to a file). In the meantime, a less versatile
alternative is simply to enable displaying Rushmore optimization information in the main
|rebuild and test Querier with latest ErrHandler changes
Rebuild Querier to pick up the latest version of ErrHandler as of changes for support of nested ErrHandlers.
Test all of the sample queries in querydf.dbf to confirm that there are no
compatibility problems (none are expected).
|provide fuller error handling coverage for all Querier methods
Querier was the first application to employ the ErrHandler
facilities, which have since undergone further extensions and wider use in
other applications. The current implementation of Querier only uses
some of the available error handling facilities within its most important
method, QueryCommand. This old logic (especially the Init method) should be reviewed/updated, and
all other Querier methods should also support appropriate error handling, as
done for Commander. Specific Querier changes include:
- changes to class Querier
- property sheet initializations
Review these to see if any ehp_...
or qryp_... defaults should be adjusted. These settings look
OK as generic defaults, but it may be preferable to set
ehp_abortstopshere = .T. for use with BTCrit (see quma0007).
Introduce the new qryp_cleanuponabort property, which can be set
to permit the metafile-based cleanup
step after a user Abort. (The default is that an Abort
causes cleanupexp to be bypassed.)
- Querier.Init method
- Querier.Destroy method
Should be OK as-is, because there's very little likelihood of
errors in this tiny piece of code. (Likewise, I avoided any explicit error handling for this method in
Commander.) Explicit error handling logic in Destroy event
methods would generally be a tricky matter, because of the
possibility of a previous error. (Also see erma0018: options
for dealing with multiple consecutive errors.)
Requires the usual adjustments for proper error handling, as
illustrated in Example of
Context-Sensitive Error Reporting. (Note that this
method is for internal use only, called by both Init and QryM_Run.) Compare with similar method of Commander.
- rename this method to qrym_Run
Follow a consistent naming
convention for all Querier methods (see cmma0004:
revise Commander property, method, & macro names for
consistency about similar changes that were made to
Commander). Making this change now will avoid the need
to document the obsolete name. For compatibility, retain
an equivalent stub method named QueryCommand, which passes on
the call to qrym_Run. This stub can be eliminated once
it is confirmed (with Nancy and Nadya) that all references to
QueryCommand have been phased out.
- make proper use of this.ehm_zero(
Put this at the start and also into the main query loop,
in order to clear all error properties and propagate errors
via target indirection, if applicable.
- supply error trapping for all metafile field eval steps
All common, predictable types of errors, such as invalid
expressions in metafile fields, should be explicitly
trapped, rather than allowing them to fall into the category
of "exceptional" errors. This will make
Querier more robust, provide more informative error messages,
and assure that proper cleanup steps can be taken on localized failures.
- substitute new macros for error codes & messages
Follow the newer convention used in other MDA apps for
naming of macros and grouping ranges of error codes by
method. Retain the old macros in querier.h,
with suitable redefinitions to map these into newer codes.
Eventually these old macros should be phased out.
- include Query Id in all error messages, where appropriate
Complete, self-contained error messages will make it easier
for the user to know exactly where and what the problem is.
- avoid blank lines in error message text
because these interfere with readability of error logs.
- eliminate superfluous old error handling logic
which was made unnecessary by proper use of ehm_zero(
), a method introduced on
11/24/99 after the original
ErrHandler of 11/6/99 was applied to Querier.
- use this.ehp_success to test for detection of errors
This is the preferred way to check for errors, because it's
the simplest, most efficient, and most comprehensive test.
- avoid metafile position restoration if it was closed
Allow for the possibility of metafile closing as a side effect, since Querier
supports automatic reopening as needed. Don't let closing of the metafile in the cleanup step cause an
- use this.ehp_appname in status bar prompt messages
instead of using the less flexible (and less appropriate) macro, QUER_CLASS.
- complications related to looping
Take into account the fact that there may be looping behavior,
to prompt for multiple output destinations. Note that the loop may continue even if there
was an error, if repetition is enabled. This means that we need to re-zero the error information on each loop
cycle, via ehm_zero( ).
- clean up and consolidate logic for cleanupexp step
Localize this logic in one place, at end of method (after the
main query loop). Trap errors upon evaluating cleanupexp,
but preserve any previous error otherwise. Use new flag
property, qryp_CleanupOnAbort, to determine whether this
cleanup step should be performed after a user Abort from a
- complication of possible consecutive errors
The possibility of consecutive errors arises because we allow the cleanup step
(cleanupexp) to be performed even after a previous error.
This seems reasonable, because the cleanup is generally
needed, whether or not the query was successful. If
there is a second error in the cleanup step, it may well be
unrelated to the error that caused the actual query to fail.
Extra care is required to preserve previous error information,
unless it is superseded by a final cleanup error. I've
punted slightly on this logic, because the remaining flaws are
minor and obscure. See SaverForm.SVM_Refresh for a more
rigorous solution to a similar problem, and see erma0018: options
for dealing with multiple consecutive errors for more
about the issue in general.
- consider how BTCrit errors should be propagated
Suppose that BTCrit uses target
indirection to propagate its
error information to Querier. (Recall that I recently
added the targobj_arg to BTCrit, for just this purpose.)
Be sure that this approach
can work. Treat this as a separate
task, since it goes beyond the scope of generic Querier
aspects. For starters, set Querier.ehp_abortstopshere
= .T., since this seems likely to be necessary and it only
pertains to the BTCrit nested error handling scenario.
Make preliminary changes, but save final testing with BTCrit
- review need for restoring the ehp_interactive setting
The present logic alters this flag and doesn't attempt to
restore its initial default state. This seems a bit unclean and
risky, but it's not a problem now, because there are no other
externally used Querier methods.
- check for any error handling issues in dialog querdest.scx
This dialog seems pretty robust. Failures tend to occur on the SELECT command for
any garbage entered via this dialog. GETFILE/PUTFILE handle invalid paths.
- changes to include file querier.h
- new macros used by wrapper for diagnostics
Added QUER_CLASS_DIAG and QUER_CLIBQ.
- macros for new error codes and msgs
- redefine obsolete error code/message macros
Point these to their newer counterparts, but retain old macros for
compatibility until confirmed phased out.
- new QuerierDiag subclass of Querier
The QuerierDiag subclass, for use in the demo wrapper function,
differs only in having the following properties set to .T.: ehp_diagmode,
- changes to the wrapper function, querier.prg
- use the cleaner newobject() method instead of SET CLASSLIB + addobject()
- enable error handling diagnostics by default
I.e. launch an instance of class QuerierDiag (instead of Querier), to make it easier to
get to the diagnostic dialog for demo purposes. The user
can simply uncheck "Diagnostics" and "Diag Force"
to operate in the non-diagnostic mode, if desired.
- test out all error cases
Be sure that each error case is tested at least once, to catch any
obvious programming errors.
Another task that should accompany these changes is quma0004: additional Querier properties and diagnostic options.
save time to combine these changes into one Querier update, and makes it possible for Nancy to phase out her manual Querier
edits. Also this will allow me to give a good illustration of
Querier-specific diagnostic tracing, with a custom Querier trace level for
reporting additional details after executing the query.
|convert old querier.txt documentation into a web page
As done for other applications, e.g. similar
task for XFormDBF, take the old text-based task list and documentation
for Querier (querier.txt) and put it
into more convenient HTML form. This will make it easier to migrate
tidbits into the newer web organization.