Asynchronous Execution (FireDAC)

Go Up to Working with Commands (FireDAC)

General

A programmer may choose between four operation modes using the ResourceOptions.CmdExecMode property:

Mode	Description
amBlocking	The calling thread and GUI are blocked until an action is finished.
amNonBlocking	The calling thread is blocked until an action is finished. The GUI is not blocked.
amCancelDialog	The calling thread and GUI are blocked until an action is finished. FireDAC shows a dialog, allowing to cancel an action.
amAsync	The calling thread and GUI are not blocked. The called method returns immediately.

An application checks TFDCommand.State or TFDAdaptedDataSet.Command.State for an operation status:

Status	Description
csInactive	A command is not prepared.
csPrepared	A command is prepared. A result set is not accessible.
csExecuting	A command execution is in progress.
csOpen	A command execution is finished. A result set is accessible and not yet fully fetched.
csFetching	A result set fetching is in progress.
csAborting	A command execution abortion is in progress.

For example:

FDQuery1.ResourceOptions.CmdExecMode := amAsync;
FDQuery1.Open;
while FDQuery1.Command.State = csExecuting do begin
  // do something while query is executing
end;

In the amCancelDialog mode, a user is notified about long-running operations and has the ability to cancel the operation by using the TFDGUIxAsyncExecuteDialog component:

To use the dialog component, drop it on a form. No additional setup is required. Read more about modes and operation notification events in the ResourceOptions.CmdExecMode property description.

Asynchronous Open and Fetching

When an application needs to open a query asynchronously (amAsync) and the query is bound to the GUI using TDataSource, the TDataSource must be disconnected from the query before opening it, and connected back after opening the query. For example:

procedure TForm1.FDQuery1BeforeOpen(DataSet: TDataSet);
begin
  DataSource1.DataSet := nil;
end;

procedure TForm1.FDQuery1AfterOpen(DataSet: TDataSet);
begin
  DataSource1.DataSet := FDQuery1;
  FDQuery1.ResourceOptions.CmdExecMode := amBlocking;
end;

FDQuery1.BeforeOpen := FDQuery1BeforeOpen;
FDQuery1.AfterOpen := FDQuery1AfterOpen;
FDQuery1.ResourceOptions.CmdExecMode := amAsync;
FDQuery1.Open;

This is not required when amCancelDialog is used. Also, fetching with GUI cannot be performed in amAsync mode. To open a query and fetch large result sets asynchronously, set FetchOptions.Mode to fmAll. Then both operations will be performed as a single background task.

Note: This process is only valid when you use a bidirectional dataset.

To asynchronously execute the Firebird query, use a separated transaction for this query.

Cancelling Long-Running Operations

A programmer can specify the timeout for the data access operation using the ResourceOptions.CmdExecTimeout property. When an operation execution requires more than the specified time, the execution is cancelled and an exception is raised. To catch a command timeout, use the following code:

try
  // set timeout to 5 seconds
  FDQuery1.ResourceOptions.CmdExecTimeout := 5000;
  FDQuery1.ExecSQL;
except
  on E: EFDDBEngineException do
    if E.Kind = ekCmdAborted then
      ; // command is aborted
end;

Alternatively, the application cancels the operation execution by calling the dataset or the command AbortJob method from other thread, or the connection AbortJob method to cancel all the operations on this connection.

Note: Not all FireDAC drivers and DBMS are supporting execution cancellation. Also, the cancellation cannot be performed immediately, when a DBMS is executing some critical operations. The following table lists supported combinations:

DBMS	Notes
Advantage
IBM DB2
Firebird	v 2.5 or higher
Informix
InterBase	v 7.0 or higher
MySQL	v 5.0 or higher
Oracle
PostgreSQL
SQL Anywhere
SQL Server
SQLite
Teradata Database

Multiple Asynchronous Queries

The execution of the multiple asynchronous queries in parallel is not supported by FireDAC, due to the general multithreading principle. To workaround this issue, consider the following options:

• Group several queries into a stored procedure and call the procedure asynchronously.
• Use a dedicated connection for each asynchronous query running in parallel.
• Launch the next asynchronous query after the previous one is finished. For this, use the AfterOpen and AfterExecute event handlers.
• Finally, the application can create its own threads and execute the DB tasks in these threads.

Samples

• FireDAC TFDQuery Async sample
• FireDAC IFDPhysCommand Async sample