TDbf.Locate finding non-existing records
Summary
Locate
is a convenient method to seach for records in a dataset. TDbf
, however, has an issue with it: it can find records with are not contained in the database.
System Information
- Operating system: Windows 11
- Processor architecture: x86-64
- Compiler version: 3.2.2, 3.3.1
- Device: Computer
Steps to reproduce
The following simple project creates a dBase file with a few country names. Searching for names which share the first character of the country name in existing records and otherwise are different is successful although such names are not in the database.
Example Project
program project1;
uses
SysUtils, DB, DBF;
const
FILENAME = 'test.dbf';
var
Dbf1: TDbf;
procedure SearchFor(ACountryName: String);
begin
Write(' Searching for ', ACountryName);
if Dbf1.Locate('Country', ACountryName, [loCaseInsensitive, loPartialKey]) then
WriteLn(': found ', Dbf1.FieldByName('Country').AsString)
else
WriteLn(': not found');
end;
begin
if not DirectoryExists('data') then CreateDir('data');
Dbf1 := TDbf.Create(nil);
Dbf1.FilePath := 'data';
Dbf1.TableName := FILENAME;
if not FileExists(dbf1.FilePath + FILENAME) then begin
// Create table
Dbf1.TableLevel := 7;
Dbf1.Exclusive := True;
Dbf1.FieldDefs.Add('Country', ftString, 25, True);
Dbf1.CreateTable;
Dbf1.Open;
// The following step is important: An index, even if not used, is required for the issue to appear.
Dbf1.AddIndex('idxByCountry', 'Country', [ixCaseInsensitive]);
// Add some data...
Dbf1.AppendRecord(['Italy']);
Dbf1.AppendRecord(['France']);
Dbf1.AppendRecord(['Egypt']);
Dbf1.AppendRecord(['Indonesia']);
Dbf1.AppendRecord(['Austria']);
end
else
Dbf1.Open;
// Dbf1.IndexName := 'idxByCountry';
WriteLn('Country names in database:');
Dbf1.First;
while not Dbf1.EoF do
begin
WriteLn(' ', Dbf1.FieldByName('Country').AsString);
Dbf1.Next;
end;
WriteLn;
WriteLn('Working:');
SearchFor('Austria');
SearchFor('It');
SearchFor('In');
WriteLn('But...');
SearchFor('Axyz');
SearchFor('Fxyz');
Dbf1.Close;
Dbf1.Free;
ReadLn;
end.
What is the current bug behavior?
This is the output of the project (built with Laz 3.2.2 or Laz 3.3.1). Note the last two lines which are not correct: Although the names "Axyz" and "Fxyz" are not listed in the db they are reported to be found
Country names in database:
Italy
France
Egypt
Indonesia
Austria
Working:
Searching for Austria: found Austria
Searching for It: found Italy
Searching for In: found Indonesia
But...
Searching for Axyz: found Egypt
Searching for Fxyz: found Indonesia
What is the expected (correct) behavior?
But...
Searching for Axyz: not found
Searching for Fxyz: not found
Possible fixes
I wondered whether the "other" version of TDbf
on SourceForce (https://sourceforge.net/projects/tdbf/) has the same issue: copied their sources into the project folder of my test program --> correct behaviour.
In their svn commit history I found revision 579 "Revert to linear search in LocateRecord when case insensitivity is requested" of Oct 15, 2015. Essentially this puts the current code of function TDbf.LocateRecord
(unit dbf.pas
) into an if
block and calls LocateRecordLinear
in the else
part:
function TDbf.LocateRecord(const KeyFields: String; const KeyValues: Variant;
Options: TLocateOptions): Boolean;
var
lCursor, lSaveCursor: TVirtualCursor;
lSaveIndexName, lIndexName: string;
lIndexDef: TDbfIndexDef;
lIndexFile, lSaveIndexFile: TIndexFile;
begin
if not (loCaseInsensitive in Options) then
begin
// current code of LocateRecord here
end else
Result := LocateRecordLinear(KeyFields, KeyValues, Options);
end;
See also this patch: dbf-locate.diff
Applying this to the FPC version fixes the issue.
Note that the original commit of r579 contains another modification ("Avoid access violation in SearchKeyPChar() when cursor is not a TIndexCursor"). This is not contained in my patch since I don't know how to reproduce that issue.