Home Delphi TAdsQuery is NOT static. How to make it static
Reply: 1

Delphi TAdsQuery is NOT static. How to make it static

Bret Bordwell
1#
Bret Bordwell Published in 2018-01-12 14:27:40Z

I've ran into this issue before, and know how to get around it, but I'd like to know if there is a property I'm missing, or better way. My solution is to get a record count, then iterate by the count, vs looking for EOF.

Is there a better way?

What happens is the query result re-reads the Source.dbf AFTER the post. You never reach EOF, and the loop is infinite.

Consider the following code. For Simplicity, Source.dbf is a single record table, with a single field (named Field1). Field1 in Record 1 has the value of '123'.

procedure TForm1.Button1Click(Sender: TObject);
begin
  CreateSourceTable;
  CreateQuery;

  while not Qry.EOF do
  begin
    tblSource.Append;
    tblSource.FieldByName('Field1').AsString := '123';
    tblSource.Post;
    Qry.Next;
  end;
end;

procedure TForm1.CreateQuery;
begin
  Qry := TAdsQuery.Create(self);
  Qry.TableType := ttADSCdx;
  Qry.DatabaseName := 'c:\project\windows\tools\StaticQuery';
  Qry.SQL.Add('Select * from Source');
  Qry.SQL.Add('where Field1 = ''123''');
  Qry.Open;
end;

procedure TForm1.CreateSourceTable;
begin
  tblSource := TAdsTable.Create(self);
  tblSource.DatabaseName := 'c:\project\windows\tools\StaticQuery';
  tblSource.TableName := 'Source.dbf';
  tblSource.TableType := ttADSCdx;
  tblSource.Open;
end;
MartynA
2#
MartynA Reply to 2018-01-12 19:39:41Z

The (D7) documentation for TDataSet.Append stated

A newly appended record is posted to the database in one of three ways: For indexed Paradox and dBASE tables, the record is inserted into the dataset in a position based on its index. For unindexed Paradox and dBASE tables, the record is added to the end of the dataset. For SQL databases, the physical location of the appended record is implementation-specific. For indexed tables, the index is updated with the new record information.

I mention D7 because TAdsTable and TAdsQuery date from around that era, or a bit before, and were intended to be similar to TTable and TQuery, which were for working with datasets implemented using te obsolete BDE (Borland Database Engine).

In the example you quote, you have a dataset with a single field and you are repeatedly appending records with indistinguishable values ('123') into it, and the behaviour you see is similar to what you would get if you repeatedly added identical records to a file of record while traversing it in physical order.

I don't have the Advantage components myself, but can confirm that the same endless loop occurs with a BDE TQuery and TTable. The reason is obvious (and I would expect the same explanation would be true of Advatage):

  • Operations on the Table cannot affect the current cursor state of the Query, BUT

  • the Query re-evaluates its EOF condition after each call to Next and Next always succeeds because the records added by the Table are indistinguishable from the others found by the Query's WHERE condition. There is no way of avoiding that while the Query and the Table are both accessing the same underlying dataset and the rows added by the Table satisfy the Query's WHERE clause. A bit to my surprise, this endless loop behaviour occurs even when the TQuery's RequestLive property is false.

So, if you cannot avoid the WHERE clause point, the simplest way to avoid the problem would be to iterate a temporary copy of the dataset, rather than the dataset itself, e.g. by using Advantage's equivalent of the BDE's TBatchMove component to create the copy table and then iterare that instead of thQuery`'s result set. I hope the code necessary to do this is self-evident, but if not I could add a BDE example.

Btw, if the TAdsQuery supports Append or Insert, you realise that youu could dispense with the TAdsTable and add the extra rows using the TAdsQuery

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.310655 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO