2.18 Open SQL and PostgreSQL
The alternative Open SQL interface can be used with PostgreSQL. This section provides some specific details and examples.
2.18.1 Placeholder Syntax
The placeholder syntax for PostgreSQL is a ”$” sign followed by an integer beginning with “1”:
$<n>
Here is an example:
INSERT INTO PICNICTABLE VALUES( $1, $2 )
Placeholders are not a portable feature. Different Access Modules use different placeholder syntax.
Please note that the DB Access Module for PostgreSQL does not support input binding of data. All values are sent as literals upon execution of the SQL statement.
2.18.2 RWDBDataCallback and Open SQL
The data callback classes are not supported in the DB Access Module for PostgreSQL. Attempts to use the data callbacks will result in an RWDBStatus::notSupported error.
2.18.3 Stored Procedures and Open SQL
PostgreSQL user-defined functions written in PL/pgSQL or SQL languages can be invoked with
RWDBOSql using
SELECT syntax.
RWDBOSql myOpenSqlObject("SELECT * FROM myStoredFunc($1, $2);", RWDBOSql::Procedure);
In this example, myStoredFunc is the name of the stored function, and $1 and $2 are placeholders for the parameters to be passed to the function. Note the hint provided as RWDBOSql::Procedure. The return value, if any, is returned as a result set.
Please see
Section 2.18.4.3 for an example that uses
RWDBOSql with stored procedures.
2.18.3.1 Out Parameters and Return Values
The DB Access Module for PostgreSQL does not currently support stored functions with OUT parameters. The return value from a stored function is received in the form of a result set. The function fetchReturnParams() is a no-op for this access module. However, to make your application portable across different access modules, it is recommended to call fetchReturnParams(), after completely processing the result sets.
2.18.4 Open SQL Examples
These examples illustrate how an
RWDBOSql object can be used to
All statements are based on an employee table
emp. The examples assume an error handler is associated with the connection in use, and don’t check for any errors after calls to the
RWDBOSql object.
The structure of the table emp is:
empno INT NOT NULL
ename VARCHAR(10) NOT NULL
posn VARCHAR(9) NOT NULL
mgr INT NULL
sal NUMERIC(7,2) NOT NULL
dept INT NOT NULL
2.18.4.1 An Open SQL Insert Example
Example 7 – Executing an INSERT statement using an RWDBOSql object
This example shows how to execute an
INSERT statement using
RWDBOSql to insert data in the
emp table.
const size_t NUM_OF_ROWS = 14; //1
RWDBTBuffer<int> empnoBuff(NUM_OF_ROWS), mgrBuff(NUM_OF_ROWS),
deptBuff(NUM_OF_ROWS);
RWDBTBuffer<RWCString> enameBuff(NUM_OF_ROWS), posnBuff(NUM_OF_ROWS);
RWDBTBuffer<RWDecimalPortable> salBuff(NUM_OF_ROWS); //2
empnoBuff[0] = 1;
enameBuff[0] = "ABC XYZ";
posnBuff[0] = "ADMIN";
mgrBuff[0] = 6;
salBuff[0] = "58000.00";
deptBuff[0] = 4; //3
// Populate rest of the rows
RWDBOSql openSql("INSERT INTO emp VALUES($1, $2, $3, $4, $5, $6)",
RWDBOSql::NonQuery); //4
openSql << empnoBuff << enameBuff << posnBuff
<< mgrBuff << salBuff << deptBuff; //5
openSql.execute(cn); //6
long rowsInserted = openSql.rowsAffected(); //7
std::cout << (openSql.isValid() ? "Data insertion successful." //8
: "Data insertion failed.") << std::endl;
if (rowsInserted >= 0) {
std::cout << "Inserted " << rowsInserted << " rows." << std::endl;
}
2.18.4.2 Open SQL Query Examples
Example 8 – Executing a SELECT query using an RWDBOSql object
These examples illustrate how an
RWDBOSql object can be used to execute
SELECT queries and retrieve their results. The
SELECT query is executed on table
emp and retrieves all columns of the table for employees in a particular department.
The first example assumes awareness of the structure of table
emp (
Section 2.18.4) and hence the schema of the result set returned by the query.
RWDBOSql openSql("SELECT * FROM emp WHERE dept = $1", RWDBOSql::Query); //1
RWDBTBuffer<int> queryDeptBuff; //2
queryDeptBuff[0] = 3; //3
openSql << queryDeptBuff; //4
openSql.execute(cn); //5
const size_t ROWS_TO_FETCH = 5; //6
RWDBTBuffer<int> empnoBuff(ROWS_TO_FETCH), mgrBuff(ROWS_TO_FETCH),
deptBuff(ROWS_TO_FETCH);
RWDBTBuffer<RWCString> enameBuff(ROWS_TO_FETCH), posnBuff(ROWS_TO_FETCH);
RWDBTBuffer<RWDecimalPortable> salBuff(ROWS_TO_FETCH); //7
openSql[0] >> empnoBuff >> enameBuff >> posnBuff >>
mgrBuff >> salBuff >> deptBuff; //8
long rowsFetched = 0;
while ((rowsFetched = openSql.fetch().rowsFetched()) > 0) { //9
for (size_t i = 0; i < rowsFetched; ++i) {
cout << empnoBuff[i] << "\t" << enameBuff[i] << "\t"
<< posnBuff[i] << "\t"; //10
if (mgrBuff.isNull(i)) { //11
cout << "NULL";
}
else {
cout << mgrBuff[i];
}
cout << "\t" << salBuff[i] << "\t" << deptBuff[i] << endl;
}
}
The second example assumes execution of an ad hoc query for which the schema of the result set is not known beforehand.
RWDBOSql openSql("SELECT * FROM emp WHERE dept = $1", RWDBOSql::Query);
RWDBTBuffer<int> queryDeptBuff;
queryDeptBuff[0] = 3;
openSql << queryDeptBuff;
openSql.execute(cn);
const size_t ROWS_TO_FETCH = 5;
RWDBMultiRow mRow(openSql.schema(), ROWS_TO_FETCH); //1
openSql[0] = mRow; //2
long rowsFetched = 0;
while ((rowsFetched = openSql.fetch().rowsFetched()) > 0) { //3
for (size_t row = 0; row < rowsFetched; ++row) {
for (size_t col = 0; col < mRow.numberOfColumns(); ++col) { //4
RWDBValue val = mRow[row][col]; //5
if ( val.isNull() ) { //6
cout << "NULL" << "\t";
}
else {
cout << val.asString() << "\t";
}
}
cout << endl;
}
}
The steps till the execution of the
RWDBOSql are the same as the previous example.
2.18.4.3 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
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;
}
2.18.5 Executing Cancel in Open SQL
Method
RWDBOSql::cancel() can be used either to cancel the current result set or to cancel all pending result sets of the current execution. This method should be called only after the
RWDBOSql object is executed.
Method
RWDBOSql::cancel(RWDBOSql::Current) cancels the current result set and moves the
RWDBOSql to the next available result set, if any.
Method RWDBOSql::cancel(RWDBOSql::All) cancels all the pending result sets. No subsequent results will be available from the current execution after this call has been made.