An Open SQL Stored Procedure Example
This example shows how to use an
RWDBOSql object to invoke stored procedure
EmpInDept, fetch result sets, and fetch output parameters.
CREATE PROCEDURE EmpInDept(IN deptno INT, OUT empcount INT)
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
SELECT empno, ename FROM emp WHERE emp.dept = deptno;
DECLARE c2 CURSOR WITH RETURN TO CALLER FOR
SELECT MIN(sal), AVG(sal), MAX(sal) FROM emp WHERE emp.dept = deptno;
SELECT COUNT(*) INTO empcount FROM emp WHERE emp.dept = deptno;
OPEN c1;
OPEN c2;
END
The stored procedure takes a department number as an input parameter and returns the count of employees in that department as an output parameter. It produces two result sets: the first returns a list of employee numbers and names 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
