Stored Procedures

DB Access Module for DB2 CLI User’s Guide : Chapter 2 Technical Information : Stored Procedures

Stored Procedures

Stored procedures are encapsulated by class RWDBStoredProc. The implementation of stored procedures by the DB Access Module for DB2 CLI provides full support for DB2 stored procedures. This includes support for:

• creating or registering stored procedures

• retrieving stored procedure text and parameter definitions

• executing stored procedures

• processing multiple result sets, output parameters, and return values

DB2 CLI supports stored procedures written in the SQL language as well as external procedures written in other languages. Please see the DB2 CLI documentation for details regarding stored procedures written in languages other than SQL.

Creating Stored Procedures

Stored procedures can be created by calling the createProcedure() method of RWDBDatabase. The DB Interface Module creates the parameter list from a schema you supply, while the body of the stored procedure is supplied by the application. Here is a simple SQL procedure example, which returns the average employee salary as an output parameter, returns the total of all salaries as a return value, and returns a table of all the employees whose salary is greater than the average:

RWCString body =

"LANGUAGE SQL \n"

"DYNAMIC RESULT SETS 1 \n"

"BEGIN \n"

"DECLARE entries INTEGER; \n"

"DECLARE counter INTEGER default 0; \n"

"DECLARE total DOUBLE default 0.0; \n"

"DECLARE aSalary DOUBLE default 0.0; \n"

"DECLARE c1 CURSOR FOR SELECT salary FROM employees; \n"

"DECLARE c2 CURSOR WITH RETURN TO CALLER FOR \n"

" SELECT * FROM employees WHERE salary > aSalary; \n"

"SELECT COUNT(*) INTO entries FROM employees; \n"

"OPEN c1; \n"

"WHILE counter < entries \n"

" DO FETCH c1 INTO aSalary; \n"

" SET total = total + aSalary; \n"

" SET counter = counter + 1; \n"

"END WHILE; \n"

"IF entries = 0 \n"

" THEN SET average = 0; \n"

"ELSE \n"

" SET average = total / entries; \n"

" SET aSalary = average; \n"

"END IF; \n"

"OPEN c2; \n"

"RETURN CAST(total as INTEGER); \n"

"END \n";

RWDBSchema params;

RWDBColumn aParameter;

aParameter.name("average").type(RWDBValue::Double);

aParameter.paramType(RWDBColumn::outParameter);

params.appendColumn(aParameter);

aDB.createProcedure("averageSalary", body, params, conn);

The createProcedure() method uses the DB2 SQL statement CREATE PROCEDURE. For this reason, it can also register stored procedures with the application server when they are written in languages other than SQL. Please see the DB2 documentation for more information.

Executing Stored Procedures

DB2 stored procedures can be written in several different languages. Once a stored procedure is registered with the server, a client application can execute it. Stored procedures are executed using the CLI CALL statement. An application has the option of processing output parameters, a return value, and/or the result sets returned by the procedure.

Here's an example of processing all the different parts of the stored procedure. We'll use the example procedure shown in “Creating Stored Procedures.”

RWDBStoredProc avg = aDB.storedProc("averageSalary", conn);

double averageSalary;

avg << &averageSalary;

RWDBTable aboveAverageSalaries = avg.execute(conn).table();

RWDBReader reader = aboveAverageSalaries.reader(conn);

RWDBValue name, title, salary;

while (reader()) {

//save or display the rows

reader >> name >> title >> salary;

cout << name.asString() << "\t" << title.asString()

<< "\t" << salary.asDouble() << endl;

}

avg.fetchReturnParams();

// save or display averageSalary

cout << "averageSalary" << averageSalary << endl;

RWDBValue returnValue = avg.returnValue();

// save or display the return value

cout << "returnValue" << returnValue.asInt() << endl;

Instantiating an RWDBStoredProc Using Schema Data

The member functions:

RWDBDatabase::storedProc(RWCString, RWDBSchema, RWDBColumn)

RWDBDatabase::storedProc(RWCString, RWDBConnection, RWDBSchema,

RWDBColumn)

increase the performance of stored procedures by eliminating the usual parameter fetch required for instantiation. The schema must include the RWDBColumn::ParamType of each parameter in the stored procedure. The column is ignored as all SQL procedures in DB2 CLI return an integer. For example, the code in “Creating Stored Procedures” uses the schema params to create the stored procedure; params could also be used to instantiate an RWDBStoredProc.