In-Memory Tables for Delphi

Alexandre is a graduate student at the Federal University of Sao Carlos, Brazil. He can be contacted at [email protected].

Nearly every Delphi programmer understands that the TGrid component provides a spreadsheet-like interface for users to edit and view information. Using TGrid is straightforward when the information in all of the cells is of a single type. But what happens if you need a grid control where cells in different columns are of different types, and all the cells in any one column are of a single type? This is when TDBGrid comes in handy.

TDBGrid is a grid that's associated with a database table, having each column mapped to a field and each row mapped to a record in the table. The cells are edited and displayed according to each column's data type, which is taken from the associated TField. Records can be added and deleted by inserting and removing lines from the grid. The table is changed on the fly as users type into the cells, and you also get automatic validation from the TField objects. This works okay, but what if you don't want to create a table? You may want to let users enter temporary data and, in such cases, it's undesirable to have to create a table and delete files later. Plus, creating a physical table dynamically can be too slow for some applications. What you really need is a memory array. However, automatic validation and typed editing are useful and, if you use an array, you'll have to code those yourself. This line of thought made me turn to In-Memory Tables (IMTs).

IMTs versus Arrays

In essence, an IMT is a table that's associated with a region in memory instead of a file. IMTs behave much like ordinary database tables, except they don't have a physical file associated with them. Instead, they have an in-memory image of their record set, which is allocated dynamically. You can think of it as a bidimensional array with a separate type for each column (field), where each record is a line. Unlike Pascal arrays, IMTs are dynamic -- the number of lines (records) is variable, making them more useful than arrays in many instances. With this in mind, all you need to do is associate a TDBGrid with an IMT instead of a standard TTable. Thus, you'll produce a typed grid for entering temporary data, without even having to code validation and editing routines.

There are several situations where IMTs come in handy, including:

• Entering and storing data in different forms. Sometimes you need to enter data in one field and save it into various other fields, or vice versa. The data could be entered into an IMT first, then transferred to a physical table, enabling you to use standard data-aware controls to enter the data (rather than having to transfer data manually between memory variables and controls).
• Creating reports where it's difficult or impossible to map records to the detail band. You can fill an IMT with the data to be listed and use it as the main report table. (Yes, IMTs can be passed to QuickReport.)
• Providing parameters to a query. IMTs are a convenient way of letting the end user enter a list of parameters of the same type to a query. For example, the list of customers to consider in a sales report could be entered in an IMT, so that the user could choose any number of customers.

TMemTb: an IMT Component in Delphi

All of Delphi's database functionality is built on top of the Borland Database Engine (BDE). Surprisingly, Delphi does not provide any type of direct or native support for IMTs, even though the BDE does. The code I present here fills this gap by implementing a component that plays the role of a bridge between Delphi and the BDE functions that access IMTs. Like the standard TTable component, the TMemTb component (see Figure 1, which shows a typical field description for a TMemTb component) is derived from the TDataSet virtual class.

TDataSets have a database handle that is obtained with a call to the CreateHandle virtual function. Thus, I had to replace the original CreateHandle (that opens a table in a database) with a custom version that creates an IMT and returns its handle. This fools the TDataSet object into thinking it's dealing with a real table, when it's actually using the IMT handle. The steps I took to implement the component included:

1. Finding out what BDE routines manipulate IMTs. I searched Delphi's documentation with no success, until I realized that what I was looking for was in the BDE documentation. The only function I needed was DbiCreateInMemTable.

2. Finding out how and where the BDE database handles are associated with TDataSet objects. While browsing through Delphi's VCL code, I discovered that the association is made through calls to CreateHandle, OpenCursor, and CloseCursor -- all of which are virtual (thus, overrideable) methods of the TDataSet class.

3. Providing a way to specify the IMT's structure. I did this by adding a string list data member to TMemTb. Each string in the list has to have the name, type, and size for a field in the IMT. For example, the string "SURNAME:A20" defines an alphabetic field named "SURNAME" that is 20 characters wide. This list is used to fill an array of field descriptions that's passed to DbiCreateInMemTable when the table is opened in the call to CreateHandle. The string list can be edited at design time (a published property).

While these steps may sound straightforward, IMTs are an undocumented feature of Delphi. Consequently, I did run into several problems implementing TMemTb, including:

• IMTs cannot have their records deleted. Every time I tried to use the standard Delete method, it would raise an "unsupported capability" exception, so I had to code a replacement to Delete (imDelete). At first, I thought I'd override the TDataSet's Delete method, but it's not virtual. In Delphi 2, it was easy to set an error handler to call imDelete when Delete failed. In Delphi 1, things are not that simple, and it's necessary to avoid calls to Delete, using imDelete instead.
• IMTs cannot have indexes. This is not really a problem if you consider the temporary nature of IMTs (they lose all their data when closed). Nevertheless, I coded a simple version of FindKey that uses the first table fields as keys to search the IMT.
• IMTs need a database. I created a Tdatabase component dynamically when the first IMT was created, passed its handle to DbiCreateInMemTable, and destroyed it when the last IMT went away.

Listing One is the class declaration for TMemTb, and Listing Two is the code for the custom version of CreateHandle. (The complete source code and a sample In-Memory Table are available electronically; see "Programmer Services," page 3.) Note that the function never calls the original version, which is completely replaced. Using the component is quite simple: After it is installed, just place it on a form, define the field structure (the FieldStruct property appears at the property sheet), and activate it.

Conclusion

Despite the limitations in the BDE and Delphi's IMT support, IMT tables can be useful in many circumstances. An enhancement that could be made to the component presented here would be to provide a way for the IMT to be related to other tables, as in a master-detail or a one-to-one relation.

DDJ

Listing One

TMemTb = class(TDataSet)  private
    FFieldStruc: TStrings;
    prvHand : HDBICur;
    procedure SetTheFields(Value: TStrings);
    function RecordMatch(const A:array of const): boolean;
    procedure Fill_Field_Spec(fld_spec, fld_name:string; var m:flddesc);
  protected
    function CreateHandle: HDBICur; override;
    {$IFDEF VER20}
    procedure ErrorDel(DataSet: TDataSet; E: EDatabaseError;
                  var Action: TDataAction);
    {$ENDIF}
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    function FindKey(const A:array of const): boolean;
    procedure imDelete;
  published
    property FieldStruc: TStrings read FFieldStruc write SetTheFields;
  end;


</p>

Back to Article

function TMemTb.CreateHandle: HDBICur;var
 return_value: HDBICur;
 mfield: array [0..(MAX_IMT_FIELDS-1)] of flddesc; { 16 fields max...for now }
 err_cd: DBiResult;  { error code }
 cur_field:integer;
 fld_name, fld_spec:string;
 colon_pos:integer;
 fld_width:integer;
 mlast: integer;
begin
 cur_field:=0;
 mlast:=0; { index to the last field added }


</p>
 while cur_field < FFieldStruc.Count do
 begin
    { field definitions assumed to be in the form NAME:TYPE[SIZE] }
    fld_name:= FFieldStruc.Strings[cur_field];
    colon_pos:=pos(':', fld_name);
    if colon_pos>0 then
    begin
       fld_spec:= copy( fld_name, colon_pos+1, length(fld_name)-colon_pos );
       fld_name:=copy(fld_name, 1, colon_pos-1 );


</p>


</p>


</p>
       Fill_Field_Spec( fld_spec, fld_name, mfield[mlast] );
       mfield[mlast].iFldNum  :=mlast+1;        { Field number (1..n) }
       if (length(fld_name)>0) and (mlast<MAX_IMT_FIELDS) then
          inc(mlast);
    end;
    inc(cur_field);
 end;
 if mlast=0 then  { empty field list?
                    I'll create an useless field just to avoid the GPF }
 begin
    Fill_Field_Spec( 'F', 'No_Fields', mfield[mlast] );
    mfield[mlast].iFldNum  :=mlast+1;        { Field number (1..n) }
    inc(mlast);
 end;
 err_cd:=DbiCreateInMemTable (
           tdbobj.Handle,     { Database handle }
           'temptable',       { Logical Name }
           mlast,             { Number_of_fields }
           return_value       { Returned cursor handle }
         );
 if err_cd<>DBIERR_NONE then
 begin
    return_value:=nil;
    DbiError(err_cd);
 end;
 prvHand := return_value; { and they thought they'd hide it from me ;) }
 CreateHandle := return_value;
end;


</p>

Back to Article