#91 v 7.01 SetRange not working when using index on integer key, if created with v7.01 AddIndex

[Giandomenico De Sanctis]

In version Tdbf 7.01 SetRange is not working when using an index on integer key, if the index
is created using AddIndex from same 7.01 version.
I found that the reason is that SetRange call TIndexCursor.VariantToBuffer method. This understand only TIndexFile.KeyType='N' for numeric indexes (dbf_idxcursor line 139). But AddIndex set KeyType on index with a call to TDbfIndexParser.GetKeyType, this method can set numeric KeyType with values other than 'N' (see dbf_idxfile
lines 1838-1840).
As a test solution I commented lines 1838-1840 in dbf_idfxfile and SetRange works again on numeric keys.
Note that the SetRange from 7.01 always works if the index is created with preceding version of Tdbf (I tested 6.9.1), because these always set KeyType='N' for numeric keys.
I have Delphi 10.2 Tokyo on Win 7 Professional. Applied patches suggested in bug #90 to write database with InsertRecord/AppendRecord.

[Giandomenico De Sanctis]

I also found that in Tdbf V7.01 the index on an integer field always acquire a keytype='I' when tablelevel=7 (xBaseVII). See TDbfFieldDef.VCLToNative in dbf_fields.
So the TDbf.SetRange is broken, because only expect keytype='N' for numeric keys.
Verified the bug also on Delphi XE2.

[Paul Baker]

Can you provide steps to reproduce with the latest code?

[Giandomenico De Sanctis]

Attached a simple program to test the bug (for this I used Delhi XE2).
I also found that the bug is exactly for Delphi index field type ftInteger, instead ftSmallint, ftLargeint and ftWord do not create the bug (see TDbfFieldDef.VCLToNative source code).

[Paul Baker]

I ran this program in Delphi 7 against https://sourceforge.net/p/tdbf/code/667/ from the Subversion repository.

I had to make some minor changes:
Data.DB to DB in uses clause (because I have Delphi 7)
System.StrUtils to StrUtils in uses clause (because I have Delphi 7)
Delete the line FilePathFull := 'C:\temp\'; (because I do not have this folder)
Use mm/dd/yyyy instead of dd/mm/yyyy (because that's what my system expects)

When I run this and click "Run test", I get these messages:

Creato db
Inserta riga 1
Inserta riga 2
Inserta riga 3
*** Reading numeric ix
Trovati 0
Fine file

I don't see a problem?

[Giandomenico De Sanctis]

First, I use the 7.01 version downloadable from sourceforge, tdbf701.zip, not the Subversion repository version.
But anyway in your run the results are incorrect (your changes to source are not relevant to this bug).
The program write 3 rows, with integer (ftInteger) keys 1, 2, 3.
Then the program start reading from record with key=2, setting the index file and then SetRange(2, 9999999), and read all rows until EOF.
So the correct reading result has to be this:
......
*** Reading numeric ix
Trovati 2
2 13/01/1960 VERDI NANDO
3 06/12/1971 ROSSINI NATALIA
Fine file

(the dates here are written in european format dd/mm/yyyy, in your locale will be different)
You can try defining the index field as ftSmallint. Now you obtain the correct result.
Or, leaving unaltered ftInteger as index field type, you can use:
Filter := 'COD_FASC>=2';
Filtered := True;
Again correct result.
Or change tablelevel from 7 (dBaseVII) to other, not changing any other line of code. Again correct result.
As another test, I compiled and run the same identical program, with no changes, against Tdbf version 6.91, Turbodelphi 2006. I always obtained the correct results.

[Paul Baker]

There are many different index key types. These are listed in section 8.1 of the documentation. The problem you are reporting is with the I (Integer) type.

Looking at the code, it appears that the TIndexCursor.VariantToBuffer() method only supports the N (Numeric) and C (Character) key types. This causes problems in the following methods that call it:

• TDbf.LocateRecordIndex(), used by TDbf.LocateRecord().
• TDbf.SetRange(), as you mention.
• TDbf.SearchKey().

The TIndexCursor.CheckUserKey() method has a similar problem. This causes problems in the following methods that call it:

• TDbf.SetRangePChar().
• TDbf.SearchKeyPChar().

[Paul Baker]

Thanks for reporting this. At the moment, the scope of this is beyond the amount of time I have to spend on it. Can you use a workaround for now?

[Giandomenico De Sanctis]

Thank for your interest.
As a workaround I can use other types of numeric index, different from 'I' (ftInteger), or use the filter methods that work well.