The Road to Delphi

Delphi – Free Pascal – Oxygene

Build your own profiler using ADO

17 Comments

You can construct your own SQL profiler for yours apps wich use ADO, the TAdoConnection Object has two events TADOConnection.OnWillExecute and TADOConnection.OnExecuteComplete to accomplish this task.

TWillExecuteEvent = procedure (const Connection: TADOConnection; var CommandText: WideString; var CursorType: TCursorType; var LockType: TADOLockType; var CommandType: TCommandType; var ExecuteOptions: TExecuteOptions; var EventStatus: TEventStatus; const Command: _Command; const Recordset: _Recordset) of object;
TExecuteCompleteEvent = procedure (const Connection: TADOConnection; RecordsAffected: Integer; const Error: Error; var EventStatus: TEventStatus; const Command: _Command; const Recordset: _Recordset) of object;

1) Create a New Form with a TListview and a TMemo (in this example i am use a TSynEdit for format the SQL Command)

2) Create a public procedure in your form called AddLog

procedure AddLog(const Command,CommandType,Status,CursorType,LockType:String;RecordsAffected:Integer);

and implement the procedure like this

procedure TFrmLogSql.AddLog(const Command,CommandType,Status,CursorType,LockType:String;RecordsAffected:Integer);
var
  item : TListItem;
begin
    ListViewSQL.Items.BeginUpdate;
  try
    item:=ListViewSQL.Items.Add;
    item.Caption:=FormatDateTime('DD/MM/YYYY HH:NN:SS.ZZZ',Now);
    item.SubItems.Add(CommandType);
    item.SubItems.Add(Command);
    item.SubItems.Add(Status);
    item.SubItems.Add(IntToStr(RecordsAffected));
    item.SubItems.Add(CursorType);
    item.SubItems.Add(LockType);
  finally
    ListViewSQL.Items.EndUpdate;
  end;
  ListViewSQL.Items.Item[ListViewSQL.Items.Count-1].MakeVisible(false); //Scroll to the last line
end;

3) Assign the OnChange Event of the TListView

procedure TFrmLogSql.ListViewSQLChange(Sender: TObject; Item: TListItem;
  Change: TItemChange);
begin
    if ListViewSQL.Selected<>nil then
    SynEdit1.Lines.Text:=ListViewSQL.Selected.SubItems[1];
end;

4) Assign the events OnWillExecute and OnExecuteComplete for you AdoConnection object.

uses
  TypInfo;

procedure TDataModule1.ADOConnection1WillExecute(
  Connection: TADOConnection; var CommandText: WideString;
  var CursorType: TCursorType; var LockType: TADOLockType;
  var CommandType: TCommandType; var ExecuteOptions: TExecuteOptions;
  var EventStatus: TEventStatus; const Command: _Command;
  const Recordset: _Recordset);
begin
   FrmLogSql.AddLog(
   CommandText,
   'Before '+GetEnumName(TypeInfo(TCommandType),Integer(CommandType)),
   GetEnumName(TypeInfo(TEventStatus),Integer(EventStatus)),
   GetEnumName(TypeInfo(TCursorType),Integer(CursorType)),
   GetEnumName(TypeInfo(TADOLockType),Integer(LockType)),
   0);
end;

procedure TDataModule1.ADOConnection1ExecuteComplete(
  Connection: TADOConnection; RecordsAffected: Integer; const Error: ADODB.Error;
  var EventStatus: TEventStatus; const Command: _Command;
  const Recordset: _Recordset);
begin
  FrmLogSql.AddLog(
  Command.CommandText,
  'After '+GetEnumName(TypeInfo(TCommandType),Integer(Command.CommandType)),
  GetEnumName(TypeInfo(TEventStatus),Integer(EventStatus)),
  GetEnumName(TypeInfo(TCursorType),Integer(Recordset.CursorType)),
  GetEnumName(TypeInfo(TADOLockType),Integer(Recordset.LockType)),
  RecordsAffected);
end;

5) and the final result

Author: Rodrigo

Just another Delphi guy.

17 thoughts on “Build your own profiler using ADO

  1. Hello.
    More interesenting article.
    I am doing some tests on two small applications that I am developing.

    Thanks.

  2. Hello Rodrigo.

    ¿Pues colocar el código del proyecto?
    Can you upload the project source code?

    Gracias/thanks.

  3. This is a quite tricky idea! Thanks for it.
    I noticed in my app, which retrievs its data from a MS SQL Server (2000) that parameters in SQL commands are shown as question marks. Is there a way to show them in a more informative way?
    Regards,
    Klaus

    • Yes is possible get the info of parameters, you must check the Command.Parameters property

      procedure TDataModule1.ADOConnection1WillExecute(
        Connection: TADOConnection; var CommandText: WideString;
        var CursorType: TCursorType; var LockType: TADOLockType;
        var CommandType: TCommandType; var ExecuteOptions: TExecuteOptions;
        var EventStatus: TEventStatus; const Command: _Command;
        const Recordset: _Recordset);
      var
       i  : integer;
      begin
         FrmLogSql.AddLog(
         CommandText,
         'Before '+GetEnumName(TypeInfo(TCommandType),Integer(CommandType)),
         GetEnumName(TypeInfo(TEventStatus),Integer(EventStatus)),
         GetEnumName(TypeInfo(TCursorType),Integer(CursorType)),
         GetEnumName(TypeInfo(TADOLockType),Integer(LockType)),
         0);
      
         if Command.Parameters.Count>0 then
         for i:=0 to  Command.Parameters.Count-1 do
          begin
            //Command.Parameters.Item[i].Name
            //Command.Parameters.Item[i].Value
          end;  
      
      
      end;
      
      
      • Thanks Rdrigo for your answer!
        The SQL statement shown whith question marks instead of the paranmters is not verry helpful for me.
        And I don’t know any way to get that signs replaced by the actual parameters or their values.
        Regards, Klaus

  4. Edelklaus, I showed you how to obtain the values of the params, not display them, you must process the data and show it.

    • Hi Rodrigo, I think there is a problem with Command.Parameters. Everytime I try to obtain the values in a Query with Parameters I get an Access Violation. I have the same problems if I use RecordSet to obtain the data.

      For example in a query with Parameters:

      for i:=0 to Command.Parameters.Count-1 do
      begin
      Showmessage(‘Name: ‘ + Command.Parameters.Item[i].Name);
      Showmessage(‘Value: ‘ + Command.Parameters.Item[i].Value);
      end;

      I never get a Showmessage (even when the compiler enter in the for).

      Using Delphi 2006.

      Thanks

  5. Could it be possible to view UPDATE and DELETE statements with this profiler? if not, do you know any other way to achive that. Thanks, nice tool!.

  6. hey rodrigo, i have a question about your code, the first lines that presents at the beginning of the article should be added in the application?, because my app doesnt works like yours did i’m missing something?

  7. one simple question, this logger only display the queries that you execute within the same app, i thought that can listen to the executed sentences for the selected database in the adoconnection source :(

  8. This doesn’t seem to work if you loop through a TADOQuery dataset and change values, like this:

    Q : TAsoQuery;

    Q.SQL := ‘Select … from …’;
    Q.Open;
    While not Q.EoF do
    Begin
    Q.Edit;
    Q.Fields[0].AsInteger := 1;
    Q.Post;
    Q.Next
    End;

    In fact, you may have made 1000 updates but the TExecuteCompleteEvent doesn’t fire once. Any idea to get around this?

  9. Hi

    We have been using a tool similar to this for ages. It’s really good to find bottlenecks in your code as you can log the Execute and ExecuteComplete together and calculate the time taken between the two…

    I’d like to know if it is possible to also log an update like ‘Walt’ has asked above.

    Thanks

  10. Hi, Rodrigo, thank you.

  11. Pingback: SQL mitloggen - Delphi-PRAXiS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s