Title: Extending TDatabase to dynamically use TQuery
Question: How can I be more productive programming database application.
Often I just want to make some little SQL statements but alway's need a TQuery component to interface with the BDE.
Answer:
Thank's in advance to be very lenient with my english.
Let say that you just want to make a select count(*) to make some validation.
Because the interface for SQL with Delphi is TQuery you must create this kind of component at design time or dynamically and set all properties needed to achieve the task. I used to have a global Datamodule that contain my TDatabase and some 1 or 2 TQuery to make on the fly SQL requests.
One day I found this technique time consuming and decide to find a way to overcome the problem. I first thought about my first days in Client/Server with Gupta SQLWindows. With SQLWindows you obtain an sqlHandle and can use this handle with SQL function. Ex: if you want to make a direct call you just have to use SQLImmediate('select count(*)...'). So I decide to make something to achieve a similar job with Delphi VCL, then TTechDynamicDB was borned.
Here's the interface public section of this component.
TTechDynamicDb = class; //only forward declaration
TChangeSettingsEvent = procedure(db: TTechDynamicDB) of object;
TNotifyQueryEvent = procedure(qry: TQuery) of object;
TTechDynamicDb = class(TDatabase)
public
{ Public declarations }
procedure Open; reintroduce; //This one hide the ancestor but called the inherited Open;
constructor Create(AOwner: TComponent); override;
destructor Destroy; override;
procedure SetParams(sServerName, sDBName, sUserName, sPassword: String);
function GetQuery: TQuery;
function SQLPrepare(sSQL: String;
const Args: array of const): TQuery;
procedure SQLImmediate(sSQL: String; const Args: array of const);
function SQLPrepareAndExecute(sSQL: String;
const Args: array of const): TQuery;
function SQLReturnOne(sSQL: String;
const Args: array of const): Variant;
function SQLPopulateList(sSQL: String;
const Args: array of const; List: TStrings): Integer;
published
{ Published declarations }
property TransactionSupported: Boolean read FTransactionSupported
write FTransactionSupported;
//Event Properties
property OnChangeSettings: TChangeSettingsEvent
read FOnChangeSettings
write FOnChangeSettings;
property OnStdQueryProp: TNotifyQueryEvent read FOnStdQueryProp
write FOnStdQueryProp;
property OnBeforeExecute: TNotifyQueryEvent read FOnBeforeExecute
write FOnBeforeExecute;
end;
The Idea behind this component is to dynamically create the TQuery component based on the current database and session. All other methods are just utility method that help you use the TQuery.
So in detail:
function GetQuery: TQuery;
Just create a TQuery object assigning Database and session property based on the TDatabase. Also fired an event to give opportunity to set other properties. The caller must free the query.
function SQLPrepare(sSQL: String;
const Args: array of const): TQuery;
Same as GetQuery and prepare the sSQL statements before returning the TQuery; Caller must free the query;
function SQLPrepareAndExecute(sSQL: String;
const Args: array of const): TQuery;
Same as SQLPrepare but open the TQuery for you. This one should have been named SQLPrepareAndOpen; Caller must free the query;
procedure SQLImmediate(sSQL: String; const Args: array of const);
This procedure automatically execute the sSQL on the current database/session. Use to execute insert,update,delete and dml;
function SQLReturnOne(sSQL: String;
const Args: array of const): Variant;
This one open a TQuery based on sSQL and open it and return query.Fields[0] as a Variant. Use it to retreive the count(*) or a foreign lookup. The only rules here, the query must return only one row. If it is not, only the first value will be return.
function SQLPopulateList(sSQL: String;
const Args: array of const; List: TStrings): Integer;
This is my favorite one, it open a query based on sSQL and populate a TStrings with the result set of the query. If the query contain more than one field, every fields are concatenated with a Tab (#9) separator. Use it to populate list box items, memo lines, combo box items etc.
Here are some exemples:
DynDB refer to an instance of TTechDynamicDB.
//Just make an iteration
var
qry: TQuery
begin
qry := DynDB.SQLPrepareAndExecute('Select STATE from ST where ' +
'COUNTRY = %s',
[QuotedStr('CAN')]);
try
...
some treatements here
...
finally
qry.Free;
end;
end;
//Get a foreign description
var
sName: String;
begin
sName := DynDB.SQLReturnOne('select LASTNAME from CUST ' +
'where CUST.ID = %s',
[QuotedStr('ABC')]);
end;
//Populate a List box
DynDB.SQLPopulateList('select NAME, WEIGHT from animals', ListBox1.Items);
Conclusion, this component save me a lot of time and now most of my TDataModule are not clutter with needlessly TQuery.
Donald