Cancelling a long TADOQuery

[Griffyn]

Hi all,

I've got a reporting service set up (no UI), that accepts requests via TIdHTTP, uses ADO to retrieve data from our database, and then generates an Excel file which is saved.

The problem I have is when somebody wants to cancel a big report. Each request starts up a thread that instantiates the appropriate report class (a descendant of TMyReport) and the TMyReport object does all the work. The thread is there to allow multiple reports to be run at once. I can have the thread set the TMyReport.Cancel property, which I can check periodically during the report generation, but most of the time is spent opening the TADOQuery request - some reports may take 30 minutes or more on this step.

I've read that I can set TADOQuery to run asynchronously, but I'm not sure how to do this appropriately. My guess is that TMyReport starts up it's own thread for each TADOQuery request, then sits in a loop waiting for the TADOQuery to finish, or if it's Cancel property is set to True. If that's the case, do I do something like

QueryThread.Start;
repeat
  Sleep(100);
until QueryThreadHasFinished or ReportIsCancelled;

I'm not sure what best practice is here. Any thoughts are appreciated.

Thanks,
Griffyn

 

[Griffyn]

I put together something like this but it's not working.

Specifically - the TADOQuery.State property remains as dsOpening, which causes my call to TADOQuery.First to hang forever, because it waits until it's state is not dsOpening.

I'm going to make a guess here that it's got something to do with me running this in a thread, but I'm not sure. I tried a hack by setting up a wrapper class for TDataset so that I could call SetState(dsBrowse) in the OnFetchComplete event, but TADOQuery.First then causes an AV instead.

Any help is appreciated.

 

[whosrdaddy]

Be aware that the OnFetchxxxx events are fired from a thread inside the ADO provider (so not the main UI thread).
So you must use Synchronize(), for more details, check This SO post. Another option is not to use Async and throw the whole thing in a thread like here.

/Daddy

-----------------------------------------------------
Helping people is my job...

 

[Griffyn]

I've read lots of stuff regarding asynchronous queries, and I can't find anything particular to this issue, so I'm wondering if I'm missing something fundamental - this is my first time using async.

My approach was to replace the reference to MyQuery.Open in my code with OpenInterruptibleQuery(MyQuery). As I said, this function returns, but fails on the next line which is MyQuery.First - it hangs forever because MyQuery.State is still set to dsOpening, instead of dsBrowse.

procedure TReportEngine.OpenInterruptibleQuery(AQuery: TADOQuery);
begin
  AQuery.ExecuteOptions := [eoAsyncExecute, eoAsyncFetchNonBlocking];
  AQuery.OnFetchComplete := InterruptibleQueryComplete;
  FInterruptibleQueryComplete := False;
  AQuery.Open;
  repeat
    Sleep(100);
  until (FInterruptibleQueryComplete or IsCancelled);
  if IsCancelled then
    AQuery.Cancel;
end;

procedure TReportEngine.InterruptibleQueryComplete(DataSet: TCustomADODataSet;
  const Error: ADODB.Error; var EventStatus: TEventStatus);
begin
  FInterruptibleQueryComplete := True;
end;

The whole purpose of this is not to have an async query, as TReportEngine is already running in a thread, but to be able to cancel the MyQuery.Open from another thread.

 

[Griffyn]

In case I wasn't clear, when my function returns, it has fully retrieved the dataset - I've watched the task manager and can see the I/O throughput matches that when I just use MyQuery.Open on it's own. OnFetchComplete is being called appropriately, and my repeat..sleep..until loop finishes because the complete flag is set.

 

[whosrdaddy]

Hi Griffyn,

I whipped up a complete example in Delphi XE7 how to correctly use ADO and async query:

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.DB, Data.Win.ADODB, Vcl.StdCtrls;

type
  TDataSetWrapper = class(TDataSet);

  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    Button1: TButton;
    Memo1: TMemo;
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
    AbortQuery : Boolean;
    procedure ADOQuery1FetchProgress(DataSet: TCustomADODataSet; Progress, MaxProgress: Integer; var EventStatus: TEventStatus);
    procedure ADOQuery1FetchComplete(DataSet: TCustomADODataSet; const Error: Error; var EventStatus: TEventStatus);
    procedure ProcessRecords;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

// warning about OnFetchxxxx events, they occurr OUTSIDE the main thread, always synchronize
procedure TForm1.ADOQuery1FetchComplete(DataSet: TCustomADODataSet; const Error: Error; var EventStatus: TEventStatus);

begin
 if EventStatus = esOK then
  // access your records here
  TThread.Synchronize(nil, ProcessRecords);
 TThread.Synchronize(nil, procedure()
  begin
   Button1.Caption := 'Start';
   Button1.Tag := 0;
  end
 );
end;

procedure TForm1.ADOQuery1FetchProgress(DataSet: TCustomADODataSet; Progress, MaxProgress: Integer; var EventStatus: TEventStatus);
begin
 if Button1.Tag=2 then
  begin
   EventStatus := esCancel;
   TThread.Synchronize(nil, procedure()
    begin
     if Assigned(ADOQuery1.RecordSet) then ADOQuery1.RecordSet.Cancel;
     Button1.Tag := 0;
    end
   );
  end else
 if Button1.Tag=1 then
  TThread.Synchronize(nil, procedure()
   begin
    Memo1.Lines.Add(Format('progress: %d',[Progress]));
   end
  );
end;

procedure TForm1.ProcessRecords;
begin
   ADOQuery1.First;
   Memo1.Lines.Add(ADOQuery1.Fields[0].AsString);
   ADOQuery1.Next;
   Memo1.Lines.Add(ADOQuery1.Fields[0].AsString);
   ADOQuery1.Next;
   Memo1.Lines.Add(ADOQuery1.Fields[0].AsString);
   ADOQuery1.Next;
   Memo1.Lines.Add(ADOQuery1.Fields[0].AsString);
   ADOQuery1.Next;
   Memo1.Lines.Add(ADOQuery1.Fields[0].AsString);
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
 // use tag to define state we are in
 // 0 means idle
 // 1 means query active
 // 2 means abort query
 if Button1.Tag = 0 then
  begin
   Button1.Tag := 1;
   // simulate long query with cross join, make sure your table has enough records (in this case 1000 records is enough)
   ADOQuery1.SQL.Text := 'SELECT TOP 100000 T1.* FROM CP_SITE_ID T1, CP_SITE_ID T2';
   ADOQuery1.Open;
   Button1.Caption := 'Abort';
  end else
 if Button1.Tag = 1 then
  begin
   // abort!!!
   Button1.Tag := 2;
  end;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
 Button1.Caption := 'Start';
 Button1.Tag := 0;
 ADOConnection1.Connected := True;
 ADOQuery1.ExecuteOptions := [eoAsyncExecute, eoAsyncFetchNonBlocking];
 ADOQuery1.OnFetchProgress := ADOQuery1FetchProgress;
 ADOQuery1.OnFetchComplete := ADOQuery1FetchComplete;
end;

end.

Shoot if you have any questions

-----------------------------------------------------
Helping people is my job...