Executing Stored Procedures (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Working with Commands (FireDAC)

Using TFDStoredProc

In general, TFDStoredProc may be set up at design time and/or at runtime. TFDStoredProc transparently generates a SQL command to call a stored procedure based on the TFDStoredProc property values. Before the execution, FireDAC sends the parameter values to a DBMS, then executes a stored procedure and receives the output parameter values.

Note: FireDAC does not support parameters with the default values.

Setting Stored Procedures at Design Time

To execute a stored procedure, drop a TFDStoredProc component on a form. TFDStoredProc.Connection will be automatically set to point to a TFDConnection on this form, if any.

Then, optionally set the CatalogName, SchemaName, and PackageName properties or choose their values from a dropdown list. After setting StoredProcName and when fiMeta is included into FetchOptions.Items, the Params collection is filled in automatically.

To unify parameter names, set ResourceOptions.UnifyParams to True. For example, this excludes the '@' prefix from the SQL Server stored procedure parameter names.

Setting Stored Procedures at Runtime

This is similar to the design time operation, you just need to use the following code:

FDStoredProc1.StoredProcName := 'my_proc';
// now the Params collection is filled in

The Prepare call fills the Params collection by using the mkProcArgs metadata, when fiMeta is included into FetchOptions.Items.

  • mkProcArgs querying can be time-consuming.
  • The application cannot change the parameter definitions after the Params is filled. For example, the assignment to TADParam.AsXxxx properties implicitly sets the parameter data type.

To avoid the issues detailed above, exclude fiMeta from FetchOptions.Items, so that the Params collection will not be automatically rebuilt when calling Prepare. Also, fill the Params collection manually, before calling Prepare or ExecProc, by using the following code:

FDStoredProc1.StoredProcName := 'my_proc';
FDStoredProc1.FetchOptions.Items := FDStoredProc1.FetchOptions.Items - [fiMeta];

Or do this, by using:

FDStoredProc1.StoredProcName := 'my_proc';
FDStoredProc1.FetchOptions.Items := FDStoredProc1.FetchOptions.Items - [fiMeta];
with FDStorecProc1.Params do begin
  with Add do begin
    Name := 'Par1';
    ParamType := ptInput;
    DataType := ftString;
    Size := 50;
  with Add do begin
    Name := 'Par2';
    ParamType := ptOutput;
    DataType := ftInteger;

Using Packaged Procedures

To use a packaged procedure, the application must specify the package name. For that, do one of the following:

  • Set PackageName--the package name can be specified in [<catalog name>.][<schema name>.]<package name> format.
  • Set StoredProcName--the stored procedure name can be specified in [<catalog name>.][<schema name>.]<package name>.<stored proc name> format.

To choose the overloaded procedure, the application must specify the Overload property. For example, on Oracle:

FDStoredProc1.PackageName := 'SYS.DBMS_SQL';
FDStoredProc1.Overload := 1;
FDStoredProc1.StoredProcName := 'BIND_VARIABLE';

Executing the Stored Procedure

To execute a stored procedure that does not return a result set, use the ExecProc method. To execute a stored function, use the ExecProc or ExecFunc methods, where ExecFunc returns the function value. If a stored procedure returns a result set, the "[FireDAC][Phys][Oracl]-310. Cannot execute command returning result sets" exception is raised.

Note: There are several overloaded ExecProc and ExecFunc methods, allowing you to avoid the TFDStoredProc property usage and specify all required information as method arguments. For example:
FDStoredProc1.StoredProcName := 'MY_PROC';
FDStoredProc1.Params[0].Value := 100;
FDStoredProc1.Params[1].Value := 'audi';

Or more compact:

FDStoredProc1.ExecProc('MY_PROC', [100, 'audi']);

To execute a stored procedure, returning a result set and open this result set, use the Open methods. If a stored procedure returns no result sets, the "[FireDAC][Phys][Oracl]-308. Cannot open / define command, which does not return result sets" is raised. If the stored procedure returns multiple result sets, check "Command Batches" for details.

Note: Stored procedures can be executed asynchronously.

Using TFDQuery

The main difference between TFDStoredProc and TFDQuery is that TFDStoredProc automatically generates a stored procedure call using the parameters information. The SQL code calling a stored procedure can be executed directly using any FireDAC method of the SQL command execution. For example, call an Oracle packaged proc using TFDQuery:

with FDQuery1.SQL do begin
  Add('  sys.dbms_sql.bind_variable(:c, :name, :value');
FDQuery1.Params[0].AsInteger := 1;
FDQuery1.Params[1].AsString := 'p1';
FDQuery1.Params[2].AsInteger := 100;

Using TFDCommand

Finally, you can use TFDCommand to execute a stored procedure. Most of the above specifications can be applied also to TFDCommand.

See Also