Executing a Stored Procedure

The DB Access Module for PostgreSQL uses the "select * from procName(<args>)" SQL syntax to execute stored procedures. Result sets composed of complex types written in the SQL stored procedure language are not supported. However, the refcursor type in the PL/pgSQL language is supported. Execution is accomplished by using the above syntax along with the SQL statement FETCH to retrieve the results. The return value of the stored procedure is fetched as part of the result set. Here is an example executing the procedure created in Example 245.

Example 246. Executing a stored procedure

RWDBStoredProc add_employee = aDb.storedProc("add_employee");

RWCString newname("Sarah");

int payscale = 5;

add_employee << newname << payscale;

RWDBTable results = add_employee.execute().table();

RWDBReader rdr = results.reader();

while (rdr()) {

// save or display results

}

Because the module does not support retrieving parameter names, binding procedure parameters by name using RWDBStoredProc::operator[] (const RWCString &paramName) is not supported.