Executing Stored Procedures (FireDAC)
Go Up to Working with Commands (FireDAC)
Contents
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';
FDStoredProc1.Prepare;
// 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.
- Note:
- 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];
FDStoredProc1.Command.FillParams(FDStoredProc1.Params);
Or do this, by using:
FDStoredProc1.StoredProcName := 'my_proc';
FDStoredProc1.FetchOptions.Items := FDStoredProc1.FetchOptions.Items - [fiMeta];
with FDStorecProc1.Params do begin
Clear;
with Add do begin
Name := 'Par1';
ParamType := ptInput;
DataType := ftString;
Size := 50;
end;
with Add do begin
Name := 'Par2';
ParamType := ptOutput;
DataType := ftInteger;
end;
end;
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.Prepare;
FDStoredProc1.Params[0].Value := 100;
FDStoredProc1.Params[1].Value := 'audi';
FDStoredProc1.ExecProc;
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
Clear;
Add('begin');
Add(' sys.dbms_sql.bind_variable(:c, :name, :value');
Add('end;');
end;
FDQuery1.Params[0].AsInteger := 1;
FDQuery1.Params[1].AsString := 'p1';
FDQuery1.Params[2].AsInteger := 100;
FDQuery1.ExecSQL;
Using TFDCommand
Finally, you can use TFDCommand to execute a stored procedure. Most of the above specifications can be applied also to TFDCommand.