Articles   Members Online:
-Article/Tip Search
-News Group Search over 21 Million news group articles.
Member Area
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Become a Member
-Why sign up!
-Chat Online!
-Indexes NEW!!
-Build your resume
-Find a job
-Post a job
-Resume Search
-Link to us
Visit Embarcadero
Embarcadero Community
How to create a better SQL Monitor Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
SQL monitor magic 19-Aug-03
Delphi 5.x
User Rating
No Votes
# Votes
DSP, Administrator
Reference URL:
			Author: Yoav Abrahami 

Having problems with SQL monitor? Need better monitoring? How about multi-threaded 
monitoring? SQL monitor infrastructure provides this and more.



SQL monitor is one of the most useful tools in Delphi, when you develop a database 
application. It allows the programmer to debug the connection between an 
application and a Database. It is very useful when you have automatic SQL 
generation. The tool provides the time it takes for each SQL to run, so you can use 
it to profile you’re DB side of the application. 

SQL monitor paints a nice picture. However, SQL monitor has some problems: 

You must start SQL monitor before you start the client application. This is a 
problem with applications that need to run non-stop for long durations. 

The tool is not designed to work with multithreaded applications. It can trace only 
one session at a time, and that session is the last one opened. You cannot select 
what thread to monitor, nor can you monitor more then one thread. 

Some applications use an automatic trouble tickets (TT) in case of errors. When you 
have a DB related problem, it is useful to add the SQL trace to the TT. However, 
the SQL monitor is an external tool, and does not allow this kind of trace. 

The SQL monitor tool uses an infrastructure provided by Delphi and the BDE to trace 
SQLs. We can connect to this infrastructure without the SQL monitor tool, in order 
to get an SQL trace internally to the application, with out any of the problems 

How the SQL trace works 

We need to tell the BDE that we want an SQL trace. We do that by registering a 
callback function with the BDE (Callback is the equivalent of an event in non 
Object Oriented systems). The BDE provides SQL trace by setting a memory buffer 
with some text, and then notifying us with a callback. The callback function gets 
one parameter – a pointer to a TtraceDesc type (defined in the BDE unit). In that 
structure is the text we see in the SQL monitor tool. 

Setting a BDE SQL Trace 

In order to set a trace on the BDE, we need to register a BDE callback using the 
DbiRegisterCallback function in the DBE unit. The unit takes a number of parameters 
that sound like gibberish when you look at them in the online help. The VCL 
provides a nice wrapper for this call with the TBDECallback Class in the DBTables 
unit. This class takes a number of parameters in its constructor, and sets the 
appropriate callback. When we free an object of this class, the callback is freed. 

To use the TBDECallback object, we need to do a number of things: 

The TBDECallback object can register all kinds of DBE callbacks. In order to trace 
SQL, we need a cbTRACE callback (the value of the CBType parameter in Create). 

We need to create a callback function with the following prototype: 

function(CBInfo: Pointer): CBRType of object;

We need to create a memory buffer of smTraceBufSize size. (smTraceBufSize is a 
constant defined in the DBTables unit). 

The code to set a trace can look like this: 

1   var
2     FSMBuffer: PTraceDesc;
3     TraceCallback: TBDECallback;
4   begin
5     GetMem(FSMBuffer, smTraceBufSize);
6     TraceCallback := TBDECallback.Create(Self, nil, cbTRACE,
7       FSMBuffer, smTraceBufSize, SqlTraceCallBack, False);
8   end;
10  //The sqlTraceCallBack is a function defined in Delphi. It can look like //this: 
12  function TInternalSQLMonitor.SqlTraceCallBack(CBInfo: Pointer): CBRType;
13  var
14    Data: Pointer;
15    S: string;
16  begin
17    Data := @PTraceDesc(CBInfo).pszTrace;
18    SetLength(S, StrLen(Data));
19    StrCopy(PChar(S), Data);
21    //  S holds the trace text!
23    Result := cbrUSEDEF;
24  end;
26  //Stopping the trace 
28  //In order to stop the trace, all you need to do is 
30  FreeMem(FSMBuffer, smTraceBufSize);
31  TraceCallback.Free;

And now for the advanced staff… 

In the last section I explained how to setup an SQL trace. However, in the start of 
this article, a complained that the SQL monitor tool does not provide good support 
for multiple sessions and threads. In fact, the code in the last section has 
exactly the same problems. We need to overcome those problems. 

If you look at the code in the last section, you will see that I do not specify 
what session and what database to trace. I also do not setup what are the trace 
options (as we have in the SQL monitor options window). 

The problem is that we are opening a trace on the default session, default database 
and using the default settings (from the BDE driver). 

When we run the above code, it registers a trace with the BDE current session. The 
current session is accessed via the sessions.CurrentSession global object property. 
By changing the current session, we can register a trace for any session we want. 
The callback function is registered per session, allowing us multi-threading trace. 
Don’t confuse the default session with the current session. The default session is 
one that is automatically opened by Delphi, and cannot be changed. The current 
session is current from the BDE point of view. It is the session that BDE functions 
work with. Because the current session is a global definition, we need some thread 
locking  mechanism when we set a trace. The code for setting a trace can now look 

32  var
33    ActivationLock: TCriticalSection;
35  procedure SetTrace;
36  begin
37    ActivationLock.Enter;
38    try
39      // set the current session to be the session we want to trace.
40      SaveCurrentSession := Sessions.CurrentSession;
41      Sessions.CurrentSession := Session;
42      // set the trace.
43      GetMem(FSMBuffer, smTraceBufSize);
44      TraceCallback := TBDECallback.Create(Self, nil, cbTRACE,
45        FSMBuffer, smTraceBufSize, SqlTraceCallBack, True);
46      // restore the current session to the saved session.
47      Sessions.CurrentSession := SaveCurrentSession;
48    finally
49      ActivationLock.Leave;
50    end;
51  end;
53  //We need the same structure when we release the trace. 
55  procedure CloseTrace;
56  begin
57    ActivationLock.Enter;
58    try
59      // set the current session to be the session we want to trace.
60      SaveCurrentSession := Sessions.CurrentSession;
61      Sessions.CurrentSession := Session;
62      // close the trace.
63      FreeMem(FSMBuffer, smTraceBufSize);
64      TraceCallback.Free;
65      // restore the current session to the saved session.
66      Sessions.CurrentSession := SaveCurrentSession;
67    finally
68      ActivationLock.Leave;
69    end;
70  end;

What about the trace options? 

The trace options come from the driver configuration of the BDE. However, you can 
override them from Delphi by setting the TraceFlags property of a Tdatabase 
component. There is one fine point to notice. You must set the value of TraceFlags 
AFTER you open the database. For some reason, if you set the options before you 
open the database, this has no affect. 


The following example is a component providing SQL trace for one session and one 
database. The component fires a Delphi event for each SQL trace event, with the 
trace text as a parameter. In order to use this component, all you need to do is 
attach it to a Tsession and Tdatabase, set the trace options, set the event and 
activate the trace. 
Note that you can only activate a trace on an open database. 


71  unit InternalSQLMonitor_thread;
73  interface
75  uses
76    Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
77    dbTables, bde, syncobjs;
79  const
80    cDefaultTraceOptions = [tfQPrepare, tfQExecute, tfError, tfStmt, tfDataIn];
82  type
83    TSQLTraceEvent = procedure(Sender: TObject; const SQLTrace: string) of object;
85    TInternalSQLMonitor = class(TComponent)
86    private
87      FActive: Boolean;
88      FOnSQLTraceEvent: TSQLTraceEvent;
89      FSMBuffer: PTraceDesc;
90      TraceCallback: TBDECallback;
91      FSession: TSession;
92      FDatabase: TDatabase;
93      FTraceOptions: TTraceFlags;
94      procedure ReplaceComponent(var Reference: TComponent; const Value: TComponent);
95      procedure SetActive(const Value: Boolean);
96      procedure SetOnSQLTraceEvent(const Value: TSQLTraceEvent);
97      procedure SetSession(const Value: TSession);
98      procedure SetDatabase(const Value: TDatabase);
99      function CanOpenTrace: Boolean;
100     procedure SetTraceOptions(const Value: TTraceFlags);
101   protected
102     function SqlTraceCallBack(CBInfo: Pointer): CBRType;
103     procedure Notification(AComponent: TComponent;
104       Operation: TOperation); override;
105   public
106     constructor Create(AOwner: TComponent); override;
107     destructor Destroy; override;
108     procedure Open;
109     procedure Close;
110   published
111     property OnSQLTraceEvent: TSQLTraceEvent read FOnSQLTraceEvent write
112       SetOnSQLTraceEvent;
113     property Active: Boolean read FActive write SetActive;
114     property Session: TSession read FSession write SetSession;
115     property Database: TDatabase read FDatabase write SetDatabase;
116     property TraceOptions: TTraceFlags read FTraceOptions write SetTraceOptions 
117 default
118       cDefaultTraceOptions;
119   end;
121 procedure register;
123 implementation
125 var
126   ActivationLock: TCriticalSection;
128 procedure register;
129 begin
130   RegisterComponents('Samples', [TInternalSQLMonitor]);
131 end;
133 { TInternalSQLMonitor }
135 function TInternalSQLMonitor.CanOpenTrace: Boolean;
136 begin
137   Result := (Session <> nil) and
138     (Session.Active) and
139     (Database <> nil) and
140     (Database.Connected);
141 end;
143 procedure TInternalSQLMonitor.Close;
144 begin
145   SetActive(False);
146 end;
148 constructor TInternalSQLMonitor.Create(AOwner: TComponent);
149 begin
150   inherited;
151   TraceOptions := cDefaultTraceOptions;
152 end;
154 destructor TInternalSQLMonitor.Destroy;
155 begin
156   inherited;
157   SetActive(False);
158 end;
160 procedure TInternalSQLMonitor.Open;
161 begin
162   SetActive(True);
163 end;
165 procedure TInternalSQLMonitor.SetActive(const Value: Boolean);
166 var
167   SaveCurrentSession: TSession;
168 begin
169   // create the critical section, if needed.
170   if ActivationLock = nil then
171     ActivationLock := TCriticalSection.Create;
172   if FActive <> Value then
173   begin
174     // check that all the preconditions needed to set a trace are met.
175     if (Value = True) and (not CanOpenTrace) then
176       raise
177         Exception.Create('Cannot open trace when the session or database are 
178 closed'
180     // prevent other threads from hampering. If other trace objects are opened
181     // at the same time, prevent them from changing the current session until
182     // we finish with it.
183     ActivationLock.Enter;
184     try
185       FActive := Value;
186       // set the current session to be the session we want to trace.
187       SaveCurrentSession := Sessions.CurrentSession;
188       Sessions.CurrentSession := Session;
189       if FActive then
190       begin
191         // set the trace.
192         GetMem(FSMBuffer, smTraceBufSize);
193         TraceCallback := TBDECallback.Create(Self, nil, cbTRACE,
194           FSMBuffer, smTraceBufSize, SqlTraceCallBack, True);
195         // Set the trace Flags to the database
196         FDatabase.TraceFlags := TraceOptions;
197       end
198       else
199       begin
200         // release the trace.
201         FreeMem(FSMBuffer, smTraceBufSize);
202         TraceCallback.Free;
203       end;
204       // restore the current session to the saved session.
205       Sessions.CurrentSession := SaveCurrentSession;
206     finally
207       ActivationLock.Leave;
208     end;
209   end;
210 end;
212 procedure TInternalSQLMonitor.SetDatabase(const Value: TDatabase);
213 begin
214   if FDatabase <> Value then
215   begin
216     if Active then
217       Active := False;
218     if Assigned(FDatabase) then
219       FDatabase.RemoveFreeNotification(Self);
220     FDatabase := Value;
221     if Assigned(FDatabase) then
222       FDatabase.FreeNotification(Self);
223   end;
224 end;
226 procedure TInternalSQLMonitor.SetOnSQLTraceEvent(
227   const Value: TSQLTraceEvent);
228 begin
229   FOnSQLTraceEvent := Value;
230 end;
232 procedure TInternalSQLMonitor.SetSession(const Value: TSession);
233 begin
234   if FSession <> Value then
235   begin
236     if Active then
237       Active := False;
238     if Assigned(FSession) then
239       FSession.RemoveFreeNotification(Self);
240     FSession := Value;
241     if Assigned(FSession) then
242       FSession.FreeNotification(Self);
243     if (FDatabase <> nil) and (FDatabase.Session <> FSession) then
244       FDatabase := nil;
245   end;
246 end;
248 procedure TInternalSQLMonitor.SetTraceOptions(const Value: TTraceFlags);
249 begin
250   if FTraceOptions <> Value then
251   begin
252     FTraceOptions := Value;
253     if Active then
254       FDatabase.TraceFlags := Value;
255   end;
256 end;
258 function TInternalSQLMonitor.SqlTraceCallBack(CBInfo: Pointer): CBRType;
259 var
260   Data: Pointer;
261   S: string;
262 begin
263   try
264     if Assigned(FOnSQLTraceEvent) then
265     begin
266       Data := @PTraceDesc(CBInfo).pszTrace;
267       SetLength(S, StrLen(Data));
268       StrCopy(PChar(S), Data);
269       FOnSQLTraceEvent(Self, S);
270     end;
271   except
272   end;
273   Result := cbrUSEDEF;
274 end;
276 procedure TInternalSQLMonitor.ReplaceComponent(var Reference: TComponent;
277   const Value: TComponent);
278 begin
279   if Assigned(Value) then
280     Reference.RemoveFreeNotification(Self);
281   Reference := Value;
282   if Assigned(Reference) then
283     Value.FreeNotification(Self);
284 end;
286 procedure TInternalSQLMonitor.Notification(AComponent: TComponent;
287   Operation: TOperation);
288 begin
289   inherited;
290   if Operation = opRemove then
291   begin
292     if (AComponent = FDatabase) then
293       Database := nil;
294     if (AComponent = FSession) then
295       Session := nil;
296   end;
297 end;
299 initialization
301 finalization
302   if ActivationLock <> nil then
303     FreeAndNil(ActivationLock);
305 end.

Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10


Share this page
Download from Google

Copyright © Mendozi Enterprises LLC