Is there an event when TMyQuery finishes?

[docH]

I have a select query operating on a very large table. When it is finished I display the results, usually only a few rows, in a report. On my development machine with a local db the query runs in about 10s but in the live db, accessed remotely over the Internet it can take up to 45s and it appears as if my application has hung.

Is there an event or something that I can use to detect when the results of the query are ready? Then I can show a hourglass while the query is running and only show the report once I have data.

Better still is there some way to show a progress bar while I wait for the data?

[ViktorV]

If you are using the FetchAll mode (the TMyQuery.FetchAll property is set to True), you can use the AfterFetch event handler, that is called after fetching all data. Also note, that MyDAC itself can change the cursor to hourglass while executing the query and fetching data. For this, it is enough to add the MyDACVcl module to the uses section of your module.
If you are not using the FetchAll mode, then data will be fetched only on demand. And TMyQuery has no event for notifying about data fetching completion.

[docH]

Thank you, I haven't explicitly set TMyQuery.FetchAll to anything so it is using whatever the default is but I'll try what you suggest.

The data returned is minimal, ten rows at most with only a couple of textual columns. but the sql getting it is complex. I obtain the latitude and logitude of a town, calculate the bounding lat and long within a given sperical radius of that town. Then I use those lat/long values in a WHERE clause to search a 3 million row table of postcodes to find postcodes inside the circle.

I have indexes on those fields but it seems that the optimiser is still doing a full table scan of the big table. One server I run this on, mysql ver 4.1, takes 8 minutes to run this sql, even using FORCE INDEX.

[ViktorV]

Feel free to contact us if you have any further questions about MyDAC.

[docH]

Using AfterFetch didn't seem to work and gives an error.
I have a FastReport (FrxReport1) joined in the normal, correct way via a fastreport dataset and a datasource to the MyDAC query.
Showing the report opens the datasource in the normal way and displays whatever is in the query. The SQL is generated elsewhere and returns 11 rows. MyQueryMembersInRadius.FetchAll is set to true.

This code works correctly.
There is a delay while the sql executes and then the report opens showing the data.

Code: Select all

procedure ShowTheReport;
begin
  MyQueryMembersInRadius.Close; //close the MyDac query
  MyQueryMembersInRadius.SQL.Clear ;
  MyQueryMembersInRadius.SQL.Add(sql) ;
  screen.cursor := crhourglass;
  MyQueryMembersInRadius.open;
  screen.cursor := crdefault;
  frxReport1.showreport(true);
end;

However, splitting it to use AfterFetch so that I only open the report after all the data is available, like this

Code: Select all

Procedure ShowTheReport;
begin
  MyQueryMembersInRadius.Close; //close the MyDac query
  MyQueryMembersInRadius.SQL.Clear ;
  MyQueryMembersInRadius.SQL.Add(sql) ;
  screen.cursor := crhourglass;
  MyQueryMembersInRadius.open;
end;

procedure TFrm1.MyQueryMembersInRadiusAfterFetch(DataSet: TCustomDADataSet);
begin
  screen.cursor := crdefault;
  frxReport1.showreport(true);
end;

always gives me an error 'SQL command doesn't return rows' as soon as it tries to open the report
Any ideas?
(It's not a huge deal as I can get it to work without using AfterFetch but I'd be interested in the reason for failure)

[ViktorV]

Your first sample is correct. However, if you want to use the AfterFetch event handler, you should edit your code as follows:

Code: Select all

Procedure ShowTheReport;
begin
  MyQueryMembersInRadius.Close; //close the MyDac query
  MyQueryMembersInRadius.SQL.Clear ;
  MyQueryMembersInRadius.SQL.Add(sql) ;
  screen.cursor := crhourglass;
  MyQueryMembersInRadius.open;
  frxReport1.showreport(true);
end;

procedure TFrm1.MyQueryMembersInRadiusAfterFetch(DataSet: TCustomDADataSet);
begin
  screen.cursor := crdefault;
end;

This error occurs due to the fact that the AfterFetch event occurs after fetching all data, but before loading them to the dataset.

[docH]

Ah, I understand, thank you.

[ViktorV]

Feel free to contact us if you have any further questions about MyDAC.