TOraTable.SetRange

h.hasenack · Post by **h.hasenack** » Wed 25 Feb 2009 09:26

The SetRange method is missing, but the mastersource property is available as well as a KeyFields and Masterfields property.

Checking the Good Old VCL reveils that master/detail handling is implemented with SetRange calls for TTable, and also for nexus and flashfiler implementations.

Any tips on how to emulate a setrange call for tOraTable? Maybe using parametered SQL?

Regards - Hans

Plash · Post by **Plash** » Thu 26 Feb 2009 08:17

Please specify why you need the SetRange method. Master/detail in TOraTable works without this method.

h.hasenack · Post by **h.hasenack** » Thu 26 Feb 2009 14:07

In my code a template like this often is used

Code: Select all

aTable.SetRange([MasterID],[MasterID]);
Progress.Max:=aTable.RecordCOunt;
cnt:=0;
while not aTable.EOF do
begin
  Progress.Pos:=cnt;
  ProcessRecordData
  aTable.Next;
  inc(cnt);
end;

While it is fairly easy to replace this with equivalent code

Code: Select all

Progress.Max:=aTable.RecordCOunt;
cnt:=0;
if aTable.FindKey([aMasterID]) then
repeat
  Progress.Pos:=cnt;
  ProcessRecordData
  aTable.Next;
  inc(cnt);
until aTable.EOF or (aTable.FieldByName('masterField').ValueaMasterID);

The equivalent code does not set the Progress.Max to a correct value (records in the range) but to the total # of records in the table.

Generally, I use setrange quite a lot to process my data on a record-by-record basis. This because the processing itself cannot be translated to SQL.

Apart from that, it is very common to use Setrange for cursor based access (Let's say TTable clones) in delphi. Well, at least to us it is.

Regards

Plash · Post by **Plash** » Mon 02 Mar 2009 10:41

You can use the Filter property for this code:

Code: Select all

aTable.Filtered := True;
aTable.Filter := 'MasterID = ' + IntToStr(MasterID); 
Progress.Max:=aTable.RecordCOunt;
cnt:=0; 
while not aTable.EOF do 
begin 
  Progress.Pos:=cnt; 
  ProcessRecordData 
  aTable.Next; 
  inc(cnt); 
end;

h.hasenack · Post by **h.hasenack** » Mon 02 Mar 2009 10:48

Is Filter property evaluated clientside or serverside? (ODAC implementation)
Does it use the available indexes?

The standard VCL implementation is client side and scans all available records, apply filter for each record just to calculate the record count. You probably don't want that to happen with a table of 10M records.

Regards - hans

Plash · Post by **Plash** » Tue 03 Mar 2009 08:49

The Filter property is checked on the client side.

But TOraTable has also the FilterSQL property. The value of this property is added to the WHERE clause of SQL statement generated by TOraTable. So data are filtered on the server side, and indexes are used by the server.

After you set the FilterSQL property you need to reopen the TOraTable component:

Code: Select all

aTable.Close;
aTable.FilterSQL := 'MasterID = ' + IntToStr(MasterID);
aTable.Open;
Progress.Max:=aTable.RecordCount;
cnt:=0;
while not aTable.EOF do 
begin 
  Progress.Pos:=cnt; 
  ProcessRecordData 
  aTable.Next; 
  inc(cnt); 
end;

h.hasenack · Post by **h.hasenack** » Tue 03 Mar 2009 10:44

Yes, that could prove to be a good replacement for the SetRange call. I'll give a try!

I guess this won't interfere with the regular Filter settings? (So both will be applied)

It's a pity though that the dataset needs to be re-opened for the SQLFilter to be applied. This means I have to change my fields to static fields or remap all my TField pointers after the dataset has been reopened...

That's what I like so much about the setrange call: It is fast, and doesn't have many side effects. I even don't have to call DisableCOntrols/EnableControls to avoid controls slowing things down.

No Chance SetRange will be implemented in the (near) future?

Regards

Plash · Post by **Plash** » Wed 04 Mar 2009 08:27

We'll consider the possibility of enhancing the FilterSQL property to work like the Refresh method (saving current field objects).

h.hasenack · Post by **h.hasenack** » Wed 04 Mar 2009 08:30

Wonderful!.

Any ETA on this?

Regards!

Plash · Post by **Plash** » Wed 04 Mar 2009 08:55

We'll try to implement this feature in a month.

h.hasenack · Post by **h.hasenack** » Wed 04 Mar 2009 09:33

Wow! that'd be cool.

No chance on a SetRange implementation in a month?

Regards

Plash · Post by **Plash** » Thu 05 Mar 2009 08:58

We have investigated this problem in more details, and have come to a conclusion that the behaviour of the FilterSQL property should not be changed. Such change can cause some problems. TOraTable has different architecture than BDE TTable. The BDE component is based on indexes while TOraTable works like TOraQuery. It has the SQL property which it opens and fetches sequentially.

You should reassign your fields pointers after you change the FilterSQL property.