Filtered Unitable seems to fetch everything - updated

[michaschumann]

My issue is with unitable against a firebird 1.5 server. I use it in several situations to edit one record only but it seems to fetch the complete table. I worked with IBX and FIB before where this didn't happen. I am sure, there is a problem with a parameter...

This is the code when a new item is added, the table has a primary key on ID (bigint).

Code: Select all

with tabupdate do begin
    close;
    tablename := 'doc';
    filter := 'id=0';
    keyFields := 'ID';
    filtered := true;
    // The next statement takes ages
    open;
    insert;
    docid := dmod.NextID('DOC');
    FieldByName('ID').AsString := docid;
...

Update: Same thing happens if I open an existing (unique) ID this way.

I used "component to code" from gExperts to supply the following code, containnint the current settings. And I use autocommit on the connection object.

Code: Select all

  with tabUpdate do
  begin
    Name := 'tabUpdate';
    Connection := DB;
    FetchRows := 1;
    LockMode := lmNone;
    SpecificOptions.Clear;
    SpecificOptions.Add('InterBase.DeferredBlobRead=True');
    SpecificOptions.Add('InterBase.CacheArrays=False');
    SpecificOptions.Add('InterBase.CacheBlobs=False');
  end;

[AlexP]

hello,

You are using a local filter (the Filter and Filtered properties), so for correct data filtration you need to retrieve all the data to the DataSet. To use server filtration, you have to use the FilterSQL property. In this case, when opening the DataSet, a filter condition will be added to the generated query, and only the required number of records will be returned.

[michaschumann]

Thanks, that I understand. Will it hurt if I just extend my code with appropriate filtersql := '...'; - Statements and leave the local filters in place? To my understanding the table will only retrieve the required records and the filter also passes them through. It's just a question of effort as I used filter quite a bit...

[AlexP]

Hello,

When opening UniTable, a query is generated:

Code: Select all

SELECT * FROM TABLE

where TABLE is the table name specified in the TableName property. When using FilterSQL before query execution, it will be modified:

Code: Select all

SELECT * FROM TABLE
WHERE ID=0

The condition is generated on the basis of the statement specified in the FilterSQL property:

Code: Select all

FilterSQL := 'ID=0';

When using a local filter, all the data is retrieved from the server, and filtered by the condition specified in the Filter property by exhaustive search.

[michaschumann]

Hello,

thanks. I was just curious whether a construction like

Code: Select all

with ...
   filter:='ID=0';
   filterSQL:='ID=0';
   filtered:=true;
   keyFields:='ID';
   open;

leads to problems due to double but identical filters. As I have used TUniTable for many updating tasks due to simplicity an would only add the appropriate FilterSQL statement everywhere. Or would it be better to delete the local filter?

[AlexP]

If the statements in the local and SQL filters are identical, then the local filter must be deleted, since it will have no sense and decrease performance if it applies to many records.