2.19 Open SQL and Oracle OCI
The alternative Open SQL interface can be used with Oracle OCI. This section provides some specific details and examples.
2.19.1 Placeholder Syntax
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)
Placeholder syntax is not a portable feature. Different Access Modules use different placeholder syntax.
2.19.2 Supported Datatypes for Output Binding
In addition to the datatype support listed in
Section 2.3, “Datatypes,” Oracle cursors can be bound using the type
RWDBNativeType1. See
Section 2.19.4 and
Section 2.19.6.3 for examples. For more information on output binding, see
Section 2.17, “Output Binding of Data.”2.19.3 RWDBDataCallback and Open SQL
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.
2.19.3.1 Piece Size Setting
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.
2.19.3.2 RWDBDataCallback::getLength()
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.
2.19.3.3 Stored Procedures and RWDBDataCallback
Stored procedure execution is not supported with the data callback classes.
2.19.4 Stored Procedures and Open SQL
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,
:id and
:cursor are the placeholders for the parameters to be passed to the stored procedure, and
myOpenSqlObject is an
RWDBOSql instance. Note the hint provided as
Procedure.
RWDBOSql myOpenSqlObject("BEGIN :ret := myStoredFunc(:id, :cursor); END;",
RWDBOSql::Procedure);
In this example,
myStoredFunc is a stored function that returns a value. An additional placeholder,
:ret, is used to receive the return value as an
OUT parameter. Please see
Section 2.19.6.3 for an example that uses
RWDBOSql with stored procedures.
Stored procedure execution is not supported with the data callback classes.
2.19.4.1 Sequence of Fetching Result Sets and Out Parameters
Oracle stored procedures return result sets in the form of reference cursor parameters. Hence, there is no requirement regarding the sequence in which result sets and OUT parameters are fetched, as both are essentially OUT parameters. Method fetchReturnParams() could be called any time after the stored procedure execution to retrieve the OUT parameters. However, to make your application portable across different access modules, it is recommended to fetch OUT parameters after completely processing the result sets.
2.19.5 Executing Cancel in Open SQL
Method
RWDBOSql::cancel() can be used either to cancel the current result set or to cancel all the pending result sets of the current execution. This method should be called only after the
RWDBOSql object is executed. Otherwise, it results in an
RWDBStatus::invalidUsage error.
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.
2.19.6 Open SQL Examples
This section provides several Open SQL examples.
2.19.6.1 An Open SQL Insert Example
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;
}
}
2.19.6.2 Open SQL Query Examples
These examples illustrate how an
RWDBOSql object can be used to execute a
SELECT query and retrieve its results. The
SELECT query is executed on an employee table
emp and retrieves all columns of the table for employees in a particular department. 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.
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
The first example assumes awareness of the structure of table emp, and hence the schema of the result set returned by the query.
RWDBOSql openSql("SELECT * FROM emp WHERE dept = :dept", 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 = :dept", 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 until
RWDBOSql execution are the same as in the previous example.
2.19.6.3 An Open SQL Stored Procedure Example
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 two result sets: the first consists of character strings; the second of two columns of types
int and
RWDecimalPortable. 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> cursor1, cursor2; //4
RWDBTBuffer<RWCString> result1 (NUMBER_OF_ROWS); //5
RWDBTBuffer<int> result2_1 (NUMBER_OF_ROWS); //6
RWDBTBuffer<RWDecimalPortable> result2_2 (NUMBER_OF_ROWS); //7
RWDBOSql openSql ("BEGIN myProc (:index, :cursor1, :cursor2); END;",
RWDBOSql::Procedure); //8
openSql << index << cursor1 << cursor2; //9
openSql[0] >> result1; //10
openSql[1] >> result2_1 >> result2_2; //11
openSql.execute(conn); //12
long count = 0;
while ((count = openSql.fetch().rowsFetched()) > 0 ) { //13
for (int row = 0; row < count; row++) {
cout << "STRING VALUE :" << result1[row] << endl;
}
}
while ((count = openSql.fetch().rowsFetched()) > 0 ) { //14
for (int row = 0; row < count; row++) {
cout << "INT VALUE :" << result2_1[row] << endl;
cout << "DECIMAL VALUE: " << result2_2[row] << endl;
}
}
openSql.fetchReturnParams(); //15
cout << "OUT PARAM :" << index[0] << endl; //16
2.19.7 OCIStmts
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.
2.19.7.1 Simultaneous Processing of Results on an RWDBOSql Object When Used for Stored Procedure
Multiple result sets produced by a stored procedure execution are handled through additional OCIStmts. To simplify usage, Open SQL does not support simultaneous fetching of multiple result sets. You must complete the current result set, by fetching all the data or canceling, in order to move on to the next result set.