|
Executing Stored Procedures |
|
|
|
Executing Stored Procedures |
|
This topic describes approaches for executing stored procedures with UniDAC.
Stored procedures in UniDAC can be executed with one of the following components: TUniConnection, TUniSQL, TUniQuery, TUniStoredProc. Below you will find the description of working with stored procedure using these components starting with the simplest approach.
The simplest way to execute a stored procedure is the TUniConnection component, but it has several limitations. TUniConnection does not have properties like SQL, StoredProcName, or Params. So you will need to provide stored procedure name and parameter values each time you need to execute it. TUniConnection does not support output parameters, however you can get a result parameter from a function. Also TUniConnection does not support preparation. Stored procedures are executed with the ExecProc and ExecProcEx methods.
Therefore, if you need to execute a stored procedure that returns neither record set nor output parameters only once, the TUniConnection component is an optimal choice.
TUniSQL is a separate component dedicated to execute commands that do not return record sets. It has no data storage, therefore it consumes a bit less memory than TUniQuery or TUniStoredProc and works a bit faster. To execute a stored procedure, an appropriate command must be assigned to the SQL property of TUniSQL. It can be assigned manually, or created with the CreateProcCall method.
The CreateProcCall method accepts a stored procedure name, gets the description of a stored procedure from the server, and generates SQL command with parameters. The generated command is automatically assigned to the SQL property. Parameters can be accessed both at design time and run time using properties such as Params, ParamByName, etc.
Comparing to the previous method of stored procedures execution, TUniSQL supports all kinds of parameters (INPUT, OUTPUT, etc.).For repeatable executions of a stored procedure, you do not need to pass a SQL command on each execution. It is stored in the SQL property.
Each command of TUniSQL can be prepared. In some cases preparation improves performance of execution.
TUniSQL is a powerful component that is an appropriate choice for a stored procedure that does not return result sets, needs to be executed multiple times, or returns output parameters.
One more component that lets you execute stored procedures is TUniQuery. In addition to the abilities provided by TUniSQL, TUniQuery allows to obtain record sets from stored procedures and modify them. If a stored procedure returns multiple record sets, all of them can be accessed sequentially. The Open method opens the first record set. The OpenNext method closes the current record set and opens the next one. If the server has sent enough metainformation about the query, obtained dataset will be editable. Otherwise to get an editable dataset you should setup properties such as SQLDelete, SQLInsert, and others properly.
The TUniQuery is a good choice for executing stored procedures that return record sets.
TUniStoredProc is a component designed specially for working with stored procedures. If you want to execute a stored procedure, just assign its name to the StoredProcName property, call PrepareSQL to describe parameters, assign parameter values, and call Execute. If the stored procedure has no input or input/output parameters to be assigned, call to the PrepareSQL method is not necessary. Other than that TUniStoredProc is similar to TUniQuery. It supports result sets, output parameters, preparation, and can be initialized by the CreateProcCall method.
TUniStoredProc is the most convenient component for working with stored procedures that covers all necessary functionality.
There are several notes concerning parameters of stored procedures.
UniDAC supports four parameter types: input, output, input/output, and result.
TUniConnection can pass values of the input parameters to the server, and get the result value from a function. If a parameter value is not assigned, the default value will be provided if possible. If an unassigned parameter has no default value, an error will be raised.
TUniSQL, TUniQuery, and TUniStoredProc components can handle all of these parameter types. If an input parameter value is not assigned with one of these components, the NULL value will be passed as a parameter value. Assigning of output and result parameter values has no effect as they are not passed to the server on execution, and after execute they will be replaced with values returned from the server.
Some stored procedures may have default values for parameters. If you want to pass a default parameter value to a stored procedure, you should do the following:
If a parameter value in TUniSQL, TUniQuery, or TUniStoredProc is not assigned or cleared, the NULL value will be passed as a parameter value. It is not the same as assigning a default value.