VFPUtils Article By: M. Asherman

  | Bottom | Unframe | Help |

Querier Development Tasks

Locate in Contents | (framed)

Locate in Discussion | (framed) 

Date Done
(not done)
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.
(not done)
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 have ehp_abortstopshere = .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:

(not done)
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.

(not done)
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 thread.

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 records selected.)

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.

Related details:

  • 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 QryP_UsrParms
    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 VFP window.

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.)
    • Querier.QryM_Open
      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.
    • Querier.QueryCommand
      • 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 exceptional error.
      • 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 previous error.
      • 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 for quma0007.
      • 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
    • 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, ehp_diagforce.
  • 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.  It will 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.

(not done)
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.

| Top | Unframe |


Copyright 2000- 2002, SpaceTime Systems