2.17 Open SQL and Oracle OCI

The DB Access Module for Oracle OCI uses positional binding for binding program variables to the Oracle library. When input parameter values are provided through binding for RWDBOSql objects, applications must use proper placeholder syntax in their SQL statements. The proper placeholder syntax for host-variable format is:

:<anything>

where anything can be a name or a number. For example, to insert data into a two-column table, named MyTable, through binding, the SQL statement would look like this:

insert into myTable values (:col1, :col2)

Please see Section 2.15, "Input Binding of Data," for information on the supported data types.

All types of the DB Interface Module are supported for output binding, with the exception of RWDBDuration. This type is not supported. Please see Section 3.1.1, "Data Type Classes," of the DB Interface Module User's Guide, for a list of the DB Interface Module data types.

In addition to the DB Interface Module types, Oracle cursors can be bound using the type RWDBNativeType1. See Section 2.17.6 and Section 2.17.7.2 for examples.

Applications must follow the type mapping rules as defined in Table 2 for output binding, and bind compatible types accordingly. For example, a RWDBValue::char type is stored internally as Oracle NUMBER(d) and retrieved as a C++ long. To retrieve the type properly, you must bind a buffer of type RWDBTBuffer<long>, or a compatible type. Trying to retrieve this as an RWDBTBuffer<char> will result in undefined errors.

The data callback classes can be used with Oracle to insert and fetch data. These classes are useful for very large data that may not fit in memory for a single database call and are described in Section 15.3, "The Data Callback Classes," in the DB Interface Module User's Guide.

Oracle supports sending multiple rows of data for one execution of an insert statement. Retrieving multiple rows of data on each call to RWDBOSql::fetch() is also supported.

The DB Access Module for Oracle sets the piece size for inserting or fetching data to 32768. This value represents bytes in the case of binary data, and code units in the case of character data.

The piece size setting can be accessed and modified with the method RWDBOCISystemHandle::piecewiseSize(). Please refer to the class reference description for RWDBOCISystemHandle for more information.

When inserting data using data callbacks, Oracle does not require the length of the data being inserted. Therefore, the method RWDBDataCallback::getLength() does not need to be overridden in derived classes.

Stored procedure execution is not supported with the data callback classes.

Apart from the data callback classes, stored procedures and PL/SQL block can be directly executed on an RWDBOSql object, just like an SQL statement. For cursor parameters, the proper binding type is RWDBNativeType1. Here is an example:

myOpenSqlObject.statement("BEGIN myStoredProc(:id, :cursor); END;", RWDBOSql::Procedure);

In this example, myStoredProc is the name of the stored procedure, and myOpenSqlObject is an RWDBOSql instance. Note the hint being given as Procedure.

Please see Section 2.17.7.2 for an example that uses RWDBOSql with stored procedures.

This section provides several Open SQL examples.

This example shows how to use the Open SQL class RWDBOSql in an Oracle insert operation. The insert is done on a foo table with an int column and a float column.

const size_t NUMBER_OF_ROWS = 10; RWDBOSql openSql; RWDBTBuffer<int> intBuffer(NUMBER_OF_ROWS); //1 RWDBTBuffer<float> floatBuffer(NUMBER_OF_ROWS); //2 RWCString oracleSyntaxSql("INSERT INTO foo VALUES(:c1, :c2)"); //3 openSql.statement(oracleSyntaxSql); //4 openSql << intBuffer; //5 openSql << floatBuffer; //6 while (FillupValues(intBuffer, floatBuffer, NUMBER_OF_ROWS)){ //7 openSql.execute(conn); //8 if (!openSql.isValid()) { //9 cout << "Insert Into foo Has Failed" << endl; break; } }

This example illustrates how to execute a stored procedure using an RWDBOSql object. The stored procedure used here is assumed to take one input-output parameter of type int. It returns one result set consisting of character strings. The example assumes an error handler is associated with the connection in use, and doesn't check for any errors after calls to the RWDBOSql object.

const size_t NUMBER_OF_ROWS = 10; RWDBTBuffer<int> index; //1 index.paramType(RWDBColumn::inOutParameter); //2 index[0] = 100; //3 RWDBTBuffer<RWDBNativeType1> cursor; //4 RWDBTBuffer<RWCString> result (NUMBER_OF_ROWS); //5 RWDBOSql openSql ("BEGIN myProc (:index, :cursor); END;", RWDBOSql::Procedure); //6 openSql << index << cursor; //7 openSql[0] >> result; //8 openSql.execute(conn); //9 long count = 0; while ((count = openSql.fetch().rowsFetched()) > 0 ) { //10 for (int row = 0; row < count; row++) { cout << "STRING VALUE :" << result[row] << endl; } } openSql.fetchReturnParams(); //11 cout << "OUT PARAM :" << index[0] << endl; //12

Each RWDBOSql object corresponds to an Oracle OCI OCIStmt structure. An OCIStmt structure is created and associated with an RWDBOSql object only when the execute() method is invoked on the RWDBOSql object. The OCIStmt structure remains associated until the RWDBOSql object goes out of scope, or an execute() is invoked on the object using a different connection. Invoking execute() on the same connection reuses the same OCIStmt structure.

High-level DB Interface Module objects, such as RWDBSelector and RWDBInserter, are internally associated with a unique RWDBOSql object. For this reason, these high-level objects are also restricted from simultaneous processing on the same connection. Also see Section 2.17.8.1.

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.