INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Cancelling a long TADOQuery

Cancelling a long TADOQuery

(OP)
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

CODE

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

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

Thanks,
Griffyn

RE: Cancelling a long TADOQuery

(OP)
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.

RE: Cancelling a long TADOQuery

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...

RE: Cancelling a long TADOQuery

(OP)
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.

CODE

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.

RE: Cancelling a long TADOQuery

(OP)
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.

RE: Cancelling a long TADOQuery

Hi Griffyn,

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

CODE

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...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close