TUniSQLMonitor and duration of an sql statement

[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

[AlexP]

Hello,

Since the FOnSQLEvent event is called in the basic class during the calling of

Code: Select all

inherited InternalSQLExecute... 

, 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;

[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

[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

[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...

[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;

[NetSonic]

Ok,

that's what i'm searching for. It work's perfect.
Thanks for your help.

Greetings
NetSonic