Open SQL and PostgreSQL

DB Access Module for PostgreSQL User's Guide
Rogue Wave web site: Home Page | Main Documentation Page

2.16 Open SQL and PostgreSQL

The alternative Open SQL interface can be used with PostgreSQL. This section provides some specific details and examples.

2.16.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.16.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.16.3 Stored Procedures and Open SQL

This example shows how a stored procedure can be executed using an RWDBOSql object. The stored procedure shown below takes a varchar and an integer as arguments and returns a bool.

Example 7: Executing a stored procedure using an RWDBOSql object

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 procedure
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 procedure execution so this output buffer is bound to the first result set which is indicated by the index `0`.
`//5`	Processes the return value from execution of the stored procedure.

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.