Avatar of gambuz
gambuz
 asked on

Dealing with long query (ADO)

Hi,

I am querying remote database server via ODBC using ADO component.

When the query is taking a long time to finish, my application becoming not-responsive.

If you click something there, Windows will response "Not-responding".

Here is my code:

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOQuery1.CommandTimeout := 3600; // 1 hour
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.AddStrings(Memo1.Lines);
  ADOQuery1.Active := TRUE;
  ADOQuery1.ExecSQL;
// The result will be displayed in a DBGrid
end;

My question, How to ignore all events or mouse clicks during this process so the "Not-responding" will not be displayed? But the application window should still be able to MOVE and MINIMIZE / MAXIMIZE ...

Further, I need to add a CANCEL button. How?

Thanks.
Delphi

Avatar of undefined
Last Comment
Geert G

8/22/2022 - Mon
Geert G

put the query in a thread
the cancel button stops the thread

here is a way with a stored procedure

https://www.experts-exchange.com/questions/23759011/How-to-implement-TimOut-for-stored-procedure-execution.html

ASKER CERTIFIED SOLUTION
Geert G

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Geert G

My question, How to ignore all events or mouse clicks during this process so the "Not-responding" will not be displayed? But the application window should still be able to MOVE and MINIMIZE / MAXIMIZE ...

this is windows behaviour itself, if you run the queries in thread it will be solved

just so you know, it is difficult ...

gambuz

ASKER
Thanks Geert,

It looks ... complicated to me :) I am still beginner btw :)

What does TBackGroundQuery at your second post has anything to do with your first post TCallbackThread?

Should I choose one of them? .... but I dont see cancel method in TBackGroundQuery.

Or probably I see it wrongly ... hmmm
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Geert G

i just posted a sample of my code to show how i do it
with TBackGroundQuery

TCallbackThread was created after using TBackGroundQuery for a while
i needed the same functionality in other threads
basically notifying an other thread or the main thread of something

The thing to keep in mind is that you need a separate connection inside the thread for the other query
otherwise it will block other queries too
Geert G

i posted in other questions once about this but haven't found it
searching for TCallbackThread will probably find your solution
gambuz

ASKER
Hi Geert,

I think I could understand the TCallbackThread ... I will try to use that in a test application.

My application is very simple, it does not have multiple queries in the same time :)

Only 1 query ... but I really dont like that Not-responding message and in-ability to cancel.

If you see my query code above, anything should I do in terms of ADO things when I kill the thread?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
gambuz

ASKER
Geert,

Here is my first attemp, see my code below.

I got error:

>>> Starting threaded procedure
>>> Error in connecting threaded procedure:
CoInitialize has not been called
>>> Finished threaded procedure

Even though I added:

  CoInitialize(nil);

In the TExecStoredProcThread as I saw in one other example:

https://www.experts-exchange.com/questions/23884499/Thread-and-select-from-Database-MSSQL2005-Express-with-ADO.html?sfQueryTermInfo=1+10+tcallbackthread
unit Unit1;
 
interface
 
uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB, DBCtrls, Grids, DBGrids, ExtCtrls,
  ActiveX // CoInitialize
  ;
 
type
  TCallbackProc = procedure (aMessage: string; aMessageInfo: Integer = 0) of object;
 
  TCallbackThread = class(TThread)
  private
    FCallBack       : TCallbackProc;
    FCallbackMsg    : string;
    FCallbackMsgInfo: integer;
    procedure SynchedCallback;
  protected
    procedure DoCallback(aMsg: string; aMsgInfo: integer = 0); virtual;
    property Callback: TCallbackProc read FCallback;
  public
    constructor Create(aCallback: TCallbackProc; CreateSuspended: Boolean = False); reintroduce; virtual;
  end;
 
  TExecStoredProcThread = class(TCallbackThread)
  private
    fSQLQuery        : TStrings;
    fConnectionString: string;
    fDatasource      : TDatasource;
    fADOQuery        : TADOQuery;
    fADOConnection   : TADOConnection;
  protected
    procedure Execute; override;
  public
    constructor Create(aCallback: TCallbackProc; aConnectionString, aSQLQuery: string; aDataSource: TDataSource); reintroduce; virtual;
    destructor Destroy; override;
  end;
 
  TForm1 = class(TForm)
    Panel1      : TPanel;
    DBGrid1     : TDBGrid;
    DBNavigator1: TDBNavigator;
    DataSource1 : TDataSource;
    StartQuery  : TButton;
    Memo1       : TMemo;
    procedure StartQueryClick(Sender: TObject);
  private
    { Private declarations }
    fExecStoredProcThread: TExecStoredProcThread;
    procedure StoredProcCallback(aMessage: string; aMessageInfo: Integer = 0);
  public
    { Public declarations }
  end;
 
var
  Form1: TForm1;
 
implementation
 
{$R *.dfm}
 
procedure TForm1.StartQueryClick(Sender: TObject);
begin
  if not Assigned(fExecStoredProcThread) then
  begin
    fExecStoredProcThread := TExecStoredProcThread.Create(StoredProcCallback, 'Provider=MSDASQL.1;Persist Security Info=False;Data Source=Employee Database', Memo1.Lines.Text, DataSource1);
  end;
end;
 
{ TCallbackThread }
 
constructor TCallbackThread.Create(aCallback: TCallbackProc;
  CreateSuspended: Boolean);
begin
  inherited Create(CreateSuspended);
  FreeOnTerminate := True;
  FCallback       := aCallback;
end;
 
procedure TCallbackThread.DoCallback(aMsg: string; aMsgInfo: integer);
begin
  FCallbackMsg     := aMsg;
  FCallbackMsgInfo := aMsgInfo;
  Synchronize(SynchedCallback);
end;
 
procedure TCallbackThread.SynchedCallback;
begin
  if Assigned(FCallback) then
    FCallBack(FCallbackMsg, FCallbackMsgInfo);
end;
 
{ TExecStoredProcThread }
 
constructor TExecStoredProcThread.Create(aCallback: TCallbackProc;
  aConnectionString, aSQLQuery: string; aDataSource: TDataSource);
begin
  inherited Create(aCallback);
 
  CoInitialize(nil);
 
  fConnectionString := aConnectionString;
 
  fSQLQuery      := TStringList.Create;
  fSQLQuery.Text := aSQLQuery;
 
  fADOConnection := TADOConnection.Create(nil);
  fADOConnection.ConnectionString := aConnectionString;
  fADOConnection.LoginPrompt      := FALSE;
  fADOConnection.Provider         := 'MSDASQL.1';
 
  fDataSource := aDataSource;
end;
 
destructor TExecStoredProcThread.Destroy;
begin
  FreeAndNil(fADOConnection);
  FreeAndNil(fSQLQuery);
  inherited Destroy;
end;
 
procedure TExecStoredProcThread.Execute;
begin
  DoCallback('Starting threaded procedure');
  try
    fADOConnection.Connected := TRUE;
    try
      DoCallback('Connected threaded procedure');
      try
        fADOQuery.CommandTimeout := 3600; // 1 hour
        fADOQuery.SQL.Clear;
        fADOQuery.SQL.AddStrings(fSQLQuery);
        fADOQuery.Active := TRUE;
        fADOQuery.ExecSQL;
        DoCallback('Threaded procedure executed Ok, 1');
      except
        on E: Exception do
          DoCallback('Error in running threaded procedure: '#13#10 + E.Message, 20);
      end;
    finally
      fADOConnection.Connected := FALSE;
    end;
    DoCallback('Disconnected threaded procedure');
  except
    on E: Exception do
      DoCallback('Error in connecting threaded procedure: '#13#10 + E.Message, 10);
  end;
  DoCallback('Finished threaded procedure');
end;
 
procedure TForm1.StoredProcCallback(aMessage: string;
  aMessageInfo: Integer);
begin
  Memo1.Lines.Add('>>> '+aMessage);
end;
 
end.

Open in new window

gambuz

ASKER
Ok, I think I put the CoInitialize in a wrong place.

It looks works ... querying, but then another error:

>>> Starting threaded procedure
>>> Connected threaded procedure
>>> Error in running threaded procedure:
Access violation at address 00494084 in module 'Project1.exe'. Read of address 000001A0
>>> Disconnected threaded procedure
>>> Finished threaded procedure

Probably because of the TDatasource?

I put DataSource1 and DBGrid1 in the main form and pass it to the create procedure:

    fExecStoredProcThread := TExecStoredProcThread.Create(StoredProcCallback, 'Provider=MSDASQL.1;Persist Security Info=False;Data Source=Employee Database', Memo1.Lines.Text, DataSource1);

procedure TExecStoredProcThread.Execute;
begin
  CoInitialize(nil);
 
  DoCallback('Starting threaded procedure');
  try
    fADOConnection.Connected := TRUE;
    try
      DoCallback('Connected threaded procedure');
      try
        fADOQuery.CommandTimeout := 3600; // 1 hour
        fADOQuery.SQL.Clear;
        fADOQuery.SQL.AddStrings(fSQLQuery);
        fADOQuery.Active := TRUE;
        fADOQuery.ExecSQL;
        DoCallback('Threaded procedure executed Ok, 1');
      except
        on E: Exception do
          DoCallback('Error in running threaded procedure: '#13#10 + E.Message, 20);
      end;
    finally
      fADOConnection.Connected := FALSE;
    end;
    DoCallback('Disconnected threaded procedure');
  except
    on E: Exception do
      DoCallback('Error in connecting threaded procedure: '#13#10 + E.Message, 10);
  end;
  DoCallback('Finished threaded procedure');
 
  CoUninitialize;
end;

Open in new window

gambuz

ASKER
Still does not work.
        fADOQuery.DataSource     := fDatasource; // <------ forgot this
        fADOQuery.CommandTimeout := 3600; // 1 hour
        fADOQuery.SQL.Clear;
        fADOQuery.SQL.AddStrings(fSQLQuery);
        fADOQuery.Active := TRUE;
        fADOQuery.ExecSQL;
        DoCallback('Threaded procedure executed Ok, 1');

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Geert G

fAdoQuery should be created in the thread in the constructor
and freed in the destructor

fAdoQuery := TAdoQuery.Create(nil);
fAdoQuery.Connection := fAdoConnection;

you don't really need  fSQLQuery
Geert G

fADOQuery.Active := TRUE;  is same as fADOQuery.Open;
this opens the query for a select

fADOQuery.ExecSQL;
this executes a statement like *UPDATE TABLE, INSERT INTO TABLE* etc

fADOQuery.SQL.AddStrings(fSQLQuery);
fADOQuery.Open;
DoCallback('Threaded procedure executed Ok, 1');

forgot to mention you need to work either with a memdataset
like TVirtualTable from www.devart.com (or TClientDataset)
or leave the thread in place and use the dataset from the thread
this last thing is a bit messy for cleanup
 
here is the report preview code, this may help a bit more
TfrmPreview = class(TForm)
    vtData: TVirtualTable;
  end;
 
procedure TfrmPreview.FlashTaskbarButton;
var
  FWinfo: FlashWInfo;
begin
  FWinfo.cbSize := 20;
  FWinfo.hwnd := Handle; // Handle of Window to flash
  FWinfo.dwflags := FLASHW_ALL;
  FWinfo.ucount := 3; // number of times to flash
  FWinfo.dwtimeout := 0; // speed in ms, 0 default blink cursor rate
  FlashWindowEx(FWinfo); // make it flash!
end;
 
procedure TfrmPreview.LoadData(ds: TDataset);
var I: Integer;
begin
  viewData.BeginUpdate;
  try
    if Assigned(ds) then
    begin
      vtData.Assign(ds);
      vtData.Active := True;
      viewData.DataController.CreateAllItems;
      for I := 0 to viewData.ColumnCount-1 do
      begin
        viewData.Columns[I].DataBinding.ValueTypeClass := TcxStringValueType;
        viewData.Columns[I].PropertiesClass := TcxTextEditProperties;
      end;
    end else
      viewData.ClearItems;
  finally
    viewData.EndUpdate;
  end;
  viewData.BeginUpdate;
  try
    viewData.ApplyBestFit;
  finally
    viewData.EndUpdate;
  end;
  FlashTaskbarButton;
end;
 
procedure TfrmPreview.QueryFinished(Sender: TObject; aQry: TDacQuery; ErrorMessage: string);
begin
  if Assigned(aQry) and not fBThreadTerminated then
  begin
    if ErrorMessage <> '' then
    begin
      pnlErrorMessage.Visible := True;
      lblErrMessage.Caption := ErrorMessage;
    end else
      LoadData(aQry.Dataset);
  end;
end;
 
procedure TfrmPreview.CreateParams(var Params: TCreateParams);
begin
  inherited CreateParams(Params);
  Params.ExStyle := Params.ExStyle or WS_EX_APPWINDOW;
end;

Open in new window

gambuz

ASKER
Here is a new attempt.

It is executed without any error ... and it looks querying the database.

But I dont get any result in the DBGrid1 ......................

So, I decided only to pass the TADOQuery to the thread ... leaving DataSource1 outside in the main-thread.

I set:

ADOQuery1.DataSource = DataSource1
DBGrid1.DataSource = DataSource1
DBNavigation1.DataSource = DataSource1

No error, but no result ...
unit Unit1;
 
interface
 
uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB, DBCtrls, Grids, DBGrids, ExtCtrls,
  ActiveX // CoInitialize
  ;
 
type
  TCallbackProc = procedure (aMessage: string; aMessageInfo: Integer = 0) of object;
 
  TCallbackThread = class(TThread)
  private
    FCallBack       : TCallbackProc;
    FCallbackMsg    : string;
    FCallbackMsgInfo: integer;
    procedure SynchedCallback;
  protected
    procedure DoCallback(aMsg: string; aMsgInfo: integer = 0); virtual;
    property Callback: TCallbackProc read FCallback;
  public
    constructor Create(aCallback: TCallbackProc; CreateSuspended: Boolean = False); reintroduce; virtual;
  end;
 
  TExecStoredProcThread = class(TCallbackThread)
  private
    fConnectionString: string;
    fADOConnection   : TADOConnection;
    fADOQuery        : TADOQuery;
  protected
    procedure Execute; override;
  public
    constructor Create(aCallback: TCallbackProc; aConnectionString: string; Query: TADOQuery); reintroduce; virtual;
    destructor Destroy; override;
  end;
 
  TForm1 = class(TForm)
    Panel1      : TPanel;
    DBGrid1     : TDBGrid;
    DBNavigator1: TDBNavigator;
    DataSource1 : TDataSource;
    StartQuery  : TButton;
    Memo1       : TMemo;
    ADOQuery1: TADOQuery;
    procedure StartQueryClick(Sender: TObject);
  private
    { Private declarations }
    fExecStoredProcThread: TExecStoredProcThread;
    procedure StoredProcCallback(aMessage: string; aMessageInfo: Integer = 0);
  public
    { Public declarations }
  end;
 
var
  Form1: TForm1;
 
implementation
 
{$R *.dfm}
 
procedure TForm1.StartQueryClick(Sender: TObject);
begin
  if not Assigned(fExecStoredProcThread) then
  begin
    ADOQuery1.SQL.Clear;
    ADOQuery1.SQL.Assign(Memo1.Lines);
    ADOQuery1.DataSource := DataSource1;
    fExecStoredProcThread := TExecStoredProcThread.Create(StoredProcCallback, 'Provider=MSDASQL.1;Persist Security Info=False;Data Source=Employee Database', ADOQuery1);
  end;
end;
 
{ TCallbackThread }
 
constructor TCallbackThread.Create(aCallback: TCallbackProc;
  CreateSuspended: Boolean);
begin
  inherited Create(CreateSuspended);
  FreeOnTerminate := True;
  FCallback       := aCallback;
end;
 
procedure TCallbackThread.DoCallback(aMsg: string; aMsgInfo: integer);
begin
  FCallbackMsg     := aMsg;
  FCallbackMsgInfo := aMsgInfo;
  Synchronize(SynchedCallback);
end;
 
procedure TCallbackThread.SynchedCallback;
begin
  if Assigned(FCallback) then
    FCallBack(FCallbackMsg, FCallbackMsgInfo);
end;
 
{ TExecStoredProcThread }
 
constructor TExecStoredProcThread.Create(aCallback: TCallbackProc; aConnectionString: string; Query: TADOQuery);
begin
  inherited Create(aCallback);
 
  fConnectionString := aConnectionString;
 
  fADOQuery := Query;
 
  fADOConnection := TADOConnection.Create(nil);
  fADOConnection.ConnectionString := aConnectionString;
  fADOConnection.LoginPrompt      := FALSE;
  fADOConnection.Provider         := 'MSDASQL.1';
end;
 
destructor TExecStoredProcThread.Destroy;
begin
  FreeAndNil(fADOConnection);
  inherited Destroy;
end;
 
procedure TExecStoredProcThread.Execute;
begin
  CoInitialize(nil);
 
  DoCallback('Starting threaded procedure');
  try
    fADOConnection.Connected := TRUE;
    try
      DoCallback('Connected threaded procedure');
      try
        fADOQuery.Connection     := fADOConnection;
        fADOQuery.CommandTimeout := 3600; // 1 hour
        fADOQuery.Active         := TRUE;
        fADOQuery.ExecSQL;
        DoCallback('Threaded procedure executed Ok, 1');
      except
        on E: Exception do
          DoCallback('Error in running threaded procedure: '#13#10 + E.Message, 20);
      end;
    finally
      fADOConnection.Connected := FALSE;
    end;
    DoCallback('Disconnected threaded procedure');
  except
    on E: Exception do
      DoCallback('Error in connecting threaded procedure: '#13#10 + E.Message, 10);
  end;
  DoCallback('Finished threaded procedure');
 
  CoUninitialize;
end;
 
procedure TForm1.StoredProcCallback(aMessage: string;
  aMessageInfo: Integer);
begin
  Memo1.Lines.Add('>>> '+aMessage);
end;
 
end.

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
nova2002

Ok, I got it working .... I should set like this:

DataSource1.DataSet := ADOQuery1;

The version below is working ... but how can I "kill" the thread??? For example: I want to cancel the query.

This code gave error "Operation cannot be performed while executing asynchronously"

procedure TForm1.CancelQueryClick(Sender: TObject);
begin
  ADOConnection1.Connected := FALSE;
  fExecStoredProcThread.Terminate;
  fExecStoredProcThread := nil;
end;
unit Unit1;
 
interface
 
uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB, DBCtrls, Grids, DBGrids, ExtCtrls,
  ActiveX // CoInitialize
  ;
 
type
  TCallbackProc = procedure (aMessage: string; aMessageInfo: Integer = 0) of object;
 
  TCallbackThread = class(TThread)
  private
    FCallBack       : TCallbackProc;
    FCallbackMsg    : string;
    FCallbackMsgInfo: integer;
    procedure SynchedCallback;
  protected
    procedure DoCallback(aMsg: string; aMsgInfo: integer = 0); virtual;
    property Callback: TCallbackProc read FCallback;
  public
    constructor Create(aCallback: TCallbackProc; CreateSuspended: Boolean = False); reintroduce; virtual;
  end;
 
  TExecStoredProcThread = class(TCallbackThread)
  private
    fADOConnection   : TADOConnection;
    fADOQuery        : TADOQuery;
  protected
    procedure Execute; override;
  public
    constructor Create(aCallback: TCallbackProc; Connection: TADOConnection; Query: TADOQuery); reintroduce; virtual;
    destructor Destroy; override;
  end;
 
  TForm1 = class(TForm)
    Panel1      : TPanel;
    DBGrid1     : TDBGrid;
    DBNavigator1: TDBNavigator;
    DataSource1 : TDataSource;
    StartQuery  : TButton;
    Memo1       : TMemo;
    ADOQuery1: TADOQuery;
    ADOConnection1: TADOConnection;
    CancelQuery: TButton;
    procedure StartQueryClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure CancelQueryClick(Sender: TObject);
  private
    { Private declarations }
    fExecStoredProcThread: TExecStoredProcThread;
    procedure StoredProcCallback(aMessage: string; aMessageInfo: Integer = 0);
  public
    { Public declarations }
  end;
 
var
  Form1: TForm1;
 
implementation
 
{$R *.dfm}
 
procedure TForm1.StartQueryClick(Sender: TObject);
begin
  if not Assigned(fExecStoredProcThread) then
  begin
    ADOQuery1.SQL.Clear;
    ADOQuery1.SQL.Assign(Memo1.Lines);
    fExecStoredProcThread := TExecStoredProcThread.Create(StoredProcCallback, ADOConnection1, ADOQuery1);
  end
  else Memo1.Lines.Add('!!!! A thread is still running ... cannot execute');
end;
 
{ TCallbackThread }
 
constructor TCallbackThread.Create(aCallback: TCallbackProc;
  CreateSuspended: Boolean);
begin
  inherited Create(CreateSuspended);
  FreeOnTerminate := True;
  FCallback       := aCallback;
end;
 
procedure TCallbackThread.DoCallback(aMsg: string; aMsgInfo: integer);
begin
  FCallbackMsg     := aMsg;
  FCallbackMsgInfo := aMsgInfo;
  Synchronize(SynchedCallback);
end;
 
procedure TCallbackThread.SynchedCallback;
begin
  if Assigned(FCallback) then
    FCallBack(FCallbackMsg, FCallbackMsgInfo);
end;
 
{ TExecStoredProcThread }
 
constructor TExecStoredProcThread.Create(aCallback: TCallbackProc; Connection: TADOConnection; Query: TADOQuery);
begin
  inherited Create(aCallback);
 
  fADOQuery := Query;
 
  fADOConnection := Connection;
end;
 
destructor TExecStoredProcThread.Destroy;
begin
  //
  inherited Destroy;
end;
 
procedure TExecStoredProcThread.Execute;
begin
  CoInitialize(nil);
 
  DoCallback('Starting threaded procedure');
  try
    fADOConnection.Connected := TRUE;
    try
      DoCallback('Connected threaded procedure');
      try
        fADOQuery.Connection     := fADOConnection;
        fADOQuery.CommandTimeout := 3600; // 1 hour
        fADOQuery.Active         := TRUE;
        fADOQuery.ExecSQL;
        DoCallback('Threaded procedure executed Ok, 1');
      except
        on E: Exception do
          DoCallback('Error in running threaded procedure: '#13#10 + E.Message, 20);
      end;
    finally
//      fADOConnection.Connected := FALSE;
    end;
    DoCallback('Disconnected threaded procedure');
  except
    on E: Exception do
      DoCallback('Error in connecting threaded procedure: '#13#10 + E.Message, 10);
  end;
  DoCallback('Finished threaded procedure');
 
  CoUninitialize;
end;
 
procedure TForm1.StoredProcCallback(aMessage: string;
  aMessageInfo: Integer);
begin
  Memo1.Lines.Add('>>> '+aMessage);
end;
 
procedure TForm1.FormCreate(Sender: TObject);
begin
  DataSource1.DataSet := ADOQuery1;
  DBGrid1.DataSource := DataSource1;
  DBNavigator1.DataSource := DataSource1;
end;
 
procedure TForm1.CancelQueryClick(Sender: TObject);
begin
  ADOConnection1.Connected := FALSE;
  fExecStoredProcThread.Terminate;
  fExecStoredProcThread := nil;
end;

Open in new window

Geert G

doesn't this work ?

procedure TForm1.btnKillThreadClick(Sender: TObject);
begin
  if Assigned(fExecStoredProcThread) then
    TerminateThread(fExecStoredProcThread.Handle, 0);
  fExecStoredProcThread := nil;
end;
nova2002

Nope, that does not work.

Still got this error "Operation cannot be performed while executing asynchronously" ...

This http://www.delphi3000.com/articles/article_5159.asp?SK= 
also does not help.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
gambuz

ASKER
ADOQuery1.SQL.Text := 'select * from table1';

I found this does not work:

ADOQuer1.Open;

Also this does not work:

ADOQuery1.Active := TRUE;
ADOQuery1.Open;

However, this does work:

ADOQuery1.Active := TRUE;
ADOQuery1.ExecSQL;

But, if I set to use Asynchronous mode, the above does not work.

*my head is now spinning dizzy*
gambuz

ASKER
I gave up :)

I found that non-Asynchronous ADO cannot be cancelled.

Doh!

Thanks Geert! I learned about TThread today because of you :)
Geert G

asynchronous is actually meaning in a thread

set it to synchronous and run it in thread
then you can kill the thread if you no longer want to wait for results

works for me but i use the sdac/odac components from www.devart.com
and not the ADO stuff
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
gambuz

ASKER
Hi Geert,

You are correct, this problem rely on ADO (activex?) from Windows.

Thanks for your info about Devart. They are nice components indeed. Unfortunately I dont have budget for this project, especially it is just to make some reports out of a database server remotely via ODBC. I can live with 5 - 15 minutes query :) ... and Good news is .. I am not the person who will make the report ... and it is only once a month.

Previously, they made using MS-Access ... then handed down to me. Oh yeah, I only know about Delphi 7 :)
Geert G

it's allways when you get given some ugly stuff  :)
Geert G

*nice* got lost between allways and when
Your help has saved me hundreds of hours of internet surfing.
fblack61