Command Batches (FireDAC)

From RAD Studio
Jump to: navigation, search

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 stored functions with RETURNS SETOF and/or OUT REFCURSOR arguments.
Note: To execute multiple INSERT/UPDATE/DELETE commands, consider using the Array DML feature, which is far more effective for large batches.

See Also

Samples