An Open SQL Stored Procedure Example
This example shows how to invoke a stored procedure, EmpInDept, using an RWDBOSql object, fetch result sets and fetch output parameters.
 
CREATE PROCEDURE EmpInDept(@deptno INT, @empcount INT OUT)
AS
SELECT @empcount = COUNT(*) FROM emp WHERE emp.dept = @deptno
SELECT empno, ename FROM emp WHERE emp.dept = @deptno
SELECT MIN(sal), AVG(sal), MAX(sal) FROM emp WHERE emp.dept = @deptno
The stored procedure takes a department number as an input parameter and provides the count of employees in that department as an output parameter. It produces two result sets: the first returns a list of employee number and name for each employee in that department; the second result set returns a single row with three columns containing the minimum, average and maximum salary of employees in that department.
 
RWDBOSql openSql ("{CALL EmpInDept(?, ?) }", RWDBOSql::Procedure); //1
 
RWDBTBuffer<int> dept, empCount; //2
empCount.paramType(RWDBColumn::outParameter); //3
 
openSql << dept << empCount; //4
 
dept[0] = 2; //5
openSql.execute(cn); //6
 
const size_t NUMBER_OF_ROWS = 10;
RWDBTBuffer<int> empNums (NUMBER_OF_ROWS); //7
RWDBTBuffer<RWCString> empNames (NUMBER_OF_ROWS); //8
 
openSql[0] >> empNums >> empNames; //9
 
long count = 0;
while ((count = openSql.fetch().rowsFetched()) > 0 ) { //10
for (int row = 0; row < count; row++) {
std::cout << "Employee: " << empNums[row] << ", "
<< empNames[row] << std::endl; //11
}
}
 
RWDBTBuffer<RWDecimalPortable> minBuff, avgBuff, maxBuff; //12
 
openSql[1] >> minBuff >> avgBuff >> maxBuff; //13
 
openSql.fetch(); //14
std::cout << "\nMinimum Salary: " << minBuff[0]
<< "\nAverage Salary: " << avgBuff[0]
<< "\nMaximum Salary: " << maxBuff[0]
<< std::endl; //15
 
openSql.fetchReturnParams(); //16
std::cout << "Number of Employees in dept " << dept[0] << ": "
<< empCount[0] << std::endl; //17
//1 Creates an RWDBOSql object and passes in the CALL statement to invoke the stored procedure EmpInDept. Two placeholders are used to indicate the in and out parameters to be bound before the stored procedure can be executed. Note the hint provided as RWDBOSql::Procedure.
//2 Creates parameter buffers, dept and empCount, both of type int. Both buffers contain a single entry.
//3 Sets the parameter type of empCount buffer as out parameter, as it will be bound to the stored procedure OUT parameter.
//4 Binds the buffers to the openSql object in the same sequence as the parameters are defined in the stored procedure.
//5 Sets the department number to be queried in the single entry in the dept buffer.
//6 Executes the openSql object, which invokes the stored procedure using the department number defined in the dept buffer.
//7-8 Defines output buffers empNums and empNames for the first result set. They are defined of type int and RWCString to match the datatypes of employee number and employee name. Each buffer has 10 entries enabling at most 10 rows to be fetched in a single fetch() call.
//9 Uses the RWDBOSql index operator to bind the output buffers to the first result set of the openSql object, indicated by index 0. The buffers are bound in the same sequence as columns in the result set.
//10 Uses a while loop to fetch rows from the first result set in the empNums and empNames buffers until all rows are fetched, indicated when fetch() returns 0 rows. The next call to fetch() will fetch the second result set.
//11 Accesses data using the index operator on the buffers empNums and empNames.
//12 Defines three buffers for the second result set, one for each column in the set — minimum, average, and maximum salary. Each buffer is created with a single entry, as a single row is expected.
//13 Binds the buffers, in sequence, to the second result set of the openSql object, indicated by index of 1.
//14 Fetches the values in the bound buffers. Because a single row is expected from the fetch, no while loop is used.
//15 Accesses data using the index operator on the buffers. Because they are single entry buffers, the only index used is 0.
//16 Fetches the OUT parameters of the stored procedure. The OUT parameter buffer, empCount, now has the output value produced by the stored procedure. Note that this call is made after completely processing the returned result sets. Please see Sequence of Fetching Result Sets and Out Parameters for more information on fetching output parameters after processing result sets.
//17 Accesses the value using the index operator on the buffer empCount.