Open SQL Stored Procedure Examples

SourcePro : DB Access Module for PostgreSQL User’s Guide : Technical Information : Open SQL and PostgreSQL : Open SQL Examples : Open SQL Stored Procedure Examples

Example 9 – Executing a stored function using an RWDBOSql object (example 1)

This example shows how to invoke a stored function EmpInDept, using an RWDBOSql object, and fetch its result sets.

CREATE FUNCTION EmpInDept(INT) RETURNS SETOF REFCURSOR AS '

DECLARE employees REFCURSOR;

DECLARE salaries REFCURSOR;

BEGIN

OPEN employees FOR SELECT empno, ename FROM emp WHERE emp.dept = $1;

OPEN salaries FOR SELECT MIN(sal), AVG(sal), MAX(sal)

FROM emp WHERE emp.dept = $1;

RETURN NEXT employees;

RETURN NEXT salaries;

RETURN;

END; ' LANGUAGE 'plpgsql';

The stored function takes a department number as an input 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 ("SELECT * FROM EmpInDept($1);", RWDBOSql::Procedure); //1

RWDBTBuffer<int> dept; //2

openSql << dept; //3

dept[0] = 2; //4

cn.beginTransaction(); //5

openSql.execute(cn); //6

const size_t NUMBER_OF_ROWS = 10;

RWDBTBuffer<int> empNums (NUMBER_OF_ROWS);

RWDBTBuffer<RWCString> empNames (NUMBER_OF_ROWS); //7

openSql[0] >> empNums >> empNames; //8

long count = 0;

while ((count = openSql.fetch().rowsFetched()) > 0 ) { //9

for (int row = 0; row < count; row++) {

std::cout << "Employee: " << empNums[row] << ", "

<< empNames[row] << std::endl; //10

}

RWDBTBuffer<RWDecimalPortable> minBuff, avgBuff, maxBuff; //11

openSql[1] >> minBuff >> avgBuff >> maxBuff; //12

openSql.fetch(); //13

std::cout << "\nMinimum Salary: " << minBuff[0]

<< "\nAverage Salary: " << avgBuff[0]

<< "\nMaximum Salary: " << maxBuff[0]

<< std::endl; //14

cn.commitTransaction(); //15

//1 Creates an RWDBOSql object and passes in the SELECT statement to invoke the stored function EmpInDept. A placeholder is used to indicate the in parameter to be bound before the stored function can be executed. Note the hint RWDBOSql::Procedure.

//2 Creates parameter buffer dept of type int with a single entry.

//3 Binds the buffer to the openSql object.

//4 Sets the department number to be queried in the single entry in the dept buffer.

//5 Starts a transaction, turning off the default auto-commit mode. Reference cursors returned by a PostgreSQL stored function remain open until the end of the transaction within which they are invoked. However, in auto-commit mode, they are immediately closed; hence an explicit transaction is required to access the result sets returned by the reference cursors.

//6 Executes the openSql object, which invokes the stored function using the department number defined in the dept buffer.

//7 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.

//8 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.

//9 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.

//10 Accesses data using the index operator on the buffers empNums and empNames.

//11 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.

//12 Binds the buffers, in sequence, to the second result set of the openSql object, indicated by the index of 1.

//13 Fetches the values in the bound buffers. Because a single row is expected from the fetch, no while loop is used.

//14 Accesses data using the index operator on the buffers. Because they are single entry buffers, the only index used is 0.

//15 Commits the transaction as we are finished fetching the results from the reference cursors.

Example 10 – Executing a stored function using an RWDBOSql object (example 2)

This example shows how to execute the stored function shown below that takes a varchar and an integer as arguments and returns a bool.

CREATE OR REPLACE FUNCTION add_employee(VARCHAR, INTEGER) RETURNS BOOL AS '

DECLARE

empname alias for $1;

payscale alias for $2;

foundname varchar;

BEGIN

select into foundname name from employee WHERE name = empname;

IF NOT FOUND THEN

insert into employee values(empname, payscale);

RETURN true;

END IF;

RETURN false;

END;

' LANGUAGE 'plpgsql';

To execute this procedure, we can use the PostgreSQL select statement:

select * from add_employee('new employee', 10);

Here's the code:

RWDBOSql osql("select * from add_employee($1, $2);",RWDBOSql::Procedure);

RWCString name('new employee');

RWDBTBuffer<RWCString> inputNameBuffer(&name, 1);

int inputInt = 10;

RWDBTBuffer<int> inputIntBuffer(&inputInt,1); //1

RWCString output;

RWDBTBuffer<RWCString> outputbuffer(&output, 1); //2

osql << inputIntBuffer << inputNameBuffer; //3

osql[0] >> outputbuffer; //4

osql.execute(cn); // Execute the stored function

long rows;

while ((rows=osql.fetch().rowsFetched())>0) { //5

cout << outputbuffer[0] << endl;

}

//1 Creates a buffer for the input parameter.

//2 Creates a buffer for the return value; in this case, one row of a boolean value.

//3 Associates an input buffer for the input parameter.

//4 Associates the output buffer with the osql object. We expect one result set containing one column for this stored function execution, so this output buffer is bound to the first result set indicated by the index 0.

//5 Processes the return value from execution of the stored function.