Asynchronous executing

[Alkatraz3]

Hi!

I've downloaded your latest SDAC trial and made very simple project: form, connection, storedproc and grid with datasource. The goal is to reproduce standart ADO aeAsyncExecute behavior. I've set NoBlocking to True in MSStoredProc1 And tried to Close/Open it. I found out, that fetching is indeed asynchronous, but executibg itself is not - application hung up after MSStoredProc1.Open until execution completed.

I want to transfer to SDAC from ADO, but i need aeAsyncExecute ADO functionality really bad. What i make wrong, or how to get that behavior in SDAC?

[Alkatraz3]

http://rapidshare.com/files/118793574/Project.rar

Here is the example project i made to test asynchronous execute, just set the MSConnection and table name in MSQuery SQL.

I also made thread object to simulate asynchronous execute (OnButton1Click), but it works weird - on first close/open thread hungs up and MSQuery is inactive untill it executes, but next close/open works fine - executing and fetching properties are set correctly and thread did not hung after MSQuery.Open... Funny...

If you could, please, correct my code, so it works correctly - i will surely use it in my projects...

UPD

Oh yes! i'm using Delphi2007 and SQL Server 2005...

[Antaeus]

TMSQuery only lets fetching data asynchronously. If you want to execute asynchronously a command, you should use the TMSSQL component.

[Alkatraz3]

That mean opening a huge query will hung application on duration of script executing, right?

Then how to open query and break execution then, if Query.Open will hung the whole application?

Or, for example, i have stored procedure, that calculate new values for a table and then returns all records with new values generated. How should i arrange asynchronous opening of such stored procedure and let user to break execution if it gets too long?

Thats too vital for me to have this functionality. If needed, i can make this through threads, if you can show me how to do it correctly. I tried do do it in my example, i would really appreciate if you look into it and correct me where i was wrong...

[Antaeus]

Yes, your application will freeze until data fetch will be started.
In order to execute a command in an additional thread, you should create a TMSConnection and TMSQuery (or TMSStoredProc) objects for this this thread and make sure that they are not used in more than one thread in the same time. Probably, connection pooling will be useful in this case. More information about connection pooling you will find in the SDAC help.

We will think of adding this functionality in SDAC.

[Alkatraz3]

Well, that'll be great, but i need this functionality now, unfortunately...

Here, this is how i get rid of this problem, please see to it, since its not working fine:

Code: Select all

unit OpenDataSet;

interface

uses
  Classes, DBAccess, MSAccess, Variants, Forms, DB, SysUtils, Windows, Messages;

const
     WM_UPDATE_STATUS = WM_USER + $100;
     WM_FETCH_IN_PROGRESS = WM_USER + $101;

type
  TOpenDataSetThread = class(TThread)
  private
    FForm: HWND;
    FDataSet: TCustomMSDataSet;

    procedure BeforeFetch(DataSet: TCustomDADataSet; var Cancel: Boolean);
    procedure AfterFetch(DataSet: TCustomDADataSet);

    procedure SynchronizeDataSet;
  public
    procedure Execute; override;

    constructor Create(Form: HWND; DataSet: TCustomMSDataSet);
    destructor Destroy; override;
  end;

  procedure OpenThreadedDataSet(Form: HWND; DataSet: TDataSet);

implementation

procedure OpenThreadedDataSet(Form: HWND; DataSet: TDataSet);
Begin
  TOpenDataSetThread.Create(Form, TCustomMSDataSet(DataSet));
End;

constructor TOpenDataSetThread.Create(Form: HWND; DataSet: TCustomMSDataSet);
begin
  inherited Create(True);

  FreeOnTerminate := True;

  FForm := Form;
  FDataSet := DataSet;

  FDataSet.BeforeFetch := BeforeFetch;
  FDataSet.AfterFetch := AfterFetch;

  If Not FDataSet.ControlsDisabled Then FDataSet.DisableControls;

  Resume();
end;

procedure TOpenDataSetThread.BeforeFetch(DataSet: TCustomDADataSet;
  var Cancel: Boolean);
begin
  PostMessage(FForm, WM_FETCH_IN_PROGRESS, LongInt(DataSet), 0);
end;

procedure TOpenDataSetThread.AfterFetch(DataSet: TCustomDADataSet);
begin
  Terminate;
end;

destructor TOpenDataSetThread.Destroy;
begin
  FDataSet.BeforeFetch := Nil;
  FDataSet.AfterFetch := Nil;

  FDataSet := Nil;

  inherited Destroy;
end;

procedure TOpenDataSetThread.Execute;
begin
  If FDataSet.Active Then FDataSet.Active := False;

  PostMessage(FForm, WM_UPDATE_STATUS, LongInt(FDataSet), 0);

  FDataSet.Active := True;

  PostMessage(FForm, WM_UPDATE_STATUS, LongInt(FDataSet), 0);

  While Not Terminated Do Application.ProcessMessages;

  Synchronize(SynchronizeDataSet);

  PostMessage(FForm, WM_UPDATE_STATUS, LongInt(FDataSet), 0);
end;

procedure TOpenDataSetThread.SynchronizeDataSet;
begin
  If FDataSet.ControlsDisabled Then FDataSet.EnableControls;

  PostMessage(FForm, WM_UPDATE_STATUS, LongInt(FDataSet), 0);
end;

end.

The update status procedure shows the current status of the dataset and fetch in progress procedure show currently fetched records. The disable controls and then enable controlls is needed for the VCL dxGrid to refresh its state and show all records.

This structure works weird - some times dxGrid not refreshed or i get Access Violations and Assertion Failed errors.

Please, again, see to it - i need an emulation of aynchronous execution and opening of datasets. It would be nice, if you help me out on this...