2.17 Open SQL and Oracle

The alternative Open SQL interface introduced in DBTools.h++ 4.0 can be used with Oracle. This section provides some specific details and examples.

2.17.1 Statement Type Hint

An Open SQL statement type can be nonquery (the default), query, or procedure. This type can be set as the second parameter on:

RWDBOSql::statement(const RWCString& sql, StatementType type=NonQuery)

Although this type setting is not required by the Oracle access library, since the proper type information is inserted after execution, it is recommended for application code portability.

2.17.2 Placeholder Syntax

Beginning with DBTools.h++ 4.0, the Oracle access library uses named placeholders 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 libraries use different placeholder syntax.

2.17.3 Supported Datatypes for Input Binding

All DBTools.h++ datatypes are supported for input binding, with the exception of RWDBDuration. This type is not supported by Oracle. Please see the User's Guide, Section 3.1.1, for a list of the DBTools.h++ datatypes.

2.17.4 Supported Datatypes for Output Binding

All DBTools.h++ types are supported for output binding, with the exception of RWDBDuration. This type is not supported by Oracle. Please see the User's Guide, Section 3.1.1, for a list of the DBTools.h++ datatypes.

In addition to the DBTools.h++ types, Oracle cursors can be bound using the type RWDBNativeType1. See Section 2.17.5 and Section 2.17.6.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 character type of width 1 is stored internally as Oracle NUMBER(d) and retrieved as a C++ short. To retrieve the type properly, you must bind a buffer of type RWDBTBuffer<short>, or a compatible type RWDBTBuffer<int> or RWDBTBuffer<long>. Trying to retrieve this as an RWDBTBuffer<char> will result in undefined errors.

2.17.5 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, and myOpenSqlObject is an RWDBOSql instance. Note the hint being given as Procedure.

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

2.17.6 Open SQL Examples

2.17.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.

#define NUMBER_OF_ROWS 10

RWDBOSql openSql;

RWDBTBuffer<int> intBuffer(NUMBER_OF_ROWS);                    //1
RWDBTBuffer<float> floatBuffer(NUMBER_OF_ROWS);                //2

FillupValues(intBuffer, floatBuffer, NUMBER_OF_ROWS);          //3

RWCString oracleSyntaxSql("INSERT INTO foo VALUES(:c1, :c2)"); //4
openSql.statement(oracleSyntaxSql, RWDBOSql::NonQuery);       //5

openSql << intBuffer;                                          //6
openSql << floatBuffer;                                        //7

openSql.execute(conn);                                         //8
FillupValues(intBuffer, floatBuffer, NUMBER_OF_ROWS);          //9
openSql.execute(conn);                                         //10

if ( !openSql.isValid() )                                      //11
     cout << "Insert Into Foo Has Failed" << endl;

//1-//2	Creates `int` and `float` buffers of size 10.
//3	The buffers are filled with values using a predefined function.
//4	Declares an Oracle-specific SQL syntax for insertion into a `foo` table.
//5	The SQL is associated with the *RWDBOSql* object.
//6-//7	Associates the buffer with the *RWDBOSql* object.
//8-//10	The SQL is executed and data is re-filled and SQL is re-executed.
//11	Checks the validity of the execution. If an error handler was associated with the connection or the *RWDBOSql* object, then the error handler is invoked if the operation fails. When error handlers are associated, explicit error checking is unnecessary.

2.17.6.2 An Open SQL Stored Procedure Example

This example illustrates how a stored procedure can be executed 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.

This 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.

#define NUMBER_OF_ROWS 10    // Indicates the number of rows for
                             // the buffer
#define MAXSTRING_SIZE 255   // Maximum length of a string in 
                             // the buffer

RWDBTBuffer<int>  id;                                         //1
id = 100;                                                     //2
RWDBTBuffer<RWDBNativeType1>cursor;                           //3
RWDBTBuffer<RWCString>result(NUMBER_OF_ROWS, MAXSTRING_SIZE); //4

RWDBOSql openSql("BEGIN myProc (:id, :cursor); END;",
                 RWDBOSql::Procedure);                        //5

openSql << id << cursor;                                      //6

openSql[0] >> result;                                         //7

openSql.execute(conn);            // execute the stored procedure

/*
Rows are fetched inside a while loop until all rows are fetched. 
A successful fetch places the rows in the buffer bound to the
openSql object ("buffer" in this case). Note that the number of
rows fetched is guaranteed to be less than or equal to the buffer's
row size.
*/
long count = 0;
while ( (count = openSql.fetch().rowsFetched() ) > 0 ) {

    for( int row = 0; row < count; row++ ){
          cout << "STRING VALUE :" << result [row] << endl;
    }
}

//1-//2	Creates a buffer for input parameters. Note the difference between this buffer creation and buffer creation in the previous example. In this case, an application address space is bound to the buffer.
//3	Creates a special buffer to hold the Oracle PL/SQL cursor parameter. The Oracle PL/SQL cursor is the special datatype representing the result set. DBTools.h++ provides class *RWDBNativeType1* to represent the Oracle PL/SQL cursor type.
//4	Creates a buffer for output values. This stored procedure returns a result set consisting of string columns of less than 255 characters in length. As in the case of the input buffer, this example uses application address space for the buffer.
//5	Creates an Oracle-specific SQL statement for stored procedure invocation.
//6	Associates that SQL statement to the *RWDBOSql* object. Note the hint being given here as stored procedure. The second bound in parameter, `cursor`, acts as the result set's placeholder.
//7	Associates the output buffer to the Open SQL object. We expect only one result set for this stored procedure execution, because there is only one PL/SQL cursor parameter in the given stored procedure. Consequently, this output buffer is bound to the first result set, indicated by index 0. After execution and fetching, data can be expected at the result buffer.

2.17.7 CDA Structures

Each RWDBConnection can produce a number of RWDBOracleHandle objects that correspond to an Oracle CDA structure (Cda_Def). When you invoke execute() on an RWDBOSql, the associated RWDBConnection produces a CDA structure for the execution. That CDA structure is associated with the RWDBOSql object until the RWDBOSql object goes out of scope, or another execute() is invoked on the object using a different RWDBConnection. Invoking execute() with the same RWDBConnection reuses the existing CDA structure.

2.17.7.1 Simultaneous Processing of Results on RWDBOSql Object When Used for Stored Procedure

Multiple result sets are handled through the use of additional CDA structures. 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.

2.17.7.2 Simultaneous Processing of Results on High-Level DBTools.h++ Objects Using the Same Connection

High-level DBTools.h++ 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.7.1.