Mirror

Auxiliary TQuery used with queries built at run time (Views: 704)


Problem/Question/Abstract:

Auxiliary queries built at run time make you copy-paste a lot, replicating your code. Why not keep it to a minimum, making it easy to read and mantain ?

Answer:

Do you have an auxiliary TQuery on your form that you use to build dynamic queries, like

'SELECT Count(id) FROM Clients'

and a bit latter you use the same TQuery to

'SELECT Count(Phone_numbers) FROM Clients WHERE area = '1''

and latter on you use it again to

'SELECT Count(area) FROM Contacts'

and so on...

If you have an auxiliar TQuery to run all these queries, you probably have a lot of similar code replicated in your application to load the query string, prepare the query, run the query and finally close the query.
Since replication is not a good thing when it comes to maintenance, why not abstract the queries from the code so that you just have to pass the TQuery object, the query string and, optionally, the database, if you use different databases.

Here's sometinh I've been using for a while that creates that abstraction layer:

procedure Execute(Q: TQuery; S: string; DBName = '');
begin
  with Q do
  begin
    if DBName <> '' then
      DatabaseName := DBName;
    try
      Close;
    finally
      SQL.Clear;
    end;

    SQL.Add(S);
    try
      Prepare;
      while not (Prepared) do
        ;
      Open;
    finally
      ;
    end;
  end;
end;

Using this procedure, you can reduce the amount of code and maintenance effort to a minimum, since you can prepare and open the queries just by using:

Execute(MyTQuery, 'SELECT Count(id) FROM Clients');

Execute(MyTQuery, 'SELECT Count(Phone_numbers) FROM Clients WHERE area = ' 1 '');

Execute(MyTQuery, 'SELECT Count(area) FROM Contacts', 'Contacts_database');

Execute(MyTQuery, 'SELECT Count(ZIP) FROM Zip_Codes', 'Address_database');

Execute(MyTQuery, 'SELECT Names FROM Vip_Clients', 'clients_database');

After calling the Execute procedure, you are able to read the result from the TQuery as usual.
This procedure simplifies the process of checking if the object is opened, close it if necessary, prepare the query for execution, release the resources to other processes while not ready and finally run the query.

<< Back to main page