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
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.
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. |
© Copyright Rogue Wave Software, Inc. All Rights Reserved.
Rogue Wave and SourcePro are registered trademarks of Rogue Wave Software, Inc. in the United States and other countries. All other trademarks are the property of their respective owners.
Contact Rogue Wave about documentation or support issues.