4.10 Stored Procedures

Many modern RDBMS implementations include a mechanism to enforce database policy through stored procedures. Database administrators may require users to do much of their data manipulation through stored procedures in order to restrict access to the underlying tables.

Class RWDBStoredProc encapsulates database stored procedures. Class RWDBStoredProc supports creating and deleting stored procedures, retrieving stored procedure definitions, executing stored procedures, and processing the results returned from these executions. Parameters may be passed to an RWDBStoredProc prior to execution, and the values of output parameters may be retrieved. If a stored procedure can return multiple sets of results, RWDBStoredProc can also access each result set in turn.

Unfortunately, the standards bodies have had little to say about stored procedures, so stored procedure implementations vary widely among RDBMS vendors. If an RWDBStoredProc feature is not supported by the underlying database, DBTools.h++ reports an RWDBStatus::notSupported error.

NOTE: Be sure to read the stored procedures section of your Rogue Wave access library documentation to determine the full capabilities of your implementation.

RWDBStoredProc instances are obtained from RWDBDatabase instances by invoking the latter's storedProc() method with the stored procedure's name. RWDBStoredProc contains an RWDBSchema to store information about its formal parameters. This information is necessary for the library to successfully match actual arguments supplied by your application with the procedure's formal parameter list. The schema information is fetched automatically whenever an RWDBStoredProc is instantiated, so instantiating an RWDBStoredProc is a more expensive proposition than instantiating most DBTools.h++ objects.

NOTE: Unlike most DBTools.h++ objects, instantiation of a stored procedure object requires database access.

Since the storedProc() method of RWDBDatabase involves a database access, it has a variant with which you can specify the connection to use. If only the procedure name is provided, a default connection is used.

The insertion operator << is used to pass actual parameters to an RWDBStoredProc. Use it to insert actual values if the stored procedure expects IN parameters, or to insert pointers to values if the stored procedure expects OUT or IN/OUT parameters and your application needs to obtain results through the parameters. It is an error to insert a NULL pointer; if you do, the status of RWDBStoredProc changes to RWDBStatus::nullReference. A NULL value can be inserted using rwdbNull.

The indexing operator [ ] can be used to access a particular parameter position by number or by name. For the stored procedure myStoredProc, which expects the parameters number and name in that order, the following notations are equivalent:

myStoredProc << 1 << "Hello, world";

myStoredProc[0] << 1;
myStoredProc[1] << "Hello, world";

myStoredProc[1] << "Hello, world";
myStoredProc[0] << 1;

myStoredProc["name"] << "Hello, world";
myStoredProc["number"] << 1;

RWDBStoredProc does not check actual parameters for type; instead, it allows the database to do type conversion where possible. If there is a type incompatibility, DBTools.h++ passes along whatever the database reports.

DBTools.h++ produces an RWDBStatus::invalidPosition error if too many arguments are inserted into an RWDBStoredProc. No check is made for too few arguments, since the underlying database may supply defaults. If it does not, DBTools.h++ passes along any errors reported by the database.

In the next example, suppose there is a database procedure called squareRoot that expects two parameters: an integer x, and a floating point number y. The procedure is supposed to compute the square root of x. If no error occurs, it places the result in y and returns 0; otherwise it returns -1 and leaves y unchanged.

RWDBStoredProc squareRoot = myDbase.storedProc("squareRoot");  //1
int x;
double y;
squareRoot << x << &y;                                         //2
squareRoot.execute(myConnection);                              //3
RWDBValue retValue = squareRoot.returnValue();                 //4
if (retValue.asInt() == 0) {
  cout << "Sqrt " << x << " = " << y << endl;
}
else {
  cout << "An error occurred" << endl;
}

On //1, an RWDBStoredProc instance is obtained from the database. This involves using a connection supplied by myDbase in order to query the database for information about the procedure's parameters.

The first argument to squareRoot is an IN parameter, and the second is an OUT parameter used for obtaining the data. The parameters are passed on //2. First the IN parameter is inserted, followed by the address of the OUT parameter.

The call to execute() on //3 causes the stored procedure to be executed by the database server. As usual, the execute() method returns an RWDBResult, which we have elected to ignore in this case. Starting on //4, we check the procedure's return value. If the return value is zero, the result of the computation is in the variable y.

As an example of a stored procedure that does multiple SELECT statements, let's consider the Sybase stored procedure sp_help, which displays information about database objects. This example also demonstrates how to use class RWDBValue to handle data without knowing its type.

// Example Program 5 - Unknown results from stored procedure
#include <rw/rstream.h>
#include <rw/db/db.h>
void display_table(RWDBTable& table) {
  RWDBReader rdr = table.reader();
  table.fetchSchema();
  int numCols = table.numberOfColumns();
  while (rdr()) {
    for (int i = 0; i < numCols; i++) {
      RWDBValue v;
      rdr >> v;
      cout << v.asString() << (i == numCols - 1 ? "\n" : "\t");
    }
  }
}

int 
main() {
  RWDBDatabase myDbase = RWDBManager::database(
          "libctl7d.so",   // access library name
          "SYBASE100",     // server name
          "dbxx",          // user name
          "demodb",        // password
          "DEMO_DB"        // database name
    );
  
  RWDBConnection session1 = myDbase.connection();
  RWCString procName("sybsystemprocs.dbo.sp_help");
  
  RWDBStoredProc sp_help = myDbase.storedProc(procName, session1);
  RWDBResult result = sp_help.execute(session1);
  RWDBTable table = result.table();
  int i = 0;
  while (table.isValid()) {
    cout << endl << "TABLE " << ++i << endl;
    display_table(table);
    table = result.table();
  }
  return 0;
}

In this example, the result of the execute() method is examined, rather than discarded. Each table in the result is passed to the display_table() routine, which simply converts tabular data to strings and prints them out. On our system, four tables of results are processed by this program.