Solved

dBASE subset filter expression indexes,creating

Posted on 1997-05-18
11
1,438 Views
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
0
Comment
Question by:dmgoncalves
  • 6
  • 5
11 Comments
 
LVL 2

Expert Comment

by:javiertb
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?
0
 

Author Comment

by:dmgoncalves
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."
0
 
LVL 2

Accepted Solution

by:
javiertb earned 240 total points
ID: 1336418
If you want to add a subset/filter programatically, just set Table1.Filter:='FIELD3 = <a value>'
Table1.Filtered:=True;
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;
     Desc:IDXDesc;
     DBase:TDataBase;
     DBREZ:DBIRESULT;
     Baza: Pchar;
Begin
 Baza:='trt01.DBF';{Table1.Tablename}
{this is building index descriptor}
 Witn Desc do
 BEgin
  szName:='c:\base\TRT01.MDX';
  {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... }
 End;
{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}
DBRez:=DbiAddIndex(DBase.Handle,hCursor,Baza,'STANDARD',Desc,nil);
If DbREz<>0 then
Application.Messagebox('Invalid index descriptor','Error',mb_OK);
End;

Note: Corresponding Object TTable must be exclusivly open.

******* dbiGetIndexDesc

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

Regards
0
 

Author Comment

by:dmgoncalves
ID: 1336419
Javiertb,
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.
HOWEVER....
When trying to set
   DESC.SZNAME:='c:\temp\filter\tests.mdx';
or DESC.SZTAGNAME:='TESTINDX';
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...
0
 
LVL 2

Expert Comment

by:javiertb
ID: 1336420
I'll try to see what is the problem you have due to.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 2

Expert Comment

by:javiertb
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:

------szName--------------------

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
------------szTagName----------------
DBINAME      Specifies the index tag name. Supported for dBASE only.

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

---------------aiKeyFld--------------------
DBIKEY      Specifies an array of field numbers in the key.

-------------szKeyExp------------------
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.

------------szKeyCond-------------------
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.

Regards,
Javi
0
 

Author Comment

by:dmgoncalves
ID: 1336422
Javi,

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).

Thanks,

David M. Goncalves
0
 

Author Comment

by:dmgoncalves
ID: 1336423
Javi-
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"
<dmgoncal@vicon.net> 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
>1.0?

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

Chuck Gadd
Director of Software Development, Cyber FX Communications.

0
 
LVL 2

Expert Comment

by:javiertb
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
0
 

Author Comment

by:dmgoncalves
ID: 1336425
Javi-

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.


0
 

Author Comment

by:dmgoncalves
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.
DMGoncalves
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Viewers will learn the different options available in the Backstage view in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now