Delphi: log all the sql events in a file

Explaining the architecture of your program in UML diagram would help us understand better how your classes are composed and being structured, this will lead us to give you a solution.

Edit: You could perhaps use the template method pattern in this situation which will be the base class for any query and will execute the query, inside the execution method you could write to a log the sql query that is being carried.

My application works in the following way:

1. There is a global TMyConnection component which provides the connection with the database

2. At different places in the program I use the following:

Var
  MyLocalQuery : TMyQuery;
begin
...

    MyLocalQuery := TMyQuery.Create(nil);
    MyLocalQuery.Connection := MyConnection;  
   
    MyLocalQuery.Sql.text := MyParameter;   
   
   and here
   
   either MyLocalQuery.Execute; or MyLocalQuery.Open; depending if it's a DDL or query

Select allOpen in new window

You can create an abstract base class that handles the execution of the query by accepting a TMyQuery object, then create subclasses where each subclass handles a certain query derived from the abstract class. Inside the base class, within the invoke method right before executing the query you can log the raw query to a file or whatever you choose. This is an implementation of the template method pattern.

Thank you,

If I implement the class you wrote, do I need to replace these calls?

    MyLocalQuery := TMyQuery.Create(nil);     MyLocalQuery.Connection := MyConnection;           MyLocalQuery.Sql.text := MyParameter;  
MyLocalQuery.Open;

You need to move the connection and execution logic to a base class, the creation of TMyQuery should be taken care in subclasses. I'll write you a code sample in the morning when I'll wake up, at bed now.

Edit: Here's a skeleton that would get you started.

Unit2 is the base template class for handling execution and logging of the raw query (by text)

unit Unit2;

{$mode ObjFPC}{$H+}

interface

uses
  Classes, SysUtils;

type
  TBaseQueryExecutor = class abstract
  private
    _query: TMyQuery;
    _connection: TMyConnection;
  public
    constructor Create(query: TMyQuery);
  protected
    procedure Execute;
  end;

implementation

constructor TBaseQueryExecutor.Create(query: TMyQuery)
begin
  _connection := TMyConnection.Create();
  _query := query; // get the query from child class
  _query.Connection = _connection;
end;

procedure TBaseQueryExecutor.Execute()
begin
  // log _query.Sql.Text to a file or whatever
  // query execution logic here
end;

end.

Select allOpen in new window

Unit3 is the subclass which encapsulates a specific query

unit Unit3;

{$mode ObjFPC}{$H+}

interface

uses
  Classes, SysUtils;

type
  TCustomQuery = class(TBaseQueryExecutor)
  public
    constructor Create();
  end;

implementation

constructor TCustomQuery.Create()
var
  _query: TMyQuery;
begin
  _query := TMyQuery.Create(nil);
  // set up all parameters needed

  inherited(_query);
end;

end.

Select allOpen in new window

Thank you Jonathan for the base code. Supposing I would implement it, my original snippet:

MyLocalQuery := TMyQuery.Create(nil);     MyLocalQuery.Connection := MyConnection;           MyLocalQuery.Sql.text := MyParameter;  
MyLocalQuery.Open;

Select allOpen in new window

Would change to this?

MyLocalQuery := TCustomQuery .Create(nil);     
MyLocalQuery.Connection := MyConnection;           
MyLocalQuery.Sql.text := MyParameter;  
MyLocalQuery.Open;

Select allOpen in new window

 Thank you!

No, the TCustomQuery is a skeleton name for a class that abstracts a single query. Inside the class's ctor you create and setup the TMyQuery object which is being passed up to the parent base class for logging and execution. The only circumstances you create an object from the child class is to call the Execute method, no need to feed it data. It's all being taken care of inside the class ctor, which hides this implementation detail from the external classes.

The connection object and setup is being done in the base abstract class, for abstraction too. So you won't have to repeat it again every time you create a subclass (DRY).

Thank you for the answer. I apologize in advance that I don't understand it completely but previously I never used abstract classes.
I understand the code you wrote about TBaseQueryExecutor  class and TCustomQuery class.

What I don't understand is that in many places my application contains lines as :

Var
 MyLocalQuery  : TMyQuery

Select allOpen in new window

After having developed your TBaseQueryExecutor  and TCustomQuery  classes at my site, how should I change the existing TMyQuery declarations? Those different units they "wouldn't even know" of the existence of TCustomQuery class.

I thought first that I would name TCustomQuery exacly like the original: TMyQuery, and if I would put this unit last in the Uses declaration, that would automatically override the original TMyQuery, or am I wrong?

I am sorry for this dumb question.

Hi,

Put a try ... finally clause around the Query execution and in the finally part you put a logging code that writes the query to a file.

Regards,
    Tomas Helgi