dBASE subset filter expression indexes,creating

Posted on 1997-05-18
Last Modified: 2013-11-24
I need to be able to create dBASE IV "subset filter" expression indexes on-the-fly in my Delphi 1 app.  Currently, I *DO* know how to create a simple expression index:
Table1.addindex('INDEXNAME', 'FIELD1 + FIELD2', [ixexpression]);

In DataBase Desktop, it is possible to add a boolean subset/filter expression, such as FIELD3="A VALUE".
How do I create such an index programmatically.  I have been told that a direct call to the BDE is required.

Thank you very much.

David M. Goncalves
Question by:dmgoncalves
  • 6
  • 5

Expert Comment

ID: 1336416
Could you give me an example of what exactly do you want to get?
I guess it has nothing to do with table filtering, hasn't it?

Author Comment

ID: 1336417
In response to javiertb's request...  Thank you for your interest.  Yes it has *something* to do with table filtering, but I'm not sure what that means to you.  

I believe I need to use the BDE function dbiAddIndex and specify the correct IDXDesc structure using bSubset=True and set the szKeyCond, but I am so rushed doing other things I don't have time to figure it out.  Ideally you would be able to show me how to use a similar function DbiGetIndexDesc to steal the structure from an existing table.

My application serves to store drug testing records in dBASE IV tables.  There are several different indexes for browsing the data.  Each index is a compound index using several fields: PART_ID, SERIAL, and DRUGORALC.  PART_ID is a unique participant id.  SERIAL is the serial number of the test, and DRUGORALC is always "D" for drugs or "A" for Alcohol.

PART_ID + SERIAL + DRUGORALC is always a unique key. In several indexes, DRUGORALC is evaluated in a subset condition (filter) index to require either DRUGORALC="D" or DRUGORALC="A".

I realize that there are other ways to perform this filtering, but such changes would be far-reaching in my established code.  I need to be able to create these indexes, and others like them, programmatically.

Following is an excerpt from the Database Desktop help file, describing subset condition (filter) indexes:

"A subset condition expression (also called a filter) is an expression that evaluates to true or false. Database Desktop creates for a dBASE table an index that points only to values that meet the filter's requirements. For example, if you create the subset condition expression State=CA, you tell Database Desktop to create an index on those values in the State field that match the value CA.
You create a subset condition expression on a dBASE table from the Create Table dialog box or the Restructure Table dialog box. Choose Define to display the dBASE Define Index dialog box.

Enter a subset condition expression in the Subset Condition (filter) Expression edit box.
To create a subset condition expression,

1.      Choose Subset Condition (filter) Expression. The button name changes to Index Field and the insertion point moves to the Subset Condition (filter) Expression edit box.
2.      Enter the expression you want.
3.      Choose Index Field to return to the field list.

Copyright 1993-1995 Borland International."

Accepted Solution

javiertb earned 240 total points
ID: 1336418
If you want to add a subset/filter programatically, just set Table1.Filter:='FIELD3 = <a value>'
i.e. if value is a number Filter:='FIELD3='+ inttostr(value);
     if value is string Filter:='FIELD3='+#39+value+#39;

Anyway, here you have an example of using DBiAddIndex and dbiGetIndexDesc

*******  dbiAddIndex ***************

procedure TForm1.Button1Click(Sender: TObject);
 var hCursor:hdbiCur;
     Baza: Pchar;
{this is building index descriptor}
 Witn Desc do
  {this is indexfile name: Full path and extention neccessary}
  iIndexId:=0;{ allways 0 }
  szTagName:='NALDOH';{ This is indexname }
  szFormat:='BTREE';{ I set always 'BTREE' and this work }
  bPrimary:=false;{ always false }
  bUnique:=false or true;{ Depend on nessesery  }
     {If DBaseTable has a Unique index, after deleting
     Record, impossible insert record into Dataset with same Key      value, because this record marked in file as deleted. To         resolv this problem I called in method AfterDelete function      DBIPackTable, It Packed corresponding file. It`s seems to me      that`s wrong- Borland IDAPI driver work in this case no          valid Therefore I never set unique index }
  bDescending:=false;{ True, for descending index }
  bMaintained:=true;{ True,  }
  bSubset:=false;{ I set false }
  bExpIdx:=true;{ True, if expression index, for multiple                   index-always true}
  iCost:=0;{ always 0 }
  iFldsInKey:=0;{0-for expression index 1-for not expression  }
  iKeyLen:=14;{ Length of index expression (without 0 byte see                 szkeyexp)}
              {for not expression index always = 12 }
  bOutofDate:=false;{  i set always false }
  iKeyExpType:=513;{ 513 for Expression index; 529 for not                      expression }
  aiKeyFld:=[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];{for expression              index always [0,0,0..0]}
            {for not expression [N,0,0,0..0], where N- number of              index field}
  szKeyExp:='str(nal,2)+str(doh,12)';{ Key expression }
  szKeyCond:='';{ Subset condition . I set always =''; }
  bCaseInsensitive:=false; { True, if case insensitive index. I                              set always false}
  iBlockSize:=4096;{ Block size in bytes.}
 {This value defined expirience for example to use function   dbiGetindexdesc}
  iRestrNum:=1;{ Restructure number: 1,2,3... }
{this is getting Database handle}
DBase:=Session.FindDatabase('zarplata');{other way possible}
{this is receiving table cursor}
DBREZ:=DbiGetCursorForTable(DBase.Handle,Baza,'STANDARD',hCursor);{other way possible}
If DbREz<>0 then
Application.Messagebox('Getting cursor error','Error',mb_OK);
{this is creating new index}
If DbREz<>0 then
Application.Messagebox('Invalid index descriptor','Error',mb_OK);

Note: Corresponding Object TTable must be exclusivly open.

******* dbiGetIndexDesc

function GetIndexDesc(T: TTable; IndexName: String): IDXDesc;
  hNewCur: hDbiCur;
  iIndexId: LongInt;
  InfoStr: string;
  pInfoStr: array[0..100] of char;
  Check(DbiCloneCursor(T.Handle, False, False, hNewCur));
    iIndexId:= 1;
    Check(DbiSwitchToIndex(hNewCur, PChar(IndexName), Nil, iIndexId, FALSE));
    Check(DbiGetIndexDesc(hNewCur, 0, Result)); //'0' specifies the active index

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 1336419
So sorry, but I have encountered several problems attempting to implement your solution:

1) I receive an (Error 44 : field identifier expected) when trying to set Table1.filtered:=true or Table1.filter:='DRUGORALC="A"'.  I suspect that the Filtered and Filter properties of the TTable component you suggested do not apply to my Delphi 1 VCL, since these properties are not in my help file.  That's ok, and I'm really more interested in my original question:

2) In an attempt to replicate your usage of the BDE engine calls, I have created a new project, added DBIProcs and DBITypes to my Uses clause, pasted in the sample you provided, and substituted information about my table.
When trying to set
or szFormat:='BTREE';
or aiKeyFld:=[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
szKeyExp:='SERIAL + PART_ID';{ Key expression }
szKeyCond:='DRUGORALC="C"'; {subset filter expression},
I receive (Error 26 : Type mismatch).  Each time I received the error, I would remark out the line, until I received another compiler error.  

I believe that you have used this function successfully, but for some reason, I cannot use it as-is. Perhaps there is an environmental parameter that I need to change...?  I have searched out the buffer typedefs, DBITBLNAME, DBINAME, and DBIKEY, but the brief descriptions are not too meaninful to me at this point.

Thank you for your time; I'm sure we can work this out shortly...

Expert Comment

ID: 1336420
I'll try to see what is the problem you have due to.

Expert Comment

ID: 1336421
You have to include BDE and DB units in your uses clause.
First of all I'd recommend you to take a look at BDE help file where you'll find some delphi examples on using DbiAddIndex. You were right about Filter property, it's a delphi 2 feature (sorry).
Anyway, here you have specific help on those errors you got:


The following table describes how to name Paradox indexes:

Index ID Param      Non-composite index      Composite index

szName               Same as field name      Can be any legal name not                                         used as a field name;                                         must be unique
iIndexID      Same as field number    Valid ID (256 to 511)                   (1 to 255)              output only;not specified                                         while index  is created
DBINAME      Specifies the index tag name. Supported for dBASE only.

DBINAME      Currently, for information only. Describes the physical index format type (for example, BTREE or HASH).

DBIKEY      Specifies an array of field numbers in the key.

DBIKEYEXP Specifies the key expression for an expression index (dBASE only). This field is used only if bExpIdx = TRUE. The expression is stated as a dBASE expression.

DBIKEYEXP Specifies the expression that defines the subset condition (dBASE only). This field is used only if bSubset = TRUE. The expression is stated as a dBASE expression.

Those examples I told you on BDE help file are more clear and simple than this one, so you'd better start from this point.


Author Comment

ID: 1336422

Please note I have increased the points on this question, as a token of my appreciation for all your additional comments.  I am very pleased with the advice you have given thus far, but...

Regarding you last comment:
I do not appear to have BDE.DCU anywhere on my system, or on my Delphi disk.  Could these BDE functions only be available to later versions of Delphi (remember, I use Delphi 1.0).


David M. Goncalves

Author Comment

ID: 1336423
An additional response came in from a newsgroup query I had posted on comp.lang.pascal.delphi.databases.:

On Fri, 23 May 1997 08:55:05 -0400, "David M. Goncalves"
<> wrote:

>I am trying to use DBiAddIndex and dbiGetIndexDesc in my Delphi
>1.0 app. I am told I need BDE.DCU in my uses clause, but I do
>not have this on my system.  Is this file for Delphi 2+ only?
>Is it possible to use these very important functions in Delphi

BDE.DCU is for D2
for D1, use all of these : DbiProcs,DbiTypes, DbiErrs

Chuck Gadd
Director of Software Development, Cyber FX Communications.


Expert Comment

ID: 1336424
Sorry about this mistake, I should have asked you befor what Delphi version you had. I already knew that BDE is only for Delphi 2 and that you had to include the other dbi* units for Delphi 1. I'm sorry about that. Anyway, I hope you have succesfully make it work.

Regards, Javi

Author Comment

ID: 1336425

Thank you for your continued assistance.  Unfortunately, my latest problem persists even with the following units in my uses clause: DbiErrs, DbiProcs, DbiTypes (they were always there).

When trying to set SZNAME, aiKeyFld,szKeyExp,szKeyCond  I receive Error 26 : Type mismatch.

If you refer to my original question, you will see that I ask:
"I need to be able to create dBASE IV "subset filter" expression indexes on-the-fly in my DELPHI 1 app."  All the rest has been commentary.

I will continue to post my "point allowance" on this question until I have a conclusive answer to my first question.  That is,
IF IT IS POSSIBLE, HOW CAN I DO IT?  Obviously, we're just a fractional step away.


Author Comment

ID: 1336426
Still haven't gotten it to work, but I'll post a fresh question to narrow-down the problem.  Thanks for all your help; we have made significant progress.

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
control image tags in a string ? 12 152
Drag & Drop... Data from one grid to another 2 47
Browsing a TTreeView in Delphi 5 31
Way to create an iPhone app for my customers 8 37
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question