An enhanced TQuery, combining the functionality of a TQuery, TBatchMove and TTable

Problem/Question/Abstract:

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?

Answer:

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 &#8212; 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:

{==================================================================================
Program Name : TEnhQuery - Enhanced Query
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!