Active Query Builder support area

How to execute a query in another thread? (VCL Edition)

Last modified:


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

Asynchronous SQL execution API

All metadata providers inherit the following methods to support asynchronous 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 asynchronously execute a unidirectional, read-only 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 require some more programming efforts. This method returns the IacAsyncSql interface which lets tune various query properties before its execution.

All asynchronous SQL execution methods require a database connection to execute a query against it. If you omit the AConnection parameter, a new copy of connection is created internally and freed together with the SqlTask object.

A copy of connection 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 its proper functioning. For example, you might need to specify database password that has not initially defined in connection properties. To fix this, you can set 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;

The Code sample

Below is the sample code of a unit that implements asynchronous 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?