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;