Page 1 of 1
TUniSQLMonitor and duration of an sql statement
Posted: Thu 25 Oct 2012 15:45
by NetSonic
Hi there,
in order to this posting
http://forums.devart.com/viewtopic.php?t=17330 I have changed my code to the example which you published there.
But I only get the current TickCountValue and not the duration. Is there still an easier way do monitor the sql statements duration or what can be wrong?
I am using UniDAC with Delphi 2010 Professional.
Code: Select all
TUniSqlMonitorEx = class(TUniSqlMonitor)
private
FExecuteDuration: Cardinal;
protected
procedure InternalSQLExecute(Obj: TObject; const SQL: string; Params: TDAParams; const Caption: string; BeforeEvent: boolean; var MessageID: Cardinal); override;
public
property ExecuteDuration: Cardinal read FExecuteDuration;
end;
Code: Select all
procedure TUniSqlMonitorEx.InternalSQLExecute(Obj: TObject;
const SQL: string; Params: TDAParams; const Caption: string;
BeforeEvent: boolean; var MessageID: Cardinal);
begin
if BeforeEvent then
FExecuteDuration := GetTickCount;
inherited;
if not BeforeEvent then
FExecuteDuration := (GetTickCount - FExecuteDuration);
end;
On FormCreate:
Code: Select all
USQLMonitor := TUniSqlMonitorEx.Create(Application.Owner);
USQLMonitor.OnSQL := USQLMonitorSQL;
USQLMonitorSQL-Procedure:
Code: Select all
WriteLog(0, FloatToStr(USQLMonitor.ExecuteDuration), Text);
Greetings from Germany,
NetSonic
Re: TUniSQLMonitor and duration of an sql statement
Posted: Fri 26 Oct 2012 08:53
by AlexP
Hello,
Since the FOnSQLEvent event is called in the basic class during the calling of
, the query execute duration variable is not computed yet, the FExecuteDuration variable is set to GetTickCount. Therefore you should call the FOnSQLEvent method again after the FExecuteDuration variable assignment in your descendant class:
Code: Select all
FExecuteDuration := (GetTickCount - FExecuteDuration);
In addition, to avoid double logging, you should also add a flag variable into your class, that will response for the event handling.
There is the full module code demonstrating the displaying of the query execute duration
Code: Select all
type
TUniSqlMonitorEx = class(TUniSqlMonitor)
private
FExecuteDuration: Cardinal;
FFinalEvent: Boolean;
protected
procedure InternalSQLExecute(Obj: TObject; const SQL: string; Params: TDAParams; const Caption: string; BeforeEvent: boolean; var MessageID: Cardinal); override;
public
property ExecuteDuration: Cardinal read FExecuteDuration;
Property IsFinalEvent: Boolean read FFinalEvent;
end;
TForm1 = class(TForm)
UniConnection1: TUniConnection;
UniQuery1: TUniQuery;
OracleUniProvider1: TOracleUniProvider;
procedure FormCreate(Sender: TObject);
private
UniSQLMonitor: TUniSqlMonitorEx;
procedure onSQL(Sender: TObject; Text: String; Flag: TDATraceFlag);
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
{ TUniSqlMonitorEx }
procedure TUniSqlMonitorEx.InternalSQLExecute(Obj: TObject;
const SQL: string; Params: TDAParams; const Caption: string;
BeforeEvent: boolean; var MessageID: Cardinal);
begin
if BeforeEvent then
begin
FFinalEvent := False;
FExecuteDuration := GetTickCount;
end;
inherited;
if not BeforeEvent then
begin
FFinalEvent := True;
FExecuteDuration := (GetTickCount - FExecuteDuration);
FOnSQLEvent(Obj, SQL, tfQExecute);
end;
end;
procedure TForm1.FormCreate(Sender: TObject);
begin
UniSQLMonitor := TUniSqlMonitorEx.Create(Application.Owner);
UniSQLMonitor.OnSQL := Self.onSQL;
UniQuery1.Execute;
end;
procedure TForm1.onSQL(Sender: TObject; Text: String; Flag: TDATraceFlag);
begin
if (Sender is TUniQuery) and (Flag = tfQExecute) and UniSQLMonitor.IsFinalEvent then
ShowMessage(FloatToStr(UniSQLMonitor.ExecuteDuration / 100));
end;
Re: TUniSQLMonitor and duration of an sql statement
Posted: Fri 26 Oct 2012 09:39
by NetSonic
Hello,
thanks for your reply. I've tried your posted solution and it works well for me.
But one more Question: How can I check if the sql statement was successfull or failed? ATM I don't know how I have to use the "Flag"-Value, which will contain this info I think.
Thanks
NetSonic
Re: TUniSQLMonitor and duration of an sql statement
Posted: Fri 26 Oct 2012 10:15
by AlexP
Hello,
To intercept the error, you can use a Try...except block when calling the Exute method, or the OnError event handler in UniConnection
Re: TUniSQLMonitor and duration of an sql statement
Posted: Fri 26 Oct 2012 10:36
by NetSonic
Sorry,
but I think that's not what i want. I use the SQLMonitor to monitor the statements. Can SQLMonitor check, if the statement was successfull or not in the OnSQL-Procedure?
If not, then my question is done...
Re: TUniSQLMonitor and duration of an sql statement
Posted: Fri 26 Oct 2012 13:17
by AlexP
hello,
The OnSQL event displays errors occured during queries execution as well, you can remember the current query and display it and error text when an error occurs. For this, you can add a string variable to the TForm1 class and rewrite the OnSQL event in the following way:
Code: Select all
procedure TForm1.onSQL(Sender: TObject; Text: String; Flag: TDATraceFlag);
begin
if (Sender is TUniQuery) and (Flag = tfQExecute) then
begin
if UniSQLMonitor.IsFinalEvent then
ShowMessage(FloatToStr(UniSQLMonitor.ExecuteDuration / 100))
else
FCurrentSQL := Text;
end
else if (Flag = tfError) then
ShowMessage(FCurrentSQL + ', Error:' + Text);
end;
Re: TUniSQLMonitor and duration of an sql statement
Posted: Mon 29 Oct 2012 12:52
by NetSonic
Ok,
that's what i'm searching for. It work's perfect.
Thanks for your help.
Greetings
NetSonic