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,

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.

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 TForml.SQLMonitorlLogTrace(Sender: TObject; CBInfo: Pointer); var

LogFileName: string; begin with Sender as TSQLMonitor do begin if TraceCount = l0 then begin

LogFileName := 'c:\log' + IntToStr(Tag) + '.txt'; Tag := Tag + l; {ensure next log file has a different name } SaveToFile(LogFileName); TraceList.Clear; { clear list } end; end; end;

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 SetTraceCallbackEvent method. SetTraceCallbackEvent takes two parameters: a callback of type TSQLCallbackEvent, and a user-defined value that is passed to the callback function.

The callback function takes two parameters: CallType and CBInfo:

  • CallType is reserved for future use.
  • CBInfo is a pointer to a record that includes the category (the same as CallType), the text of the SQL command, and the user-defined value that is passed to the SetTraceCallbackEvent method.

The callback returns a value of type CBRType, typically cbrUSEDEF.

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

Warning Do not call SetTraceCallbackEvent 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.

+1 0

Post a comment