I.T. Discussion Community!
-Collapse +Expand
Delphi
Search Delphi Group:

Advanced
-Collapse +Expand Delphi To/From
To/FromCODEGuides
-Collapse +Expand Delphi Store
PRESTWOODSTORE

Prestwood eMagazine

July Edition
Subscribe now! It's Free!
Enter your email:

   ► KBProgrammingDelphi for W...   Print This     
 
Delphi for Win32:
Sub-Classing the BDE TQuery Component
 
Posted 14 years ago on 1/12/2006 and updated 10/30/2008
Take Away: First in a series

KB100296


First in a series, by Joshua Delahunty


 

Introduction

Have you ever had a Delphi component that did nearly everything you needed it to, save one extra function? Did you then want to add just that one piece of functionality onto the component to get it to do what you needed? In this article, I will discuss augmenting the BDE TQuery component to output its answer results to a BDE table on disk, using an added property and public method.

Recently the company I work for, Prestwood Software of Citrus Heights, California, had a client come to us with a job that involved running a series of SQL queries against Borland Database Engine (BDE) tables, producing an output file that was then exported to a text file at the end of the process. The existing process involved a written series of steps to be performed manually, using Paradox for Windows SQL statements and scripts, and was unwieldy and error-prone. Based on a tight budget and tight timelines, it was decided that we should automate the existing process, following the existing steps as closely as possible. The existing scripts and SQL were each very simple, straightforward steps; many of which could be combined into more elaborate SQL statements, but Local SQL has its limitations, and it can be time-consuming to adjust to those limitations, and adding testing time to the budget might have exceeded our parameters.

So I set out to move the process to a Borland Delphi for Microsoft Windows application, substituting Delphi objects and processes for those of the SQL statements and scripts. The scripts were all simple scan loops, modifying and adjusting one field at a time (along the lines of adjusting time-date formats, or combining multiple name fields in a single name field). The first step was to clean up the variable name usage and combine all the scripts into one, both to make the process more straightforward and maintainable, and also to speed it up.

Next, the SQL statements were combined where possible with a minimum of fuss (such as combining multiple INSERT INTO queries into a single UNION query) and testing. It was easy enough to run several queries to create a test file, and then to form a UNION query that it could be easily shown created the same output.

When the time came to move the process to Delphi, I realized I had a challenge to overcome. While I've been programming Delphi for years, two of them while working at Borland International, it never occurred to me that the TQuery component only produces an in-memory result set. While working with Paradox, I'd grown to rely on ANSWER.DB tables being created from queries, and having ready access to those query results to "chain" further results off of. Indeed, our client had relied on that very same functionality while creating this process. But TQuery doesn't do that work, and I was faced with a challenge.

The first reaction a programmer might have to this situation might be to create a TTable component with the desired output format, and link a TBatchMove component between the TQuery and TTable to copy the results out to an external BDE table. This procedure could be followed for each result table in the process, creating hard-coded TTable and TBatchMove parameters. This could even be optimized by changing the options on a single TTable and TBatchMove through code. The problem with this approach comes to light when the format of the data changes, and all those hard-coded values have to change. The end-user has no control over those changes, and the application has to be compiled from scratch with any changes made (although, as I'll show, the core idea of this approach is important to the eventual solution I chose).

The next step might be to create a subroutine that would create the table on the fly from the information in the TQuery. This gets closer to the goal of a well-engineered solution, but maintainability is still a factor.

My approach was to sub-class the TQuery component, adding this functionality as a new method, WriteQueryResults(). I also added a new property, AnswerTable, to allow the name of the output table to be specified. This article will show you the steps I followed. A follow-up article will explain further enhancements I made to the component as I worked with it further.

Creating TPSQueryEx

Follow the steps below to get started. All examples are from Borland Delphi 2005 for Microsoft Windows (hereafter referred to as "Delphi 9" or simply "D9". These steps presume Delphi 9 has been launched and starts with no active project. Menu choices are represented by the main menu choice, followed by any sub-menu choices, separated by vertical bar characters, such as "File | New | Form " Delphi for Win32" to represent the menu command for creating a new form in a D9 project.

1) Choose Component | New VCL Component... to start the New VCL Component wizard.

2) [Personality, Framework and Platform] Leave VCL for Delphi Win32 selected, click [Next >>]

3) [Ancestor Component] Choose TQuery from the list, click [Next >>] (we'll be basing our component off of TQuery)

It's time to choose a name for the component. You want to choose a good name here; something that won't clash with all the other component names available. It's best to include your initials, or an initialism of your company name, as I'm doing here, and perhaps some "non-standard" terms as well. It should start with the Borland-standard "T" (for type), as well. For this example, I'll be using TPSQueryEx - T for Type, PS for Prestwood Software, Query because the component will be based on TQuery, and Ex for "extended", because I'm extending an existing component. While TQueryExPS (or even TQueryEx_PS), two other possibilities I considered, might "read" better, I chose to name the component TPSQueryEx so that all Prestwood custom components we create group together when components names are sorted in a list. An argument could be made that it's overkill to use both PS and Ex here, the best rule of thumb is to choose a naming convention that you're comfortable with and then keep it consistent.

You'll also want to decide which component palette to place the new component on (I'll be keeping it on the BDE tab, right next to the original TQuery), and also where to keep the source and object code for the component.

4) [Component]

 

   

a) Change Class Name to the name for your component (I'm using TPSQueryEx)

 

    b) Change the Palette Page to something more useful to you (BDE in this example, so the new component groups with TQuery)

    c) Change the Unit Name (create a project directory for this component where it will be readily accessible to everyone who needs it for building applications, keeping it separate from other code. As an example, I used F:\lib.delphi\pub.components\TPSQueryEx\source\uPSQueryEx.pas, and had to create the TPSQueryEx and source directories in my existing F:\lib.delphi\pub.components tree separately through Windows Explorer before I completed this step).

 

    d) Click [Next >>]

5) [Install] Leave Create Unit selected (it should be the only option), click [Finish]

Creating the Component Package

You should now see the skeletal code for the new component. This code is currently free-standing, not part of any project or package. Next, create a package to "hold" the component and allow it to be registered to the appropriate Delphi component palette.

6) Click menu File | Close All

7) A [Confirm] message box appears. Click [Yes]

8) Browse to the folder you chose in step 4 for the Unit Name entry (create it now if you hadn't previously). Make sure the proper filename (uPSQueryEx.pas is set, and then click [Save]

9) Choose File | New | Package - Delphi for Win32

10) In the Project Manager window, Right-Click Package1.bpl, Click Add...

11) In the [Add] dialog, on the Add Unit tab, Click [Browse...], and browse to the unit file name for uPSQueryEx.pas; (this should be in the folder you chose in step 4). Click [OK]

12) Right-Click Package1.bpl, choose Rename, set the name to PSQueryEx; Press

13) Choose File | Save All

14) Browse to the directory where you saved uPSQueryEx.pas (from step 4); make sure the filename is PSQueryEx.bdsproj

15) Click [Save]

Adding Code

Now the actual coding takes place.

16) Open the Contains folder in the Project Manager, and double-click on uPSQueryEx.pas (or use your favorite way to display the source file) First, we'll add the AnswerTable property to the component.

17) Add the following line of code to the private declarations:

    FAnswerTable : String;

This represents the internal holder for the AnswerTable property. By convention, this is named with a capital "F" (for Field).

Add the following line of code to the published declarations:

    property AnswerTable : String read FAnswerTable write FAnswerTable;

This actually creates and publishes the property AnswerTable, indicating that the property is set ("write") and get ("read)" through simple access to the FAnswerTable variable in each case. Publishing the property causes it to show up in the Object Inspector for modification at design-time.

That's good enough for now; we've added a new string property to hold the answer table. Note that code could be added to verify that this string represents a proper filename, or that it ends in .DB, or other more intensive verification functions. I'll leave that as an exercise.

Next, we add the method that really does what we need: generation and output of an answer table from query results.

18) Add the following line of code to the public declarations:

    procedure WriteQueryResults; This is the procedure declaration for the new method we'll write.

19) Add the following procedure definition to the end of the code block, preceding the "end." statement at the end of the source file:

procedure TPSQueryEx.WriteQueryResults;

var

    tblAnswer : TTable;

begin

tblAnswer := TTable.Create(nil);

    try

        if (Self.FAnswerTable <> "') then

            tblAnswer.TableName := Self.FAnswerTable

        else

            tblAnswer.TableName := "ANSWER.DB';

        tblAnswer.DatabaseName := Self.DatabaseName;

        tblAnswer.FieldDefs := Self.FieldDefs;

        if (tblAnswer.Exists) then

            tblAnswer.DeleteTable;

        tblAnswer.CreateTable;

        with TBatchMove.Create(nil) do

        begin

            {.}Source := Self;

            {.}Destination := tblAnswer;

            {.}Execute;

            {.}Free;

        end;

    finally

        tblAnswer.Free;

    end;

end;

Click File | Save Code Explanation

Here's the code breakdown, by sections:

procedure TPSQueryEx.WriteQueryResults;

var

    tblAnswer : TTable;

begin

This code declares the WriteQueryResults procedure as a method of class TPSQueryEx, declares a local variable of type TTable to be used by this procedure, named tblAnswer, and begins the procedure definition.

    tblAnswer := TTable.Create(nil);

    try

        if (Self.FAnswerTable <> "') then

            tblAnswer.TableName := Self.FAnswerTable

        else

            tblAnswer.TableName := "ANSWER.DB';

        tblAnswer.DatabaseName := Self.DatabaseName;

This code creates a new table object to be used for this operation; making its ancestor nil - since it will be freed immediately after use, it won't need to be "auto-freed" later. Most of the work is encapsulated in a try block so that the table will always be freed, even in the case of an error condition. The FanswerTable field is checked for blankness. If it's not blank, it is assigned as the table name for the new table object, otherwise, a default table name of ANSWER.DB is assigned. The same database name is assigned from the query object to the table object; so the table will be created in the same database as the query is pulling data from.

    tblAnswer.FieldDefs := Self.FieldDefs;

    if (tblAnswer.Exists) then

        tblAnswer.DeleteTable;

    tblAnswer.CreateTable;

This code assigns the field definitions from the query to the table object (Delphi is really helping us out at this point " no iteration/loops, no checking, just a single safe assignment). The answer table is checked to see whether it already exists (such as from a prior execution of this method) and is deleted if it does. Then the new table to be output is created.   

         with TBatchMove.Create(nil) do  

    begin

        {.}Source := Self;

        {.}Destination := tblAnswer;

        {.}Execute;

        {.}Free;

    end;

This code creates a new TBatchMove object to be used to fill the table from the query results. Again, it will be freed almost immediately, so its ancestor is set to nil. The TBatchMove object's .Source property is set to the TPSQueryEx object, the .Destination property to the answer table that was just created, and then .Execute and .Free are called to perform the operation and free the temporary TBatchMove object. Note my stylistic use of {.} comments inside the with block to indicate properties that apply locally to the "with" object. They can be omitted if desired.

    finally

        tblAnswer.Free;

    end;

end;

This code provides a Free call that will return the temporary TTable object to the system after use, even if an error occurs in the preceding try block. The try block and procedure definition are then both completed with end statements.

Registering the Component

20) In the Project Manager Window, Right-Click the name of your project (
PSQueryEx.bpl in our example), and click Build. Make sure you receive no compile errors. If you do, go back to the program listing above and make sure you typed everything as indicated in the steps, and then once you've fixed any issues, perform this step again.

Once the code compiles properly, it needs to be Registered, so it will appear on a component palette, and it can be placed on forms for use.

21) Right-Click the name of your project (PSQueryEx.bpl), and Click
Install.

If all goes well, you'll see a dialog indicating that your component was successfully registered (similar to this one).


[GOOD]

Otherwise, you may see a dialog like this, in which case you'll have to troubleshoot the issue (look for a component by that name already registered by using the Component | Install Packages menu, or investigate further as necessary)

 [BAD!]

Once you see the first dialog, and NOT the second, then...

22) Click [OK]

23) Choose File | Close All to close the component code. Click [Yes] to save changes if you are prompted.

Using the Component

We will now test the component's new functionality. Here, we create a form with a
DBGrid for displaying the records of the sample biolife.db table. We will create a query that will retrieve all fish that have a length of less then 50cm, placing those records in an external "answer" table. We will create a TTable component that will reference that answer table, and a second DBGrid to display the table contents.24) Click
File | New | VCL Forms Application- Delphi for Win32

25) Place a TTable component on your new form, name it tblBiolife. Set its DatabaseName property to DBDEMOS, and its TableName property to biolife.db

26) Place a TDataSource component on the form, name it dsBiolife. Set its DataSet property to tblBiolife

27) Drag out a TDBGrid on the form, name it dbgBiolife. Set its DataSource property to dsBiolife

28) Set tblBiolife's Active property to True

Your form should now look something like this:

29) Next, expand the form downward to open up layout space for a button, more data components, and a second TDBGrid (an area about the same size as that for the the existing items)

30) Place a TButton below dbgBiolife, name it btnRunQuery. Change its Caption property to Run Query

31) Place a TDBQueryEx component to the right of btnRunQuery, name it qryBiolife. Set its AnswerTable property to ANSWER.DB, its DatabaseName property to DBDEMOS, and its SQL property to SELECT * FROM biolife b WHERE b.'Length (cm)' < 50

32) Place a TTable to the right of btnRunQuery, name it tblQueryResults. Set its DatabaseName property to DBDEMOS and its TableName property to ANSWER.DB

33) Place a TDataSource to the right of tblQueryResults, name it dsQueryResults. Set its DataSet property to tblQueryResults

34) Drag out a TDBGrid below the button and database components, name it dbgQueryResults. Set dbgQueryResults' DataSource property to dsQueryResults

The form should look similar to:

35) Double-click btnQueryResults, and set its code to the following:

procedure TForm1.btnRunQueryClick(Sender: TObject);

begin

    qryBioLife.Active := True;

    qryBioLife.WriteQueryResults;

    tblQueryResults.Active := True;

end;

36) Save the form and project, and then run it

37) Click the Run Query button, and then observe the results in the bottom grid. You should find the 9 result records representing fish with lengths 30, 38, 19, 28, 30, 15, 40, 25, and 25.Note that this was a simple test, we could have easily just based the

TDBGrid off the query in the first place. But having an answer table means that a new query can be created based off the result set of the first query. I encourage you to now expand upon this demo to try more varied and interesting tests of this added functionality.

More to Come

In the second article in this series, I will expand upon the new component, adding further functionality to make it more "design-time friendly" and more flexible.

More Info

Definition:  BDE
Definition:  BDE Alias
KB Post:  BDE Best Practices: Safely Use Paradox Tables
KB Post:  BDE Broken? Recent Microsoft Betas may be the culprit
Download:  Borland Database Engine 5.202
Article:  Installing BDE using Merge Modules
KB Post:  Paradox Table Specifications
Article:  Repair Paradox Tables
KB Post:  Using BDE TTables to read or write text file records

Comments

0 Comments.
Share a thought or comment...
 
Write a Comment...
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1117A1
Enter key:
Article Contributed By Joshua Delahunty:
I am a software engineer with many years professional experience, using everything from 6809 assembly language to C, C++, Delphi, Paradox for Windows, and Borland JBuilder.
Visit Profile

 KB Article #100296 Counter
12101
Since 4/2/2008

Follow PrestwoodBoards on: 


©1995-2020 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech