gambuz

asked on 3/19/2009

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

Last Comment

Geert G

8/22/2022 - Mon

Geert G

3/19/2009

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

3/19/2009

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

3/19/2009

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

3/19/2009

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

3/19/2009

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

3/19/2009

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

gambuz

3/19/2009

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

3/19/2009

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

3/19/2009

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

3/19/2009

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

3/19/2009

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

3/19/2009

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

3/19/2009

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

3/19/2009

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

3/19/2009

doesn't this work ?

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

nova2002

3/19/2009

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

3/19/2009

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

3/19/2009

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

3/19/2009

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

3/20/2009

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

3/20/2009

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

Geert G

3/20/2009

*nice* got lost between allways and when

Your help has saved me hundreds of hours of internet surfing.

fblack61