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 say little about stored procedures, so stored procedure implementations vary widely among RDBMS vendors. If an
RWDBStoredProc feature is not supported by the underlying database, the DB Interface Module reports an
RWDBStatus::notSupported error.
NOTE >> Be sure to read the stored procedures section of your Rogue Wave Access Module documentation to determine the full capabilities of your implementation.
Instantiating RWDBStoredProc
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 objects of the DB Interface Module.
NOTE >> Unlike most objects of the DB Interface Module, 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;
Type and Error Checking in RWDBStoredProc
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, the DB Interface Module passes along whatever the database reports.
The DB Interface Module 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, the DB Interface Module 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
squareRoot.fetchReturnParams(); //4
RWDBValue retValue = squareRoot.returnValue(); //5
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. On
//4, a call to
fetchReturnParams() ensures the
OUT and
IN/OUT parameters are fetched. Starting on
//5, we check the procedure's return value. If the return value is zero, the result of the computation is in the variable
y.
Complex Stored Procedure Example
As an example of a stored procedure that does multiple
SELECT statements, let’s consider the Sybase system 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.
To use this code, the <ver> placeholder in any library names would need to be replaced with the actual digits representing the library version number.
Example 5 – Unknown results from stored procedure
// Example Program 5 - Unknown results from stored procedure
#include <rw/rstream.h>
#include <rw/db/db.h>
void display_table(const RWDBTable& table) {
RWDBReader rdr = table.reader();
size_t numCols = table.numberOfColumns();
RWDBValue v;
while (rdr()) {
for (size_t i = 0; i < numCols; i++) {
rdr >> v;
std::cout << v.asString() << "\t";
}
std::cout << std::endl;
}
}
int
main() {
RWDBDatabase myDbase = RWDBManager::database(
"libctl<ver>12d.so", // Access Module name
"SYBASE100", // server name
"user", // user name
"pwd", // password
"DEMO_DB" // database name
);
RWDBConnection session1 = myDbase.connection();
RWCString procName("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()) {
std::cout << std::endl << "TABLE " << ++i << std::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.