Mega Code Archive
An enhanced tquery combining the functionality of a tquery, tbatchmove and ttable
<![CDATA[
In many of my applications, when I perform a query, I write it out to disk, using a TBatchMove. How can I create a component that will combine the functionality of TQuery with a TBatchMove?
--------------------------------------------------------------------------------
Where's the Documentation?
One of my associates mentioned something recently that took me by surprise. He said there aren't many articles about building components in the major Delphi periodicals. When I really thought about it, and also perused some back issues of the periodicals I get, I realized he was correct. There were articles about specific components and what they do, but I couldn't find an article that dealt with building components in a general way.
I think the reason is that the process of building a component is a really involved and complex one. It doesn't matter whether the desired component's functionality is simple or not. There are just a lot of things you have to consider while building a component. And because of this, I don't think you could easily cover that type of material in a single article. You'd probably want to include it as several chapters in a book or devote an entire book to the subject, which is exactly what many writers have done.
Why is the process complex, even if what you might write is not? It has to do with the object hierarchy. When you build custom components, you will always inherit from a base class, be it TObject, TComponent or another class on the inheritance tree. To ensure that you aren't reinventing the wheel when writing new methods, it's a good idea to study the methods and properties of the ancestor class and even the ancestor's ancestor class, or further up the line if you want. I find myself doing it a lot when creating components because inadvertently redeclaring functions and properties without overriding base class functions and properties will usually get you in a lot of trouble with the compiler. Or, your component may compile, but it may not work as expected or — worse yet — not work at all.
This tip is no exception.
A New TQuery Component
One of the most common things you'll do when performing queries in Delphi is write the answer set(s) to persistent data stores. What does this involve? Let's look at the steps:
Create a TQuery
Load SQL into the TQuery
Open the Query
Create a destination TTable
Set its DatabaseName, TableName and TableType properties
Create a TBatchMove
Set its Source, Destination and Mode properties
Execute the TBatchMove
Fairly easy, but a lot of code to accomplish a really simple task. Here's an example:
InitQuery := TQuery.Create(Application);
with InitQuery do
begin
DatabaseName := 'PRIVATE';
Close;
SQL.Clear;
SQL.Add('SELECT D.BATCH, D.RECORD, D.ACCOUNT, D.FACILITY, D."INGREDIENT COST",');
SQL.Add('D."PHARMACY ID", D.DAW, D."DAYS SUPPLY", D."DISPENSING FEE", D."MEMBER ID",');
SQL.Add('D."DOCTOR ID", D.NDC, D.FORMULARY, D."Apr Amt Due",');
SQL.Add('D1."DEA CODE", D1."GPI CODE", D1."DRUG NAME", D1."GENERIC CODE", 0 AS D."DAW COUNT"');
SQL.Add('FROM "' + EncPath + '" D, ":DRUGS:MDMDDB" D1');
SQL.Add('WHERE (D.' + DateFld + ' >= ' + BStart + ' AND D.' + DateFld + ' <= ' + BEnd + ') AND');
SQL.Add('((D."RECORD STATUS" P'') OR (D."RECORD STATUS" R'')) ');
SQL.SaveToFile('mgrInit.sql');
try
Open;
try // Send the SQL result to :PRIV:INIT.DB
InitTable :="TTable.Create(Application);"
with InitTable do begin DatabaseName :="PRIVATE";
TableName :="INIT";
end;
InitBatch := TBatchMove.Create(Application);
with InitBatch do begin
Destination := InitTable;
Source := InitQuery;
Mode := batCopy;
Execute;
end;
finally
InitTable.Free;
InitBatch.Free;
end;
except
Free;
Abort;
end;
Free;
end;
Having grown tired of having to do this over and over in my code, I decided to create a component that combines all of the functionality mentioned above. In fact, there are not any multiple execution steps — just one call to make the thing go. This component is a descendant of TQuery, so it enjoys all of TQuery's features, but has the ability to execute the steps above with one call. Not only that, it's intelligent enough to know if you're doing a query, such as an UPDATE, that doesn't require writing to another table. I could go into a lot more detail with this but I won't because I documented the source code extensively. Let's take a look at it:
{==================================================================================
Copyright © 1996 Brendan V. Delumpa All Rights Reserved.
Program Name : TEnhQuery - Enhanced Query
Created by : Brendan V. Delumpa
Description : This component, derived from TQuery, was created to save coding by
integrating the functionality of performing a BatchMove into the
TQuery's execution code. Whenever you want to create a persistent
result set in code, you always have to create a TTable and a
TBatchMove to move the data from the Query to the persistent store.
This component eliminates that by creating the necessary objects
immediately after performing an open. The component is smart enough
to know if a BatchMove is actually necessary by parsing the SQL and
seeing if a SELECT is being performed. If it isn't, the component
will perform an ExecSQL instead. One other thing to note is that
I've included a lot of exception handling. Granted, they force a
silent Abort, but I've ensured there aren't any stray objects
floating around either.
Important Additions:
Properties: DestinationTable - Name of destination table. Defaults to 'INIT.DB'
DestDatabaseName - Name destination database. If a component is
dropped into a form, you can set this interactively
with a property editor I created for it.
DestBatchMoveMode - This is a property of type TBatchMode. Defaults
to batCopy.
DoBatchMove - Determines if a batch move should take place at
all. If it should (value = True), the SQL
result set will be moved to a persistent data
store. Otherwise, a regular Open will
occur.
Methods: Execute (virtual) This is what you will call when using this
component. However, since this is a descendant
of TQuery, you can always use Open or ExecSQL
to go around this function. Notice that this is
virtual, which means that you can add more
functionality if you wish.
DoEnhQueryOpen: This takes the place of the Open method, but
(virtual) since it's private, it can only be called by
Execute. It too is virtual, so you can override
its functionality. I suggest you keep it private
to avoid people inadvertently using it.
Notes:
You may get a couple of compiler warnings stating that the vars "btc" and "tbl" may
not have been initialized. Ignore them. The reason for the warning is because the
vars are declared but only initialized if the Open succeeded. No use in creating
them if they aren't needed.
==================================================================================}
unit enhquery;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
DB, DBTables, DSGNINTF, alnames;
type
TDBStringProperty = class(TStringProperty)
public
function GetAttributes: TPropertyAttributes; override;
procedure GetValueList(List: TStrings); virtual; abstract;
procedure GetValues(Proc: TGetStrProc); override;
end;
TDestDBProperty = class(TDBStringProperty)
public
procedure GetValueList(List: TStrings); override;
end;
{Main type information for TEnhQuery}
TEnhQuery = class(TQuery)
private
FDestTblName : String;
FDestDBName : String;
FBatchMode : TBatchMode;
FDoBatchMove : Boolean;
procedure SetDestTblName(Value : String);
procedure DoEnhQueryOpen; virtual;
public
constructor Create(AOwner : TComponent); override;
procedure Execute; virtual; {Let people override this}
published
property DestinationTable : String read FDestTblName write SetDestTblName;
property DestDatabaseName : String read FDestDBName write FDestDBName;
property DestBatchMoveMode: TBatchMode read FBatchMode write FBatchMode;
property DoBatchMove : Boolean read FDoBatchMove write FDoBatchMove;
end;
procedure Register;
implementation
constructor TEnhQuery.Create(AOwner : TComponent);
begin
inherited Create(AOwner);
FDestTblName := 'INIT.DB'; {Set initial value of Destination Table on Create}
FDestDBName := Session.PrivateDir;
FBatchMode := batCopy;
FDoBatchMove := True;
end;
procedure TEnhQuery.SetDestTblName(Value : String);
begin
if (FDestTblName <> Value) then
FDestTblName := Value;
end;
{=========================================================================
This is a very simple routine that will determine which route to take with
respect to executing the SQL query. It gives the component a bit of
intelligence, so the user need only use one call. Essentially, it looks
at the first line of the query; if it finds the word SELECT, then it
knows to call OpenProc, which will open the query and perform a batch move.
=========================================================================}
procedure TEnhQuery.Execute;
begin
if (SQL.Count > 0) then
if DoBatchMove then {Check to see if a batch move is desired}
if (Pos('SELECT', SQL[0]) > 0) then
if (DestinationTable <> '') AND (DestDatabaseName <> '') then
try
DoEnhQueryOpen;
except
raise Exception.Create('Enhanced Query DoEnhQueryOpen procedure did not execute
properly. Aborting');
Abort;
end
else
MessageDlg('You must supply a Destination Table and DatabaseName', mtError, [mbOK], 0)
else
Open
else
try
ExecSQL;
except
raise Exception.Create('ExecSQL did not execute properly. Aborting');
Abort;
end
else
MessageDlg('You have not provided any SQL to execute' + #13 +
'so there is nothing to process. Load the' + #13 +
'SQL property with a query', mtError, [mbOk], 0);
end;
procedure TEnhQuery.DoEnhQueryOpen;
var
btc : TBatchMove;
tbl : TTable;
begin
try
Open;
try
tbl := TTable.Create(Application);
btc := TBatchMove.Create(Application);
with tbl do begin
Active := False;
DatabaseName := DestDatabaseName;
TableName := DestinationTable;
end;
with btc do begin
Source := Self;
Destination := tbl;
Mode := DestBatchMoveMode;
Execute;
end;
finally
btc.Free;
tbl.Free;
end;
except
Abort;
end;
end;
{=============================================================================
TDestDBProperty property editor override functions. Since the property editor
is derived from TStringProperty, we only need to override the functions
associated with displaying our dialog box.
=============================================================================}
function TDBStringProperty.GetAttributes: TPropertyAttributes;
begin
Result := [paValueList, paSortList, paMultiSelect];
end;
procedure TDBStringProperty.GetValues(Proc: TGetStrProc);
var
I: Integer;
Values: TStringList;
begin
Values := TStringList.Create;
try
GetValueList(Values);
for I := 0 to Values.Count - 1 do Proc(Values[I]);
finally
Values.Free;
end;
end;
procedure TDestDBProperty.GetValueList(List: TStrings);
begin
(GetComponent(0) as TDBDataSet).DBSession.GetDatabaseNames(List);
end;
procedure Register;
begin
RegisterPropertyEditor(TypeInfo(String), TEnhQuery, 'DestDatabaseName', TDestDBProperty);
RegisterComponents('BD', [TEnhQuery]);
end;
end.
With this component, here's all you do to perform a basic extract query:
Create an instance of the component
Set the SQL property
Set the Destination TableName (it defaults to 'INIT.DB')
Set the Destination DatabaseName (it defaults to Session.PrivateDir)
As you can see, it's all a matter of setting properties. You'll notice in the properties section of the code, I've got a property called DoBatchMove. This is a Boolean property that defaults to True. If you set it to false, the batch move will not occur, but the query will be opened. This ensures that you can use the component like a regular TQuery. You'd set this to False when you are using the component in conjunction with a TDataSource and TDBGrid.
As mentioned in the code comments, we have a custom property editor. For those of you who have wanted to learn how to do custom drop-down list property editors, study the code above. You'll be amazed at how incredibly easy it is to do.
Pat Richey of TeamBorland pointed me to the DBREG.PAS file in the \LIB directory to get the code for the property editor. I adapted it to use in this component. But the great thing about this is that once I implemented the property editor, I had a drop-down combo of databases, just like TQuery's and TTable's DatabaseName property!
]]>