Question:
In many of my applications, when I perform a query, I write itout to disk, using a TBatchMove. How can I create a component that willcombine the functionality of TQuery with a TBatchMove?

Answer:

Where’s the Documentation?

One of my associates mentioned something recently thattook me by surprise. He said there aren’t manyarticles about building components in the major Delphi periodicals.When I really thought about it, and also perused some back issues ofthe periodicals I get, I realized he was correct. There werearticles about specific components and what they do, but Icouldn’t find an article that dealt with building components in ageneral way.

I think the reason is that theprocess of building a component is a really involved and complexone.It doesn’t matter whether the desired component’sfunctionality is simple or not. Thereare just a lot of things you have to consider while building acomponent. And because of this, Idon’t think you could easily cover that type of material in a singlearticle. You’d probably want to include it as several chapters in abook or devote an entire book to the subject, which is exactly whatmany writers have done.

Why is the process complex, even if what you might write isnot? It has to do with the object hierarchy. When you buildcustom components, you will always inherit from a base class, beit TObject, TComponent or another class on the inheritancetree. To ensure that you aren’t reinventing the wheel whenwriting new methods, it’s a good idea to study the methods and properties of the ancestor classand even the ancestor’s ancestor class, or further up the line if youwant. I find myself doing it a lot when creating components becauseinadvertently redeclaring functions and properties without overridingbase class functions and properties will usually get you in a lot oftrouble with the compiler. Or, your component may compile, but it maynot work as expected or &#151 worse yet &#151 not work at all.

This tip is no exception.

A New TQuery Component

One of the most common things you’ll do when performing queriesin 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 reallysimple 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 ofhaving to do this over and over in my code, I decided to createa component that combines all of the functionality mentioned above.In fact, there are not any multiple execution steps &#151 just one callto make the thing go. This component is a descendant of TQuery, so itenjoys 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 anUPDATE, that doesn’t require writing to another table. I could gointo a lot more detail with this but I won’t because Idocumented the source code extensively. Let’s take a look atit:

{================================================================================== 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;interfaceuses  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;implementationconstructor 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 extractquery:

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’llnotice in the properties section of the code, I’ve got a propertycalled DoBatchMove. This is a Boolean property that defaults to True.If you set it to false, the batch move will not occur, but the querywill be opened. This ensures that you can use the component like aregular TQuery. You’d set this to False when you are using thecomponent in conjunction with a TDataSource and TDBGrid.

Asmentioned in the code comments, we have a custom property editor. Forthose of you who have wanted to learn how to do custom drop-down listproperty editors, study the code above. You’ll beamazed at how incredibly easy it is to do.

Pat Richey of TeamBorland pointedme to the DBREG.PAS file in the LIB directory to get the code forthe property editor. I adapted it to use in this component. But thegreat thing about this is that once I implemented the propertyeditor, I had a drop-down combo of databases, just like TQuery’s andTTable’s DatabaseName property!