Title: Externalising your Queries
Question: Most of the work we do is multi-tier client/server stuff. Typically, the SQL code needed ends up inside the EXE, which is not always agood idea. Alternatives are to use stored procedures or views in your database of choice, but that's not always what you want to do.
Answer:
We have found an eloquent way of doing this is to store all queries in a tabel in the SQL database. This gives us a single point of reference for each query, which is most important in a multi-coder project. The EXE also has a form in it which allows editing and changing of queries directly from within the application. Obviously, this capability is carefully protected from the normal user.
We (Zayin Krige, my lead programmer) created a descendent of TQuery which adds properties allowing the easy accessing of SQL code. the new properties are:
QueriD: String identifying the SQL query
QuerySource: a DataSource containing all SQL queries (optional)
SourceTable: the tabel name in the database containing the SQL queries (optional)
The last two are mutually exclusive. If you use QuerySource, it is assumed that you have a query with datasource open containing all of the required queries in the app.
Alternatively, you can use SourceTable, in which case the SQL query text will be retreived automatically by the component.
I prefer the second, as I don't need any opne queries while working in the IDE.
Here's the source code for the component.
unit RPQuery;
interface
uses
Classes, SysUtils,
Db, DBTables
{$IFDEF D5}
, IBQuery
{$ENDIF}
;
type
TRPQuery = class(TQuery)
private
{ Private declarations }
HadText : Boolean;
procedure SetActive(Value: Boolean); overload;
function GetActive : Boolean; overload;
protected
{ Protected declarations }
TName : String;
QryID : String;
QSrc : TDataSource;
public
{ Public declarations }
Procedure LoadQry(name:string); overload;
Procedure LoadQry(DBName, QryTable, QryID : String); overload;
Procedure LoadQry(QryTable, QryID : String); overload;
Procedure LoadQry; overload;
Procedure Open; Overload;
Procedure Close; Overload;
published
{ Published declarations }
property QuerySource : TDataSource read QSrc write QSrc;
property SourceTable : String read TName write Tname;
property QueryID : String read QryID write QryID;
property Active : Boolean read GetActive write SetActive;
end;
{$IFDEF D5}
TRPIBQuery = class(TIBQuery)
private
{ Private declarations }
HadText : Boolean;
procedure SetActive(Value: Boolean); overload;
function GetActive : Boolean; overload;
protected
{ Protected declarations }
TName : String;
QryID : String;
QSrc : TDataSource;
public
{ Public declarations }
Procedure LoadQry; overload;
Procedure Open; Overload;
Procedure Close; Overload;
published
{ Published declarations }
property QuerySource : TDataSource read QSrc write QSrc;
property SourceTable : String read TName write Tname;
property QueryID : String read QryID write QryID;
property Active : Boolean read GetActive write SetActive;
end;
{$ENDIF}
procedure Register;
implementation
uses Dialogs;
Procedure TRPQuery.LoadQry(name:string);
var
path:string;
qry:TStringList;
begin
qry:=tstringList.create;
path:=ExtractFilePath(paramstr(0));
qry.clear;
qry.loadfromfile(path+'queries\'+name);
sql.Text:=qry.text;
qry.free;
end;
Procedure TRPQuery.LoadQry(DBName, QryTable, QryID : String);
var
Q : TQuery;
begin
Q := TQuery.Create(Nil);
Q.DatabaseName := DBName;
q.SQL.add('SELECT QueryText FROM '+QryTable+' WHERE QueryID = :QryID');
q.ParamByName('QryID').AsString := QryID;
q.open;
sql.Text := q.FieldByName('QueryText').AsString;
q.close;
q.free;
end;
Procedure TRPQuery.LoadQry(QryTable, QryID : String);
var
Q : TQuery;
begin
Q := TQuery.Create(Nil);
Q.DatabaseName := DataBaseName;
q.SQL.add('SELECT QueryText FROM '+QryTable+' WHERE QueryID = :QryID');
q.ParamByName('QryID').AsString := QryID;
q.open;
sql.Text := q.FieldByName('QueryText').AsString;
q.close;
q.free;
end;
Procedure TRPQuery.LoadQry;
var
Q : TQuery;
WasActive : Boolean;
begin
if QSrc = Nil then
begin
Q := TQuery.Create(Nil);
Q.DatabaseName := DatabaseName;
q.SQL.add('SELECT QueryText FROM '+TName+' WHERE QueryID = :QryID');
q.ParamByName('QryID').AsString := QryID;
try
q.open;
sql.Text := q.FieldByName('QueryText').AsString;
q.close;
except
raise;
end;
q.free;
end else
begin
if QSrc.DataSet = Nil then
begin
ShowMessage('Datasource has no dataset!');
exit;
end;
try
WasActive := QSrc.DataSet.Active;
if not QSrc.DataSet.Active then
QSrc.DataSet.Open;
if QSrc.DataSet.Locate('QueryID',QryID,[]) then
SQL.Text := QSrc.DataSet.FieldByName('QueryText').AsString;
if not WasActive then
QSrc.DataSet.Close;
except
raise;
end;
end;
end;
Procedure TRPQuery.SetActive(Value : Boolean);
begin
if Value = True then
begin
if (QryID '') then
begin
if (SQL.Text = '') then
begin
HadText := False;
LoadQry;
end else
HadText := True;
end else
begin
if (SQL.Text '') then
HadText := True;
end
end else
if not HadText then
SQL.Text := '';
try
inherited;
except
if not HadText then
SQL.Text := '';
raise;
end;
end;
function TRPQuery.GetActive : Boolean;
begin
Result := not (State in [dsInactive]);
end;
procedure TRPQuery.Open;
begin
if not Active then
Active := True;
end;
procedure TRPQuery.Close;
begin
if Active then
Active := False;
end;
{$IFDEF D5}
Procedure TRPIBQuery.LoadQry;
var
Q : TIBQuery;
begin
if QSrc = Nil then
begin
Q := TIBQuery.Create(Nil);
Q.Database := Database;
Q.Transaction := Transaction;
q.SQL.add('SELECT QueryText FROM '+TName+' WHERE QueryID = :QryID');
q.ParamByName('QryID').AsString := QryID;
try
q.open;
sql.Text := q.FieldByName('QueryText').AsString;
q.close;
except
raise;
end;
q.free;
end else
begin
if QSrc.DataSet = Nil then
begin
ShowMessage('Datasource has no dataset!');
exit;
end;
try
if QSrc.DataSet.Locate('QueryID',QryID,[]) then
SQL.Text := QSrc.DataSet.FieldByName('QueryText').AsString;
except
raise;
end;
end;
end;
Procedure TRPIBQuery.SetActive(Value : Boolean);
begin
if Value = True then
begin
if (QryID '') then
begin
if (SQL.Text = '') then
begin
LoadQry;
HadText := False;
end else
HadText := True;
end else
begin
if (SQL.Text '') then
HadText := True;
end
end else
if not HadText then
SQL.Text := '';
try
inherited;
except
if not HadText then
SQL.Text := '';
raise;
end;
end;
function TRPIBQuery.GetActive : Boolean;
begin
Result := not (State in [dsInactive, dsOpening]);
end;
procedure TRPIBQuery.Open;
begin
Active := True;
end;
procedure TRPIBQuery.Close;
begin
Active := False;
end;
{$ENDIF}
procedure Register;
begin
RegisterComponents('Data Access', [TRPQuery]);
{$IFDEF D5}
RegisterComponents('Interbase', [TRPIBQuery]);
{$ENDIF}
end;
end.
Table structure required is:
QueryID: Char 10 (primary key)
Description: Char 60
QueryText image 16
Hope this is found to be useful, and any comments and suggestions are welcome.
Norman McFarlane
Redpoint Solutions