The alternative Open SQL interface can be used with PostgreSQL. This section provides some specific details and examples.
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.
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.
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.17.4.3 for an example that uses RWDBOSql with stored procedures.
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.
These examples illustrate how an RWDBOSql object can be used to
Execute an INSERT statement, Section 2.17.4.1, "An Open SQL Insert Example."
Execute a SELECT query and retrieve its results, Section 2.17.4.2, "Open SQL Query Examples."
Execute a stored procedure and process its parameters and result sets, Section 2.17.4.3, "Open SQL Stored Procedure Examples."
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 |
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;
}
|
| //1 | Defines a constant for the number of rows to insert into the table in one insert operation. This is the array size of the bound variables. |
| //2 | Defines buffers for holding the data to be inserted. A buffer is defined for every column in the table. The datatype on which each RWDBTBuffer object is templatized depends on the server type of each column in the table. Mappings between the C++ types and server datatypes are provided in Section 2.3, "Datatypes." The number of entries in the buffer equal the number of rows to be inserted. |
| //3 | Fills the buffers with data for the first row using the index operator on the RWDBTBuffer objects. Similarly, fill other rows with the data. You may want to write a separate subroutine to fill the buffer with data. |
| //4 | Creates an RWDBOSql object with the INSERT SQL statement using the statement type hint of RWDBOSql::NonQuery. The INSERT statement uses placeholders for each column in the table. Buffers need to be bound for the placeholders before the RWDBOSql is ready for execution. |
| //5 | Binds the input buffers to the RWDBOSql in the sequence of columns in the table. |
| //6 | Executes the RWDBOSql on the supplied RWDBConnection. |
| //7 | Fetches the number of rows inserted using method rowsAffected(). Note that some rows may be inserted even if the insert operation fails. |
| //8 | Determines the success of the insert operation using the isValid() method on RWDBOSql. |
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.17.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;
}
}
|
| //1 | Creates an RWDBOSql object with the supplied SQL query and the hint RWDBOSql::Query indicating that the SQL is a SELECT statement. The "$1" in the SQL statement is a placeholder for the department number whose employees are to be selected. The placeholder needs to be bound before executing the RWDBOSql. |
| //2 | Creates an RWDBTBuffer object that will be bound to the placeholder. It is templatized on type int, as the dept column is of server type INT. The buffer will have a single entry of type int. |
| //3 | Uses the index operator to access the single entry from the buffer created in //2, assigning it a department number whose records are to be queried. |
| //4 | Binds the buffer to the RWDBOSql object. The SQL query has only one placeholder, hence only one buffer needs to be bound. The RWDBOSql object is now ready for execution. |
| //5 | Executes the RWDBOSql object on a supplied RWDBConnection object. |
| //6 | Defines a constant for the number of rows to fetch at a time from the query result set. |
| //7 | Creates RWDBTBuffer objects to be used in fetching the query result set. One object is needed per column of the result set. The types on which they are templatized are based on the mapping of the expected server types of the result columns to the SourcePro types. The type mapping is provided in Section 2.3, "Datatypes." Each buffer is created with the number of entries equal to the number of rows to be fetched at a time. |
| //8 | Binds the output buffers to the first RWDBMultiRow object in the RWDBOSql. As this is the first (and only) result set from the execution of the RWDBOSql, we bind to the first RWDBMultiRow object that is accessed by using the index operator on RWDBOSql. |
| //9 | Each call to fetch() fetches the result into the bound buffers. Call rowsFetched() returns the number of rows fetched. Call to fetch() is repeated until all rows in the result set are fetched, indicated by rowsFetched() returning 0. |
| //10 | Uses the index operator on the bound output buffers to access the fetched data. |
| //11 | Uses the isNull() method on the bound output buffer mgrBuff to determine if a particular entry in the buffer is NULL. |
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.
| //1 | Fetches the schema of the available result set by using the schema() method on the RWDBOSql. RWDBSchema API can be used to browse through the contents of the result set schema. Here, we use the returned schema directly to create an RWDBMultiRow which creates buffers matching the datatypes of the result set columns. Each buffer will be created with ROWS_TO_FETCH entries. |
| //2 | Assigns the created RWDBMultiRow to the RWDBOSql for use in fetching the first result set, by assigning to the 0th position in the RWDBOSql's result sets. |
| //3 | Fetches rows into the buffers in the RWDBMultiRow with the fetch() call. The number of rows fetched are returned by the rowsFetched() call. This is repeated till all rows in the result set are fetched, which is indicated by the rowsFetched() returning 0. |
| //4 | Finds the number of buffers in the RWDBMultiRow by using method numberOfColumns(). |
| //5 | Data inside the buffers in the RWDBMultiRow can be accessed as an RWDBRow object by calling the index operator on RWDBMultiRow passing in the row number needed. Each value in the RWDBRow instance can be accessed as RWDBValue using the index operator on the RWDBRow passing the column number needed. |
| //6 | RWDBValue API can be used to determine the type of the value, checking if it is NULL, or to process it in other ways. |
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. |
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. |
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.
Copyright © Rogue Wave Software, Inc. All Rights Reserved.
The Rogue Wave name and logo, and SourcePro, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Provide feedback to Rogue Wave about its documentation.