When it comes to database manipulation, there are few tools as convenient and easy to use as Delphi DataSets. Instanciate any TDataset, run a sql query, call the open method and bingo! We have a copy of the records (and a multitude of metadata) in memory, ready to be manipulated.

You may be very familiar with some variation of this code:

procedure Foo;
var
  qry: TSqlQuery;
begin
  qry := TSqlQuery.Create;
  try
    qry.CommandText := 'select some fields from some table';
    qry.Open;
    while not qry.Eof do
    begin
      { some proccessing oncurrent row  }
      qry.Next; 
   end;
   finally
     qry.Close;
     FreeAndNil(qry);
   end;
end;

Simple, isn't?

Yes, but... Well, image maintain a system where there are thousands of functions exactly the same as this, spreaded over hundreds or thousands of units and you will start to feel chills.

There are someting very wrong here!

 - Dont' repeat yourself, don't repeat yourself, don't repeat yourself - I repeated in my mind every time I saw a copy of this code.

Usually, in a case like this, the answer is quite simple: create a decendant class of TSqlQuery and implement a method that encapsulates the query creation, opening, iterating, closing, and releasing logic. But to follow this to path, you'l need to change all occurrences of TSqlQuery for the new created class and that would mean more boring, repetitive, and error-prone work.

What brings us to the title topic.

Class Interceptors

Interceptor Design Pattern

The syntax of object pascal (or delphi language, as it renamed in later versions) allows you to declare classes with the same name if they are in different units (working as a kind of namespacing). When this occurs, the second class will intercept the first and will extend it, in the scope of its unit, combining the two classes into one in compilte time.

With this in mind, solving the problem of repeated code presented above becomes easier. Just intercept the TSqlQuery class on the units you are working on and extend them with a smarter function to iterate over your data.

Look at the following code:

unit SQLExprInterceptor;

interface

uses
  SqlExpr;

type
  TSqlQueryRowCallback = reference to procedure;
  TSqlQuery = class( SqlExpr.TSqlQuery )
  public
    class procedure ForEachRow( const sql : string; callback: TSqlQueryRowCallback ); static;
  end;

implementation

procedure TSqlQuery.ForEachRow(const sql: string; callback: TSqlQueryRowCallback);
var
  qry: TSqlQuery;
begin
  qry:= TSqlQuery.Create;
  qry.CommandText := sql;
  try
    qry.Open;
    while not qry.Eof do
    begin
      callback;
      qry.Next;
    end;
  finally
    qry.Close;
    FreeAndNil(qry);
  end;
end;

end.

The TSqlQuery was declared as a reference for the original class (which is inside the unit SqlExpr)..

TSqlQuery = class( SqlExpr.TSqlQuery )

..and we extend its declaration with a method that receives an sql string and a reference to our callback, whose signature is a simple method, without parameters.

TSqlQueryRowCallback = reference to procedure;

Now, with the help of anonymous methods, we can eliminate all the repetitive code from the beginning of the text and replace it with the code below:

procedure Foo;
begin
   TSqlQuery.ForEachRow('select some fields from some table', procedure 
       begin
          //proccess the current row...
       end);   
end;

Now it's much better! Short and clean.

Note that the only requirement for class interceptors to work is that the reference to the original class appears before the unit containing the extensions for the class.

Thank you very much for reading, and see you in the next post.

Fabiano Salles.