| Bottom | Unframe | Help |

Locate in Contents | (framed)

Locate in Discussion | (framed) 

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:

• erma0007: changes for support of nested ErrHandlers
• cmma0001: support standard error handling facilities in Commander
• erma0008: optional ErrHandler Init argument for target indirection
• cmma0007: quiet Commander subclass for nested error handling requirements
• erma0010: pick up default diagnostic/tracing properties via target indirection
• erma0017: propagate Aborts and error info up the target indirection hierarchy
• svma0013: supply error-handling logic for all methods of SaverForm
• quma0002: provide fuller error handling coverage for all Querier methods

• 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.

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.

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.

• 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
    • add optional argument, targob_arg
      Support this standard ErrHandler Init Event argument.
    • add an initial dodefault(m.targob_arg) step
      To support ErrHandler's target indirection and diagnostic initializations.
    • use this.ehm_showapperr for error reporting
    • beef up arg validation for metafi_arg
    • return ehp_success
      Don't depend on function return value from this.QryM_Open() until erma0024: RETURN TO ... .F. for non-local failure returns is done.  Take no chances on properly detecting failure.
  • 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
    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, 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.