Author: Dave Murray
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 codeing queries whose
SQL statements are set at runtime.
Answer:
In article "Auxiliary TQuery used with queries built at run time", Fernando Martins
suggested a way to avoid code replication when using TQuery objects with SQL
statements set at runtime. Here I show how to take this a step further and remove
the SQL from your code so that the queries can be changed without recompiling your
program.
My queries are stored within 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. My
inifile has the following syntax:
[QUERIES]
1=SELECT CAPITAL FROM COUNTRY WHERE NAME = 'Argentina'
2=SELECT NAME FROM COUNTRY WHERE CONTINENT = 'South America'
To perform the query call the ExecuteQuery procedure which in turn will call the
GetQuery function. ExecuteQuery must be passed the following paramaters:
myQuery : TQuery - TQuery component used to perform query
queryID : integer - ID number in inifile for the SQL satement
myDB : string - optional Database Name
ExecuteQuery(qryRuntime, 1, );
ExecuteQuery(qryRuntime, 2, 'DBDEMOS');
1
2 //Now for the code:
3
4 uses IniFiles;
5
6 const
7 queryFileName = 'queries.ini';
8
9 procedure ExecuteQuery(myQuery: TQuery; const queryID: integer; const myDB: string =
10 '');
11 {performs query getting SQL statement at runtime from inifile}
12 begin
13 if not (myDB = '') then
14 myQuery.DatabaseName := myDB;
15 try
16 myQuery.Close;
17 myQuery.SQL.Clear;
18 myQuery.SQL.Add(GetQuery(queryID));
19 myQuery.Open;
20 except
21 on E: Exception do
22 MessageDlg(E.message, mtError, [mbYes], 0);
23 end; {try..except}
24 end; {procedure ExecuteQuery}
25
26 function GetQuery(const qID: integer): string;
27 {reads SQL statement from inifile}
28 var
29 DirPath: string;
30 queryIni: TIniFile;
31 begin
32 DirPath := ExtractFilePath(ParamStr(0));
33 queryIni := TIniFile.Create(DirPath + queryFileName);
34 try
35 if not (queryIni.ValueExists('QUERIES', IntToStr(qID))) then
36 raise Exception.Create('ERROR: Query ID not found in file!')
37 else
38 result := queryIni.ReadString('QUERIES', IntToStr(qID), '');
39 finally
40 queryIni.Free;
41 end; {try..finally}
42 end; {function GetQuery}
Finally, 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:
43
44 ExecuteQuery(qryRuntime, GET_CAPITAL, );
45 ExecuteQuery(qryRuntime, GET_COUNTRIES, 'DBDEMOS');
|
