RAD Studio |  |

Debugging dbExpress Applications
Using TSQLMonitor to monitor SQL commands | Using a callback to monitor SQL commands | See Also
Collapse All

While you are debugging your database application, it may prove useful to monitor the SQL messages that are sent to and from the database server through your connection component, including those that are generated automatically for you (for example by a provider component or by the dbExpress driver).

Using TSQLMonitor to monitor SQL commands

TSQLConnection uses a companion component, TSQLMonitor, to intercept these messages and save them in a string list. TSQLMonitor works much like the SQL monitor utility that you can use with the BDE, except that it monitors only those commands involving a single TSQLConnection component rather than all commands managed by dbExpress.

To use TSQLMonitor

1. Add a TSQLMonitor component to the form or data module containing the TSQLConnection component whose SQL commands you want to monitor.
2. Set its SQLConnection property to the TSQLConnection component.
3. Set the SQL monitor's Active property to True.

Flags for monitoring SQL commands

Flag | Meaning
traceUNKNOWN | All SQL commands.
traceQPREPARE | prepared queries sent to the server.
traceQEXECUTE | Queries to be executed by the server. Note that a single statement may be prepared once and executed several times with different parameter bindings.
traceERROR | Error messages returned by the server. The error message may include an error code, depending on the server.
traceSTMT | Operations to be performed such as ALLOCATE, PREPARE, EXECUTE, and FETCH.
traceCONNECT | Operations associated with connecting and disconnecting to databases, including allocation of connection handles and freeing connection handles, if required by server.
traceTRANSACT | Transaction operations such as BEGIN, COMMIT, and ROLLBACK (ABORT).
traceBLOB | Operations on Binary Large Object (BLOB) data, including STORE BLOB, GET BLOB HANDLE, and so on.
traceMISC | commands not covered by any other flag.
traceVENDOR | API function calls to the server. For example, ORLON for Oracle, ISC_ATTACH for InterBase.
traceDATAIN | Parameter data sent to servers when doing INSERTs or UPDATEs.
traceDATAOUT | Data retrieved from servers.

As SQL commands are sent to the server, the SQL monitor's TraceList property is automatically updated to list all the SQL commands that are intercepted.

You can save this list to a file by specifying a value for the FileName property and then setting the AutoSave property to True. AutoSave causes the SQL monitor to save the contents of the TraceList property to a file every time is logs a new message.

If you do not want the overhead of saving a file every time a message is logged, you can use the OnLogTrace event handler to only save files after a number of messages have been logged. For example, the following event handler saves the contents of TraceList every 10th message, clearing the log after saving it so that the list never gets too long:

Copy Code

    procedure TForm1.SQLMonitor1LogTrace(Sender: TObject; CBInfo: Pointer);
    var
      LogFileName: string;
    begin
      with Sender as TSQLMonitor do
      begin
        if TraceCount = 10 then
        begin
          LogFileName := 'c:\log' + IntToStr(Tag) + '.txt';
          Tag := Tag + 1; {ensure next log file has a different name }
          SaveToFile(LogFileName);
          TraceList.Clear; { clear list }
        end;
      end;
    end;

Copy Code

    void __fastcall TForm1::SQLMonitor1LogTrace(TObject *Sender, void *CBInfo)
    {
      TSQLMonitor *pMonitor = dynamic_cast<TSQLMonitor *>(Sender);
      if (pMonitor->TraceCount == 10)
      {
        // build unique file name
        AnsiString LogFileName = "c:\\log";
        LogFileName = LogFileName + IntToStr(pMonitor->Tag);
        LogFileName = LogFileName + ".txt"
        pMonitor->Tag = pMonitor->Tag + 1;
        // Save contents of log and clear the list
        pMonitor->SaveToFile(LogFileName);
        pMonitor->TraceList->Clear();

    }

Note: If you were to use the previous event handler, you would also want to save any partial list (fewer than 10 entries) when the application shuts down.
Using a callback to monitor SQL commands

Instead of using TSQLMonitor, you can customize the way your application traces SQL commands by using the SQL connection component's SetTraceEvent method. SetTraceEvent takes a TDBXTraceEvent parameter .

The dbExpress driver triggers the event every time the SQL connection component passes a command to the server or the server returns an error message.

Warning: Do not call SetTraceEvent if the TSQLConnection object has an associated TSQLMonitor component. TSQLMonitor uses the callback mechanism to work, and TSQLConnection can only support one callback at a time.
See Also

Connecting to the Database Server

Types of Unidirectional Datasets

Specifying What Data to Display

Executing Commands That Do Not Return Records

Fetching the Data

Accessing Schema Information

Copyright(C) 2009 Embarcadero Technologies, Inc. All Rights Reserved.
What do you think about this topic? Send feedback!