Home How To Online Documentation Support Download Order
 

 

 

Connecting to databases
Transactions
Executing an SQL command
Executing stored procedures
Binding input parameters
Fetching result set
Fetching multiple result sets
Optimizing Database Performance
Returning output parameters
Working with Long or Lob(CLob, BLob) Data
Working with date/time values
Error handling
Using native API
Query examples


Executing stored procedures

Calling (executing) a stored procedure (function) requires the following steps:

  1. Create a command object and pass stored procedure name to it.
  2. Bind input parameters
  3. Execute stored procedure
  4. Process output parameters and possibly result set

Suppose, we want to call a procedure TestProc with two numeric parameters n1 and n2 where n1 is input parameter and n2 is input/output parameter. Procedure will add n1 and n2 and assign the result to n2 (n2 = n1+n2 ).

Step 1. Creating a command object and passing stored procedure name to it.

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(&Connection, "TestProc");

The line above creates a command object cmd based on previously created and connected connection object Connection (for creating and connection Connection object see Connecting to databases). Second parameter is the name of the required procedure. Required procedure can also be provided using SACommand::setCommandText method.

SACommand cmd(&Connection);
cmd.setCommandText("TestProc");

 

Step 2. Binding 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.

First: using ordinary assignment:

cmd.Param("n1").setAsLong() = 5;
cmd.Param("n2").setAsLong() = 10;

Second: using stream binding operator:

cmd << SAPos("n1") << 5 << SAPos("n2") << 10;

 

Step 3. Executing stored procedure

To actually execute a procedure call SACommand::Execute method.

cmd.Execute();

 

Step 4. Processing output parameters and possibly result set(s)

If stored procedure does not generate result set(s) you can reach output parameters immediately after calling SACommand::Execute :

printf("%d\n", cmd.Param("n2").asLong());

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 Fetching result set and Fetching multiple result sets.

That is all there is to it. Steps 2-4 can be repeated if you need to call procedure several times.

 

Problems and Questions

If you haven't found the answer to your questions or have some problems on using the Library, please, send e-mail to howto@sqlapi.com.