Debugging dbExpress Applications
Go Up to Using dbExpress Components Index
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, Data.SqlExpr.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
A number of flags are present in order to help you monitor the various SQL commands. These can be used in the OnTrace event as in the following example:
procedure TForm26.SQLMonitor1Trace(Sender: TObject; TraceInfo: TDBXTraceInfo; var LogTrace: Boolean);
begin
if TraceInfo.TraceFlag = TDBXTraceFlags.Execute then
begin
{ statement }
end;
end;
The following table lists the trace flags:
| 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 it 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 Data.SqlExpr.TSQLMonitor.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.
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.