Calling (executing) a stored procedure (function) requires the following steps:
|Step 1.||Initialize stored procedure command|
|Step 2.||Bind input parameters|
|Step 3.||Execute stored procedure|
|Step 4.||Process output|
Let's say we want to call a procedure
TestProc with two numeric parameters
n1 is input parameter and
n2 is input/output parameter. Procedure will add
n2 and assign the result to
n2 = n1+n2).
Initialize Stored Procedure Command
As usual, the first thing to do is to create the objects. To execute a stored procedure we need two objects: SAConnection (connection object) and SACommand (command object):
SACommand cmd(&con, "TestProc");
The line above creates a command object
cmd based on previously created and connected connection object
con (for initializing
con object see Connect to database). Second parameter is the name of the required procedure. Required procedure can also be provided using SACommand::setCommandText method:
SACommand cmd(&con); cmd.setCommandText(_TSA("TestProc"));
Bind Input Parameters
After parameters are created (automatically by the library or manually) they all initialized to null values. There are two methods of assigning values to them.
Using ordinary assignment:
cmd.Param(_TSA("n1")).setAsLong() = 5L; cmd.Param(_TSA("n2")).setAsLong() = 10L;
Using stream binding operator:
cmd << SAPos(_TSA("n1")) << 5L << SAPos(_TSA("n2")) << 10L;
Execute Stored Procedure
To actually execute a procedure call SACommand::Execute method:
If stored procedure does not generate result set(s) you can reach output parameters immediately after calling SACommand::Execute:
After the statement has been executed, SQLAPI++ stores the returned values of output parameters in the SAParam objects bound to those parameters. On some servers these returned values are not guaranteed to be set until all results returned by the procedure have been fetched (using SACommand::FetchNext method). See Server specific information on output parameters availability on different DBMSs.
For processing result set(s), if any, see Fetch result set(s).
Steps 2 to 4 can be repeated if you need to call procedure several times.