<< Click to Display Table of Contents >> Navigation:  Application development > Debugging > SQL Trace

The SQL Trace facility is a useful debugging tool that opens the SQL "black box," allowing you to track the SQL statements sent to the servers when their BDE function calls are executed. SQL Trace is implemented as the SQL TRACE option in the Windows Registry and as a callback to return trace information.

Configuration option

To set the SQL trace mode, use the SQLTRACE option in the DRIVER\INIT section of the Windows Registry (settings\driver\driver_name\init\TRACE MODE) for the appropriate driver. The option takes a numeric value (actually a bit mask) that determines how much information to log. The Windows OutputDebugString call is used to output the requested information to the debug window. The following table shows which information is logged based on bit settings:

Examples

1.To trace only prepared and executed query statements, sets bits 0x0001 and 0x0002 (that is, set SQLTRACE to 3)

2.To trace only vendor calls, set bit 0x0100 (that is, set SQLTRACE to 256)

Because the value of SQLTRACE is evaluated as an unsigned integer, a value of -1 will turn on all bits, and therefore all of the above events will be traced.

dbTRACEMODE

To programatically override the DRIVER option for any database, use the database property dbTRACEMODE with DbiSetProp. TRACE MODE is a DRIVER option in the Registry that determines the trace behavior for all database operations associated with the driver.

cbTRACE

To retrieve trace information, use the system-level callback cbTRACE. The trace string retrieved through the callback is the same as that which goes to the debug window via OutputDebugString.

The TraceDESC structure is used to return trace information to the callback:

typedef struct       // trace callback info
     {
        TRACECat    eTraceCat;     // trace category
        UINT16      uTotalMsgLen;  // total message length
        CHAR        pszTrace[];    // trace string
                                   // (recommended size = DBIMAXTRACELEN (8192))
     } TRACEDesc;

 

 typedef enum         // trace categories
    {
       traceUNKNOWN   = 0x0000,   
       traceQPREPARE  = 0x0001,   // prepared query statements
       traceQEXECUTE  = 0x0002,   // executed query statements
       traceERROR     = 0x0004,   // vendor errors
       traceSTMT      = 0x0008,   // statement ops (i.e. allocate, free)
       traceCONNECT   = 0x0010,   // connect / disconnect
       traceTRANSACT  = 0x0020,   // transaction
       traceBLOB      = 0x0040,   // blob i/o
       traceMISC      = 0x0080,   // misc.
       traceVENDOR    = 0x0100,   // vendor calls
    } TRACECat;

 

The TRACECat enums have the same bit sequence used to set the TRACE MODE configuration option, and can also be used (singularly or piped together) as input to the dbTRACEMODE database property. You can use the uTotalMsgLen field of the TRACEDesc structure to determine whether the returned string (in pszTrace) has been truncated.

Example: Registering a cbTRACE Callback:

Note: Before calling DbiRegisterCallBack() for SQLTRACE, the argument pTraceInfo must be allocated for the size of (TRACEDESC) plus DBIMAXTRACELEN.

DbiRegisterCallBack 
     (NULL, 
     cbTRACE, 
     iClientData, 
     sizeof (TRACEDesc) + DBIMAXTRACELEN,
     (pVOID)pTraceInfo,          // ptr to client-allocated TRACEDesc
     (pfDBICallBack) lpfnTrace);