Home

News
News Archive

Articles
Abstracting Queries

Forums / Groups

Mozilla-Delphi Project

Pascal Newsletter

Software

Links

Search

Vote For Us:
Irongut's Delphi Pages
Pascal Newsletter

Contact Details

Legal Stuff

Programming methodology books such as 'The Pragmatic Programmer' by Andrew Hunt and David Thomas teach the principles of decoupling, abstraction and non-repetition. This article shows how to achieve some of these goals when coding database queries. By moving SQL statements from your code, your queries can be changed without recompiling your program.

This article first appeared as #2935 on Delphi3000.com and was based on #2887 by Fernando Martins. This new version has been updated to allow the use of parameterised queries. The code uses a TQuery component but can easily be changed to use IBQuery, TSQLQuery or TADOQuery components.

We will store our SQL statements in an inifile in the program's directory. This provides us with a simple file format which is easy to use both in and out of Delphi. This inifile has the following syntax:

[QUERIES]
1=SELECT NAME, CAPITAL FROM COUNTRY WHERE NAME = 'Argentina'
2=SELECT * FROM COUNTRY WHERE NAME = :NAME
3=SELECT * FROM COUNTRY WHERE CONTINENT = :CONTINENT
4=SELECT * FROM COUNTRY WHERE POPULATION = :POPULATION


The code in AbstractQuery.pas contains two methods. They are written such that you can even change the SQL statements while an application is running. The function GetQuery is used behind the scenes to read the inifile. If there is an error reading the inifile it raises an exception. We do not call this function directly.

To perform the query we call the ExecuteQuery procedure which must be passed the following parameters:

eg.
ExecuteQuery(qryRuntime, 1, );
ExecuteQuery(qryRuntime, 3, VarArrayOf('South America'), 'DBDEMOS');

ExecuteQuery ensures that the TQuery passed will automatically create any required TParams by setting ParamCheck to true. Then it sets DatabaseName if myDB is present. Next it sets the SQL statement using the GetQuery function. If parameters are present in both the procedure call and the SQL it iterates through them setting their values. When running a parameterless query, pass qParams as length 0. Finally, it performs the query. If an exception occurs during ExecuteQuery it shows a dialog containing the exception's message.

To avoid having to look up long lists of query IDs when programming, incorporate them into a unit of constant values so that you can use code like the following:

ExecuteQuery(qryRuntime, GET_CAPITAL, );
ExecuteQuery(qryRuntime, GET_COUNTRIES, VarArrayOf(edtContinent.Text), 'DBDEMOS');


The Code

unit AbstractQuery;
{Functions from "Abstracting Runtime Queries from Code" article on Irongut's Delphi Pages
 Written by Dave Murray, June 2002. Irongut's Delphi Pages : http://www.paranoia.clara.net
 (C) 2002 - 2003 Conspiracy Software <conspiracysoftware@hotmail.com>}
interface

uses SysUtils, Dialogs, DB, DBTables;

procedure ExecuteQuery(myQuery : TQuery; const qID : integer;
   const qParams: array of variant; const myDB : string = '');
function GetQuery(const qID : integer) : string;

implementation

uses IniFiles;

const
  queryFileName = 'queries.ini';

procedure ExecuteQuery(myQuery : TQuery; const qID : integer;
   const qParams: array of variant; const myDB : string = '');
{performs query getting SQL statement at runtime from inifile
 params: myQuery : TQuery  - TQuery instance used to perform query
         qID : integer - ID number in inifile for the SQL satement
         qParams : array of variant - parameters for query
         myDB : string - optional Database Name
 on exception: shows a dialog with the exception's message}
var
  i : integer;
begin
  i := 0;
    try
      myQuery.Close;
      myQuery.SQL.Clear;
      myQuery.ParamCheck := true; {ensure params created automatically}
      if not(myDB = '') then myQuery.DatabaseName := myDB; {optional database name}
      myQuery.SQL.Add(GetQuery(qID)); {set query statement}
      {set params if qParams not empty and required}
      if not(Length(qParams) = 0) and (myQuery.ParamCount > 0) then
        while (i < myQuery.ParamCount)) do begin
          myQuery.Params[i].Value := qParams[i];
          inc(i);
          end; {while i < ..}
      myQuery.Open; {run query}
    except
      on E : Exception do MessageDlg(E.message, mtError, [mbOk], 0);
      end; {try..except}
end; {procedure ExecuteQuery}

function GetQuery(const qID : integer) : string;
{returns SQL statement read from inifile}
{raises an exception if the statement doesn't exist}
var
  DirPath : string;
  queryIni : TIniFile;
begin
  DirPath := ExtractFilePath(ParamStr(0));
  queryIni := TIniFile.Create(DirPath + queryFileName);
  try
    if not(queryIni.ValueExists('QUERIES', IntToStr(qID))) then
      raise Exception.Create('ERROR: Query ID not found in file!')
    else result := queryIni.ReadString('QUERIES', IntToStr(qID), '');
  finally
    queryIni.Free;
    end; {try..finally}
end; {function GetQuery}

end.

	Download this article in eBook format. You can download a free eBook reader for PalmOS, PocketPC, MacOS, Symbian or Windows.
	Download the code with a Demo application.

Refer this page to a friend!