An Enhanced TQuery

Combining the Functionality of a TQuery, TBatchMove and TTable

 


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:

  1. Create a TQuery
  2. Load SQL into the TQuery
  3. Open the Query
  4. Create a destination TTable
  5. Set its DatabaseName, TableName and TableType properties
  6. Create a TBatchMove
  7. Set its Source, Destination and Mode properties
  8. 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:

  1. Create an instance of the component
  2. Set the SQL property
  3. Set the Destination TableName (it defaults to 'INIT.DB')
  4. 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!