RAD Studio
ContentsIndex
PreviousUpNext
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).

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:

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;

 

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.

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.

Copyright(C) 2008 CodeGear(TM). All Rights Reserved.
What do you think about this topic? Send feedback!