I am encountering a problem with TIBOTable that occurs after setting a range and then changing the IndexName property. I have a function that gets called multiple times and has the same TIBOTable object passed to it. Each time the function is called it saves off the original IndexName property, changes the IndexName , does a SetRange, and then restores the IndexName to the original value. The first time the function is called everything works as expected. There is an odd problem on subsequent calls when the IndexName is set. On the subsequent calls, the SetRange is still in effect from previous passes. After the IndexName is set, it appears that there are no records in the table (i.e. mytable.RecordCount =0). Subsequent SetRange calls do not find any records either.

I can do a CancelRange to prevent this from happening...but...its expensive. I can also close/open the table...but...that invalidates field references that I may have.

We are using IBO Version 4.5B (an upgrade isn't feasible right now), FireBird 1.5.4

Any thoughts on how to prevent the behavior without CancelRange or closing the table ?

Below is a contrived example that illustrates the problem:


procedure TForm1.TestSetRange;
var
mytable: TIBOTable;
myConnection: TIB_Connection;
seq: integer;
begin
BuildTable;
myConnection:=nil; myTable:=nil;
try
myConnection:=TIB_Connection.Create(nil);
myConnection.Username:='<YOUR USERNAME>';
myConnection.Password:='<YOUR PASSWORD>';
myConnection.Database:='<PATH TO YOUR DATABASE>';
myConnection.Connect;
myTable:=TIBOTable.Create(nil);
myTable.IB_Connection:=myConnection;
myTable.TableName:='TestTable';

//Pass 1 works correctly
mytable.Open;
myTable.IndexName:='Sequence2Index';
seq:=2;
mytable.SetRange([seq],[seq]);
showmessage('For Seq: '+inttostr(seq)+' : RecordCount='+inttostr(myTable.RecordCount));
myTable.IndexName:='Sequence1Index';

//Pass 2 fails to work correctly. myTable.RecordCount is 0 after setting the index
//and SetRange does not find any records
mytable.Open;
myTable.IndexName:='Sequence2Index'; //myTable.RecordCount becomes 0 after this line
seq:=4;
mytable.SetRange([seq],[seq]);
showmessage('For Seq:'+inttostr(seq)+' : RecordCount='+inttostr(myTable.RecordCount));
myTable.IndexName:='Sequence1Index';
finally myConnection.Free; myTable.Free end;
end;

procedure TForm1.BuildTable;
var
query:TIB_Query;
myConnection: TIB_Connection;
begin
query:=nil; myConnection:=nil;
try
myConnection:=TIB_Connection.Create(nil);
myConnection.Username:='<YOUR USERNAME>';
myConnection.Password:='<YOUR PASSWORD>';
myConnection.Database:='<PATH TO YOUR DATABASE>';
myConnection.Connect;
query:=TIB_Query.Create(nil);
query.IB_Connection:=myConnection;

query.SQL.Add('drop table TestTable');
try query.ExecSql; except end;
query.SQL.Clear;
query.SQL.Add('create table TestTable (');
query.SQL.Add('Sequence1 integer NOT NULL,');
query.SQL.Add('Sequence2 integer NOT NULL,');
query.SQL.Add('primary key (Sequence1))');
query.ExecSql;
query.SQL.Clear;
query.SQL.Add('create index Sequence1Index on TestTable (Sequence1)');
query.ExecSQL;
query.SQL.Clear;
query.SQL.Add('create index Sequence2Index on TestTable (Sequence2)');
query.ExecSQL;
query.SQL.Clear;
query.SQL.Text:='Insert into TestTable values (1,2)'; query.ExecSQL;
query.SQL.Text:='Insert into TestTable values (3,4)'; query.ExecSQL;
query.SQL.Text:='Insert into TestTable values (5,6)'; query.ExecSQL;
finally myConnection.Free; query.Free end;
end;