DB Access Module for Oracle OCI User’s Guide : Chapter 2 Technical Information : Open SQL and Oracle OCI : Open SQL Examples
Open SQL Examples
This section provides several Open SQL examples.
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;
}
}
//1-//2 Creates int and float buffers of size 10; in other words, the buffers hold up to 10 rows of data.
//3 Declares an Oracle-specific SQL syntax for inserting into a foo table. Note the placeholder syntax.
//4 The SQL is associated with the RWDBOSql object.
//5-6 Associates the buffer with the RWDBOSql object.
//7 The function FillupValues() is assumed to be a user-defined function which places NUMBER_OF_ROWS data entries into the int and float buffers every time it is called and returns true if it is able to do so.
//8 If FillupValues() succeeds, this line executes the inserter and inserts NUMBER_OF_ROWS new rows into the table named foo. If the execution of the SQL statement fails, the error handler associated with the connection is invoked.
//9 Checks the validity of the execution and breaks out of the while loop on failure.
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;
}
}
 
 
//1 Creates an RWDBOSql object with the supplied SQL query and the hint RWDBOSql::Query indicating that the SQL is a SELECT statement. :dept in the SQL statement is a placeholder for the department number from which employees are to be selected. The placeholder needs to be bound before executing the RWDBOSql.
//2 Creates an RWDBTBuffer object to 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 the department number for which the records will 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, with one object per column of the result set. The objects are templatized on the expected server types of the result columns as mapped to SourcePro types. (See the type mapping in “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, the buffers are bound to the first RWDBMultiRow object accessed using the index operator on RWDBOSql.
//9 Fetches the result into the bound buffers with each call to fetch() . The call rowsFetched() returns the number of rows fetched. The call to fetch() is repeated until all rows in the result set are fetched, indicated when rowsFetched() returns 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 = :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.
//1 Fetches the schema of the available result set using the schema() method on the RWDBOSql object. (You can use the RWDBSchema API to browse through the contents of the result set schema.) Here, we use the returned schema directly to create an RWDBMultiRow that 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 is returned by rowsFetched(). This is repeated until all rows in the result set are fetched, indicated when rowsFetched() returns 0.
//4 Finds the number of buffers in the RWDBMultiRow by using method numberOfColumns().
//5 Accesses each row’s data inside the RWDBMultiRow buffers as an RWDBRow object by calling the index operator on RWDBMultiRow and passing in the row number needed. Each value in the RWDBRow instance can then be accessed as an RWDBValue instance using the index operator on RWDBRow and passing in the column number needed.
//6 Uses the RWDBValue API to check if it is NULL. RWDBValue contains several other methods to process the value, for example, to determine the value’s type, access the value, or convert the value from one type to another.
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
 
//1 Creates a buffer of size 1 and type int for the input-output parameter.
//2 Sets the parameter type of buffer to IN/OUT.
//3 Assigns a value to the buffer.
//4 Creates special buffers to hold the two Oracle PL/SQL cursor parameters. The Oracle PL/SQL cursor is the special datatype representing the result set. The DB Interface Module provides class RWDBNativeType1 to represent the Oracle PL/SQL cursor type.
//5 Creates a buffer of size NUMBER_OF_ROWS and type RWCString for the output values of the first result set. This stored procedure returns the first result set in the form of the reference cursor consisting of a string column.
//6 - //7 Creates buffers of size NUMBER_OF_ROWS and types int and RWDecimalPortable for the output values of the second result set. This stored procedure returns the second result set in the form of the reference cursor consisting of two columns of types int and RWDecimalPortable respectively.
//8 Associates an SQL statement with the RWDBOSql object. The second argument of the constructor sets the statement type. This information helps optimize binding and performance.
//9 Associates the input buffers with the RWDBOSql object. The second and third bound in parameters, cursor1 and cursor2, act as the result set's placeholders.
//10 Associates the output buffer for the first result (result1 in this case) with the openSQL object. This output buffer is bound to the first result set, indicated by index 0.
//11 Associates the output buffers for the second result (result2_1 and result2_2) with the openSQL object. These output buffers are bound to the second result set, indicated by index 1.
//12 Executes the SQL statement.
//13 Fetches the rows inside a while loop until all rows for the first result set are fetched. A successful fetch places the rows in the buffer bound to the openSQL object (result1 in this case). Note that the number of rows fetched is guaranteed to be less than or equal to the buffer's row size (NUMBER_OF_ROWS in this case). Completion of the fetch operation of all rows in the first result set is indicated when fetch() returns 0 rows.
//14 Uses another while loop to fetch all rows for the second result set, placing the rows in the buffers result2_1 and result2_2.
//15 Fetches the output values of the OUT and IN/OUT parameters by calling fetchReturnParams().
//16 Prints the fetched output value of the IN/OUT parameter using the bound buffer.
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.
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.