Command Batches (FireDAC)

Go Up to Working with Commands (FireDAC)

General

A Command Batch is a group of SQL statements sent at one time from an application to a DBMS for execution. DBMS compiles the statements of a batch into a single execution plan. The statements in the execution plan are then executed one at a time. This minimizes network traffic and server workload. After the execution, DBMS returns to the client result sets produced by the commands.

Processing Result Sets

FireDAC allows you to process all the result sets one by one, using the NextRecordSet method. To enable the processing of all the result sets, you should set FetchOptions.AutoClose to False before executing a command. Otherwise, right after reaching Eof at the first cursor, it will be closed and other cursors will be discarded.

This is an example with SQL Server:

FDQuery1.FetchOptions.AutoClose := False;
FDQuery1.SQL.Add('select * from [Orders]');
FDQuery1.SQL.Add('select * from [Order Details]');
FDQuery1.Open; // [Orders] table rows are accessable here
FDQuery1.NextRecordSet; // [Order Details] table rows available here

An application can store each result set into a separate dataset, using the TFDMemTable and Data property. For example:

FDQuery1.FetchOptions.AutoClose := False;
FDQuery1.SQL.Text := 'select * from orders; select * from customers';

FDQuery1.Open;
FDQuery1.FetchAll;
// assign orders records to FDMemTable1
FDMemTable1.Data := FDQuery1.Data;

FDQuery1.NextRecordSet;
FDQuery1.FetchAll;
// assign customers records to FDMemTable2
FDMemTable2.Data := FDQuery1.Data;

FireDAC automatically skips empty result sets that do not have columns and rows.

DMBS and Batches

A DBMS must support command batches that are executed using FireDAC. If a DBMS does not support batches, you can use the SQL scripting. Here are the DBMS supporting batches:

DBMS	Notes
IBM DB2	The commands must be separated by ';'.
Firebird	Use the EXECUTE BLOCK construction.
Informix	The commands must be separated by ';'. The batch may contain only single DB accessing command.
Microsoft SQL Server	Optionally, the commands can be separated by ';'.
MySQL	The commands must be separated by ';'.
Oracle	Use the BEGIN END anonymous block construction.
PostgreSQL	The commands must be separated by ';'. A batch without parameters can be executed with ResourceOptions.DirectExecute set to True. With PostgreSQL v >= 9.0, use the DO BEGIN END anonymous block construction.
SQLite	The commands must be separated by ';'.
SQL Anywhere	Optionally, the commands can be separated by ';'.
Teradata Database	The commands must be separated by ';'.

Also, FireDAC fully supports commands returning multiple result sets. Here are some examples:

• Oracle stored procedures with REF CURSOR's.
• Oracle result sets with nested cursors.
• PostgreSQL varieties of cursor-returning statements.

Note: To execute multiple INSERT/UPDATE/DELETE commands, consider using the Array DML feature, which is far more effective for large batches.

See Also

• Array DML

Samples

• FireDAC TFDQuery Batch sample
• FireDAC TFDQuery Batch Error Handling sample
• FireDAC IFDPhysCommand Batch sample