How to really cancel an asynchronous Connection.Execute

From: Ian Boyd (ian.msnews010_at_avatopia.com)
Date: 03/21/04 

Date: Sun, 21 Mar 2004 12:28:12 -0500

>>>> From: Ian Boyd (ian.msnews009@avatopia.com)
>>>> Subject: Anything i can do to help Connection.Cancel come back faster?
>>>> Date: 2003-08-31 12:18:56 PST

i'm executing a query asynchronously though an a Connection.Execute method.

The Cancel method is synchronous, meaning that my software is stuck until
SQL Server or ADO decides it is ready.

i've tried simply calling Connection.Close to simply break my connection to
SQL Server, but ADO throws an exception at that.

Is there something i can do to speed up the Cancel? i've tried boosting my
thread's priority, but that didn't do anything.

And i'm fresh out of ideas.

>>>> From: Ian Boyd (ian.msnews009@avatopia.com)
>>>> Subject: What's the proper way to Cancel and asynchronous execute and
fetch?
>>>> Date: 2003-08-26 06:25:10 PST

i'm trying to run a complicated query, that will return quite a few rows.

i want to be able to Connection.Cancel the execution at any time.

What is the proper combination of
    Connection.CursorLocation, adAsyncExecute, adAsyncFetch,
adAsyncFetchNonBlocking

i tried
    Connection.CursorLocation := adUseClient;
    Connection.Execute(, , adAsyncExecute);

and

    Connection.CursorLocation := adUseClient;
    Connection.Execute(, , adAsyncExecute | adAsyncFetch);

In both cases, i can Cancel the execution if i cancel it shortly into the
execute. If i wait too long, it doesn't cancel. Presumably, this is me being
allowed to cancel the running query on SQL Server, but once results are
coming back, i cannot cancel it until they are all returned (which takes a
while).

i've investigated articles
    KB194960
    KB190988
    KB229799
    KB224332
    KB262311

as well as Googled newsgroup articles.

All the above conflict, or their methods don't actually work.

i want to know the MS intended way to do it.

>>>> From: Ian Boyd (ian.msnews008@zunblvlda1.dyndns.org)
>>>> Subject: How to really Cancel an asynchronous Execute?
>>>> Date: 2003-10-19 09:54:51 PST

i asynchronously run a query that will take a long time to return using
Connection.Execute.

i would then like to be able to Cancel the asynchronously executing query.

i call Connection.Cancel, but Cancel doesn't really cancel it (i.e. i can
still wait up to 3 minutes on some queries). It's like it is just going to
let the query finish running anyway.

Is there a way i can REALLY cancel the query. i try freeing the Connection
object, but it complains about trying to free while an operation is going
on. i've thought about killing the thread, but then the objects won't have a
change to clean up.

Is there some way to cancel an asynchronously running query?

>>>> From: Ian Boyd (ian.borlandnews009@avatopia.com)
>>>> Subject: Cancelling asynchronous queries - HOW
>>>> Date: 2003-08-26 10:47:23 PST

How do i cancel an asynchronously running and fetching query?

Consider

procedure TfrmMain.bbStartQueryClick(Sender: TObject);
begin
    ADOQuery1.Connection := ADOConnection1;
    ADOQuery1.CursorLocation := clUseServer;
    ADOQuery1.CursorType := ctOpenForwardOnly;
    ADOQuery1.ExecuteOptions := [eoAsyncExecute, eoAsyncFetch];

    ADOQuery1.SQL.Text := Memo1.Lines.Text;
    ADOQuery1.Open;

    //The call to open returns immediatly (i.e. asynchronously)
end;

procedure TfrmMain.bbCancelQueryClick(Sender: TObject);
begin
// ADOConnection1.Cancel; //nope - exception
// ADOQuery1.Close; //nope - exception
// ADOQuery1.Recordset.Close; //nope - exception
// ADOQuery1.Recordset.Cancel; //nope - exception

// Insert your genius here

// ^^^^^^^^^^^^^^^
end;

>>>> From: Ian Boyd (ian.msnews009@avatopia.com)
>>>> Subject: How to really cancel an asynchronous Connection.Execute
>>>> Date: 2004-03-21 12:28:03 EST

It's now 8 months later, and i still don't have an answer.

Relevant Pages

  • Re: How to really cancel an asynchronous Connection.Execute
    ... When you cancel an operation, you're asking the engine to undo what you ... > i'm executing a query asynchronously though an a Connection.Execute ... Connection.CursorLocation, adAsyncExecute, adAsyncFetch, ... > i would then like to be able to Cancel the asynchronously executing query. ...
    (microsoft.public.data.ado)
  • Re: Return to form if query unmatched
    ... Dim MyRecCount As Long ... If it doesn't pop up a message and set Cancel = True. ... record source of that form. ... If it is a sql then you should create a query with that sql, ...
    (microsoft.public.access.queries)
  • Re: Return to form if query unmatched
    ... The final solution I have gone with is using my original query as datasource ... Dim MyRecCount As Long ... If it doesn't pop up a message and set Cancel = True. ...
    (microsoft.public.access.queries)
  • RE: check qry on form close for null entrys
    ... criteria I just put Datebut it still gives me dates from way back. ... If you are using the query builder, don't put Null for each field in the ... show the MsgBox and not restrict the person from closing. ... cancel the close and present the records will Nulls in the form. ...
    (microsoft.public.access.gettingstarted)
  • RE: check qry on form close for null entrys
    ... If you are using the query builder, don't put Null for each field in the ... Put each Null criteria on a different line. ... show the MsgBox and not restrict the person from closing. ... cancel the close and present the records will Nulls in the form. ...
    (microsoft.public.access.gettingstarted)