This article describes the API to execute SQL queries asyncronlusly. The sample of asyncronous SQL query execution is at the bottom.

Asyncronous SQL execution API

All metadata providers inherit the following methods to support asyncronous query execution:
// returns true if this metadata provider is capable to create copies of Connection objects
function SupportAsyncExecSql: boolean; virtual;

// creates and executes a query asyncronously and returns unidirectional readonly dataset
function ExecSQLDatasetURAsync(const ASQL:WideString; AParams:TParams=nil): IacAsyncSql; virtual;

// asyncronous query execution methods: 
function CreateSqlAsync(AConnection:TComponent; ATag:TObject): IacAsyncSql;
function CreateSqlAsync(ATag:TObject): IacAsyncSql; // internally creates a new connection 

function SupportCancelAsyncExecSql: boolean; virtual;
function CancelSQLDatasetAsync(AConnection:TComponent; ADataset:TDataset): boolean; virtual;

// methods to create a copy of Connection object: 
function CreateConnectionCopy(AOwner:TComponent): TCustomConnection; virtual; 

// event is fired after creation of each copy of Connection object
property OnCustomizeConnectionCopy: TacConnectionEvent;
The simplest way to create asyncronously execute a unidirectional, readonly query, is to call the ExecSQLDatasetURAsync method. If you need to create and execute a bi-directional query, you need to use the CreateSqlAsync method and this will requrie some more programming efforts. This method returns the IacAsyncSql interface that lets tune various query properites before its execution.

All asyncronous SQL execution methods requires a database connection to execute a query against it. If AConnection parameter is omitted, a new copy of connection is created internally and freed togeher with the SqlTask object.

A copy of connection that's created inside the ExecSQLDatasetURAsync and CreateSqlAsync methods has the same values of all published properties as the original connection object, but sometimes it's needed to perform additional tuning of connection for it's proper functioning. For example, this might be needed to specify database password that was not initially defined in connection properties. To fix this, you can define a handler for the OnCustomizeConnectionCopy event and tune the connection properties the way you need.

The IacAsyncSql interface

The IacAsyncSql interface provides the following properties and methods:
property Sql: WideString;
property Params: TParams;
property ReturnResults: boolean;
property Unidirectional: boolean;
property ReadOnly: boolean;

property MetadataProvider: TacBaseMetadataProvider; 
property Connection: TCustomConnection; 
property Dataset: TDataset;    

procedure Run; 
property State: TacTaskState; 
// TacTaskState=(actCreated, actRunning, actCompleted, actCanceled, actFaulted);

property CanCancel: boolean; 
function Cancel:boolean; // cancels the query

property Exception: Exception; // read it if the query state is actFaulted
function Wait(ATimeout:Cardinal=INFINITE):boolean;  

// The OnStarting and OnStopped events 
property OnStarting: TNotifyEvent; 
property OnStopped: TNotifyEvent;

// auxilary events (can be replaced with one OnStopped event handler)
property OnCompleted: TNotifyEvent;
property OnFaulted: TNotifyEvent;
property OnCanceled: TNotifyEvent;

Code sample

Below is the sample code of a unit that implements asyncronous SQL query execution and it's cancellation:
unit uDataView;

interface

uses
  acAST, acQBBase;

type
  TfrmDataView = class(TForm)
    acQueryBuilder1: TacQueryBuilder;

  private
    SqlTask: IacAsyncSql;
    DatabaseConnection: TComponent;
    procedure OnSqlTaskStopped(ASender: TObject);

implementation

procedure TfDataView.ExecuteSQLQuery;
begin
  if Assigned(SqlTask) and (SqlTask.State = actRunning) then
    Exit;

  if not Assigned(DatabaseConnection) then
  begin
    DatabaseConnection := acQueryBuilder1.SQLContext.MetadataProvider.CreateConnectionCopy;
    // tune specific connection properties like UserName and Password here if they aren't 
    // explicitly specified in the published properties of MetadataProvider.Connection object
  end;
  
  if acQueryBuilder1.SQLContext.MetadataProvider.SupportAsyncExecSql then
  begin
    SqlTask := acQueryBuilder1.SQLContext.MetadataProvider.CreateSqlAsync(DatabaseConnection,nil);
    SqlTask.OnStopped := OnSqlTaskStopped;
    SqlTask.Unidirectional := False;  SqlTask.ReadOnly := False;
    SqlTask.ReturnResults := True;

    SqlTask.Sql := 'SELECT * FROM Table';
    SqlTask.Run;
  end;
end;


procedure TfDataView.CancelSQLQuery;
begin
  if Assigned(SqlTask) and (SqlTask.State = actRunning) and SqlTask.CanCancel then 
    SqlTask.CancelAsync;
end;

procedure TfDataView.OnSqlTaskStopped(ASender: TObject);
begin
  case SqlTask.State of

    actCompleted:
    begin
      // do something with the SqlTask.DataSet
    end;

    actCanceled:
    begin
      // query has been canceled by the user 
    end;

    actFaulted:
    begin
      if SqlTask.Exception <> nil then
        ShowMessage('SQL execution has been failed. Error message: ' + SqlTask.Exception.Message);
    end;

  end;
end;

procedure TfDataView.Destroy;
begin
  if Assigned(SqlTask) and (SqlTask.State = actRunning) and SqlTask.CanCancel then
    SqlTask.Cancel;

  if Assigned(DatabaseConnection) then
  begin
    acQueryBuilder1.SQLContext.MetadataProvider.CloseConnection(DatabaseConnection);
    FreeAndNil(DatabaseConnection);
  end;
end;

Is this article helpful for you?