How TMyQuery works

[tanvir]

Hi, conside the following code:

Code: Select all

procedure Query2();
var
  xQ : TMyQuery;
  ResultString, ss : string;
begin
    xQ := TMyQuery.Create(nil);
    xQ.Connection := Form1.MySQL1Connection;
    xQ.SQL.Clear;
    xQ.SQL.Add('SELECT   a, b, c from table) 
    xQ.Open;
    while not xQ.Eof do
    begin
       ResultString := ResultString + ' A ' + inttostr(xQ.FieldValues['a']);
       xQ.Next;
    end;
    xQ.Close;
    xQ.Free;
end;

Does it fetch the full result from the MySQL server when I open the xQ? Or it keeps fetching when it executes xQ.Next?
If it fetches the full result to the client machine, then why I can't close the mySQL connection immediately xQ.Open?
If it fatches iteratively, is there any way that I get the full result together in client machine and itereate it there? My target is to reduce number of communication with the server.
Thanks in advance for your help.

[ViktorV]

The TMyQuery component has the FetchAll and FetchRows properties, which default values are True and 25, correspondingly. If FetchAll = False, only FetchRows of rows will be read during the fetch of the TMyQuery.Open method. The rest of the rows will be fetched by necessity. You can read about it in the MyDAC help: https://www.devart.com/mydac/docs/?deva ... tchall.htm
When the FetchAll property is set to True, on calling the Open method, all data is read out from the database. And on calling the Next method, records are navigated through without additional connection to the database. But on connection close, all linked datasets will be closed as well.
To solve you issue, you should use the disconnected mode. More details can be found at https://www.devart.com/ibdac/docs/?work ... ctmode.htm

[tanvir]

Thanks a lot for your reply and clarification!
As you mentioned FetchAll is True by default, in that case how much data it can fetch all together? If the query returens a very big amount of result, does that fetch all of them at a time?

In our scenario, disconnected method is not a good option as establishing a connection again will take longer during the use of application.

[ViktorV]

When the FetchAll property is set to True, on calling the Open method the client reads out all the selected data from the database.
If disconnected mode doesn't suit you, you can implement the needed functionality by yourself.