Debugging dbExpress Applications

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

Add a TSQLMonitor component to the form or data module containing the TSQLConnection component whose SQL commands you want to monitor.
Set its SQLConnection property to the TSQLConnection component.
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.