Command Batches (FireDAC)
Go Up to Working with Commands (FireDAC)
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
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:
|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.