Author: Yoav Abrahami
Having problems with SQL monitor? Need better monitoring? How about multi-threaded
monitoring? SQL monitor infrastructure provides this and more.
Answer:
Introduction
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
above.
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;
9
10 //The sqlTraceCallBack is a function defined in Delphi. It can look like //this:
11
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);
20
21 // S holds the trace text!
22
23 Result := cbrUSEDEF;
24 end;
25
26 //Stopping the trace
27
28 //In order to stop the trace, all you need to do is
29
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
like:
32 var
33 ActivationLock: TCriticalSection;
34
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;
52
53 //We need the same structure when we release the trace.
54
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.
Example
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.
Code
71 unit InternalSQLMonitor_thread;
72
73 interface
74
75 uses
76 Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
77 dbTables, bde, syncobjs;
78
79 const
80 cDefaultTraceOptions = [tfQPrepare, tfQExecute, tfError, tfStmt, tfDataIn];
81
82 type
83 TSQLTraceEvent = procedure(Sender: TObject; const SQLTrace: string) of object;
84
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;
120
121 procedure register;
122
123 implementation
124
125 var
126 ActivationLock: TCriticalSection;
127
128 procedure register;
129 begin
130 RegisterComponents('Samples', [TInternalSQLMonitor]);
131 end;
132
133 { TInternalSQLMonitor }
134
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;
142
143 procedure TInternalSQLMonitor.Close;
144 begin
145 SetActive(False);
146 end;
147
148 constructor TInternalSQLMonitor.Create(AOwner: TComponent);
149 begin
150 inherited;
151 TraceOptions := cDefaultTraceOptions;
152 end;
153
154 destructor TInternalSQLMonitor.Destroy;
155 begin
156 inherited;
157 SetActive(False);
158 end;
159
160 procedure TInternalSQLMonitor.Open;
161 begin
162 SetActive(True);
163 end;
164
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'
179
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;
211
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;
225
226 procedure TInternalSQLMonitor.SetOnSQLTraceEvent(
227 const Value: TSQLTraceEvent);
228 begin
229 FOnSQLTraceEvent := Value;
230 end;
231
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;
247
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;
257
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;
275
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;
285
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;
298
299 initialization
300
301 finalization
302 if ActivationLock <> nil then
303 FreeAndNil(ActivationLock);
304
305 end.
|
