Articles   Members Online:
-Article/Tip Search
-News Group Search over 21 Million news group articles.
-Delphi/Pascal
-CBuilder/C++
-C#Builder/C#
-JBuilder/Java
-Kylix
Member Area
-Home
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Login/Logout
-Become a Member
-Why sign up!
-Newsletter
-Chat Online!
-Indexes NEW!!
Employment
-Build your resume
-Find a job
-Post a job
-Resume Search
Contacts
-Contacts
-Feedbacks
-Link to us
-Privacy/Disclaimer
Embarcadero
Visit Embarcadero
Embarcadero Community
JEDI
Links
Abstraction of Runtime Queries from Code Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
23-Jun-03
Category
Database-SQL
Language
Delphi 2.x
Views
142
User Rating
No Votes
# Votes
0
Replies
0
Publisher:
DSP, Administrator
Reference URL:
DKB
			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');


			
Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10

 

Advertisement
Share this page
Advertisement
Download from Google

Copyright © Mendozi Enterprises LLC