2
Relational Programming

This chapter describes the basics of developing C++ applications using Oracle C++ Call Interface (OCCI) to work with data stored in standard relational databases.

It includes the following topics:

Connecting to a Database

You have a number of different options with regard to how your application connects to the database. These options are discussed in the following sections:

Creating and Terminating an Environment

All OCCI processing takes place in the context of the Environment class. An OCCI environment provides application modes and user-specified memory management functions. The following code example shows how you can create an OCCI environment:

Environment *env = Environment::createEnvironment();

All OCCI objects created with the createxxx methods (connections, connection pools, statements) must be explicitly terminated and so, when appropriate, you must also explicitly terminate the environment. The following code example shows how you terminate an OCCI environment.

Environment::terminateEnvironment(env);

In addition, an OCCI environment should have a scope that is larger than the scope of any objects created in the context of the that environment. This concept is demonstrated in the following code example:

const string userName = "SCOTT";
const string password = "TIGER";
const string connectString = "";

Environment *env = Environment::createEnvironment();
{
   Connection *conn = env->createConnection(userName, password, connectString);
   Statement *stmt = conn->createStatement("SELECT blobcol FROM mytable");
   ResultSet *rs = stmt->executeQuery();
   rs->next();
   Blob b = rs->getBlob(1);
   cout << "Length of BLOB : " << b.length();
   .
   .
   .
   stmt->closeResultSet(rs);
   conn->terminateStatement(stmt);
   env->terminateConnection(conn);
}
Environment::terminateEnvironment(env);

You can use the mode parameter of the createEnvironment method to specify that your application:

Runs in a threaded environment (THREADED_MUTEXED or THREADED_UNMUTEXED)
Uses objects (OBJECT)
Utilizes shared data structures (SHARED)

The mode can be set independently in each environment.

Opening and Closing a Connection

The Environment class is the factory class for creating Connection objects. You first create an Environment instance, and then use it to enable users to connect to the database by means of the createConnection method.

The following code example creates an environment instance and then uses it to create a database connection for a database user scott with the password tiger.

Environment *env = Environment::createEnvironment();
Connection *conn = env->createConnection("scott", "tiger");

You must use the terminateConnection method shown in the following code example to explicitly close the connection at the end of the working session. In addition, the OCCI environment should be explicitly terminated.

env->terminateConnection(conn);
Environment::terminateEnvironment(env);

Creating a Connection Pool

For many shared server, middle-tier applications, connections to the database should be enabled for a large number of threads, each thread for a relatively short duration. Opening a connection to the database for every thread would result in inefficient utilization of connections and poor performance.

By employing the connection pooling feature, your application can use database management system (DBMS) functionality to manage the connections. Oracle creates a small number of open connections, dynamically selects one of the free connections to execute a statement, and then releases the connection immediately after the execution. This relieves you from creating complex mechanisms to handle connections and optimizes performance in your application.

Creating a Connection Pool

To create a connection pool, you use the createConnectionPool method:

virtual ConnectionPool* createConnectionPool(
   const string &poolUserName,
   const string &poolPassword,
   const string &connectString ="", 
   unsigned int minConn =0,
   unsigned int maxConn =1,
   unsigned int incrConn =1) = 0;

The following parameters are used in the previous method example:

poolUserName: The owner of the connection pool
poolPassword: The password to gain access to the connection pool
connectString ="": The database name that specifies the database server to which the connection pool is related
minConn: The minimum number of connections to be opened when the connection pool is created
maxConn: The maximum number of connections that can be maintained by the connection pool. When the maximum number of connections are open in the connection pool, and all the connections are busy, an OCCI method call that needs a connection waits until it gets one, unless setErrorOnBusy() was called on the connection pool
incrConn: The additional number of connections to be opened when all the connections are busy and a call needs a connection. This increment is implemented only when the total number of open connections is less than the maximum number of connections that can be opened in that connection pool

The following code example demonstrates how you can create a connection pool:

const string poolUserName = "SCOTT";
const string poolPassword = "TIGER";
const string connectString = "";
const string username = "SCOTT";
const string password = "TIGER";
unsigned int maxConn = 5;
unsigned int minConn = 3;
unsigned int incrConn = 2;

ConnectionPool *connPool = env->createConnectionPool(poolUserName, poolPassword,
   connectString, minConn, maxConn, incrConn);

See Also:

Appendix A, "OCCI Demonstration Programs" and the code example occipool.cpp that demonstrates how to use the connection pool interface of OCCI

You can also configure all these attributes dynamically. This lets you design an application that has the flexibility of reading the current load (number of open connections and number of busy connections) and tune these attributes appropriately. In addition, you can use the setTimeOut method to time out the connections that are idle for more than the specified time. The DBMS terminates idle connections periodically so as to maintain an optimum number of open connections.

Each connection pool has a data structure (pool handle) associated with it. This pool handle stores the pool parameters. There is no restriction that one environment must have only one connection pool. There can be multiple connection pools in a single OCCI environment, and these can connect to the same or different databases. This is useful for applications requiring load balancing. However, note that since a pool handle requires memory, multiple connection pools consume more memory.

Proxy Connections

If you authorize the connection pool user to act as a proxy for other connections, then no password is required to log in database users who use one of the connections in the connection pool to act as a proxy on their behalf.

A proxy connection can be created by using either of the following methods:

ConnectionPool->createProxyConnection(const string &name,
   Connection::ProxyType proxyType =
   Connection::PROXY_DEFAULT);

ConnectionPool->createProxyConnection(const string &name,
   string roles[], int numRoles,
   Connection::ProxyType proxyType =
   Connection::PROXY_DEFAULT);

The following parameters are used in the previous method example:

roles[]: The roles array specifies a list of roles to be activated after the proxy connection is activated for the client
Connection::ProxyType proxyType = Connection::PROXY_DEFAULT: The enumeration Connection::ProxyType lists constants representing the various ways of achieving proxy authentication. PROXY_DEFAULT is used to indicate that name represents a database username and is the only proxy authentication mode currently supported.

Executing SQL DDL and DML Statements

SQL is the industry-wide language for working with relational databases. In OCCI you execute SQL commands by means of the Statement class.

Creating a Statement Handle

To create a Statement handle, call the createStatement method of the Connection object, as shown in the following example:

Statement *stmt = conn->createStatement();

Creating a Statement Handle to Execute SQL Commands

Once you have created a Statement handle, execute SQL commands by calling the execute, executeUpdate, executeArrayUpdate, or executeQuery methods on the Statement. These methods are used for the following purposes:

execute: To execute all nonspecific statement types
executeUpdate: To execute DML and DDL statements
executeQuery: To execute a query
executeArrayUpdate: To execute multiple DML statements

Creating a Database Table

Using the executeUpdate method, the following code example demonstrates how you can create a database table:

stmt->executeUpdate("CREATE TABLE basket_tab 
   (fruit VARCHAR2(30), quantity NUMBER)");

Inserting Values into a Database Table

Similarly, you can execute a SQL INSERT statement by invoking the executeUpdate method:

stmt->executeUpdate("INSERT INTO basket_tab
   VALUES(`MANGOES', 3)");

The executeUpdate method returns the number of rows affected by the SQL statement.

See Also:

Appendix A, "OCCI Demonstration Programs" and the code example occidml.cpp that demonstrates how to perform insert, select, update, and delete operations of a table row by using OCCI

Reusing a Statement Handle

You can reuse a statement handle to execute SQL statements multiple times. For example, to repeatedly execute the same statement with different parameters, you specify the statement by the setSQL method of the Statement handle:

stmt->setSQL("INSERT INTO basket_tab VALUES(:1,:2)");

You may now execute this INSERT statement as many times as required. If at a later time you wish to execute a different SQL statement, you simply reset the statement handle. For example:

stmt->setSQL("SELECT * FROM basket_tab WHERE quantity >= :1");

Thus, OCCI statement handles and their associated resources are not allocated or freed unnecessarily. You can retrieve the contents of the current statement handle at any time by means of the getSQL method.

SHARED Mode

When a SQL statement is processed, certain underlying data is associated with the statement. This data includes information about statement text and bind data, as well as resultset and describe information for queries. This data remains the same from one execution of a statement to another, even if the statement is executed by different users.

When an OCCI environment is initialized in SHARED mode, common statement data is shared between multiple statement handles, thus providing memory savings for the application. This savings may be particularly valuable for applications that create multiple statement handles which execute the same SQL statement on different user sessions, either on the same or multiple connections.

To enable sharing of common metadata across multiple statement handles, create the Environment in SHARED mode.

Terminating a Statement Handle

You should explicitly terminate and deallocate a Statement:

Connection::conn->terminateStatement(Statement *stmt);

Types of SQL Statements in the OCCI Environment

There are three types of SQL statements in the OCCI environment:

Standard Statements use SQL commands with specified values
Parameterized Statements have parameters, or bind variables
Callable Statements call stored PL/SQL procedures

The Statement methods are subdivided into those applicable to all statements, to parameterized statements, and to callable statements. Standard statements are a superset of parameterized statements, and parameterized statements are a superset of callable statements.

Standard Statements

Previous sections describe examples of both DDL and DML commands. For example:

stmt->executeUpdate("CREATE TABLE basket_tab 
   (fruit VARCHAR2(30), quantity NUMBER)");

and

stmt->executeUpdate("INSERT INTO basket_tab
   VALUES(`MANGOES', 3)");

These are each an example of a standard statement in which you explicitly define the values of the statement. So, in these examples, the CREATE TABLE statement specifies the name of the table (basket_tab), and the INSERT statement stipulates the values to be inserted (`MANGOES', 3).

Parameterized Statements

You can execute the same statement with different parameters by setting placeholders for the input variables of the statement. These statements are referred to as parameterized statements because they are able to accept input from a user or program by using parameters.

For example, suppose you want to execute an INSERT statement with different parameters. You first specify the statement by the setSQL method of the Statement handle:

stmt->setSQL("INSERT INTO basket_tab VALUES(:1, :2)");

You then call the setxxx methods to specify the parameters, where xxx stands for the type of the parameter. The following example invokes the setString and setInt methods to input the values of these types into the first and second parameters.

To insert a row:

stmt->setString(1, "Bananas");     // value for first parameter
stmt->setInt(2, 5);                // value for second parameter

Having specified the parameters, you insert values into the row:

stmt->executeUpdate();             // execute statement

To insert another row:

stmt->setString(1, "Apples");      // value for first parameter
stmt->setInt(2, 9);                // value for second parameter

Having specified the parameters, you again insert values into the row:

stmt->executeUpdate();             // execute statement

If your application is executing the same statement repeatedly, then avoid changing the input parameter types because a rebind is performed each time the input type changes.

Callable Statements

PL/SQL stored procedures, as their name suggests, are procedures that are stored on the database server for reuse by an application. By using OCCI, a call to a procedure which contains other SQL statements is referred to as a callable statement.

For example, suppose you wish to call a procedure (countFruit) that returns the quantity of a specified kind of fruit. To specify the input parameters of a PL/SQL stored procedure, call the setxxx methods of the Statement class as you would for parameterized statements.

stmt->setSQL("BEGIN countFruit(:1, :2); END:");
int quantity;
stmt->setString(1, "Apples");
// specify the first (IN) parameter of procedure

However, before calling a stored procedure, you need to specify the type and size of any OUT and IN/OUT parameters by calling the registerOutParam method.

stmt->registerOutParam(2, Type::OCCIINT, sizeof(quantity));
// specify the type and size of the second (OUT) parameter

You now execute the statement by calling the procedure:

stmt->executeUpdate();           // call the procedure

Finally, you obtain the output parameters by calling the relevant getxxx method:

quantity = stmt->getInt(2);     // get the value of the second (OUT) parameter

Callable Statements with Arrays as Parameters

A PL/SQL stored procedure executed through a callable statement can have array of values as parameters. The number of elements in the array and the dimension of elements in the array are specified through the setDataBufferArray method.

The following example shows the setDataBufferArray method:

void setDataBufferArray(int paramIndex,
   void *buffer, 
   Type type, 
   ub4 arraySize, 
   ub4 *arrayLength, 
   sb4 elementSize, 
   sb2 *ind = NULL, 
   ub2 *rc = NULL);

The following parameters are used in the previous method example:

paramIndex: Parameter number
buffer: Data buffer containing an array of values
Type: Type of data in the data buffer
arraySize: Maximum number of elements in the array
arrayLength: Number of elements in the array
elementSize: Size of the current element in the array
ind: Indicator information
rc: Return code

See Also:

Appendix A, "OCCI Demonstration Programs" and the code example occiproc.cpp that demonstrates how to invoke PL/SQL procedures with bind parameters

Streamed Reads and Writes

Streamed data is of three kinds:

A writable stream corresponds to an IN bind variable.
A readable stream corresponds to an OUT bind variable.
A bidirectional stream corresponds to an IN/OUT bind variable.

OCCI supports streamed parameters for parameterized and callable statements of all three kinds: IN, OUT, and IN/OUT.

Modifying Rows Iteratively

While you can issue the executeUpdate method repeatedly for each row, OCCI provides an efficient mechanism for sending data for multiple rows in a single network round-trip. To do this, use the addIteration method of the Statement class to perform batch operations that modify a different row with each iteration.

To execute INSERT, UPDATE, and DELETE operations iteratively, you must:

Set the maximum number of iterations
Set the maximum parameter size for variable length parameters

Setting the Maximum Number of Iterations

For iterative execution, first specify the maximum number of iterations that would be done for the statement by calling the setMaxIterations method:

Statement->setMaxIterations(int maxIterations)

You can retrieve the current maximum iterations setting by calling the getMaxIterations method.

Setting the Maximum Parameter Size

If the iterative execution involves variable length datatypes, such as string and Bytes, then you must set the maximum parameter size so that OCCI can allocate the maximum size buffer:

Statement->setMaxParamSize(int parameterIndex, int maxParamSize)

You do not need to set the maximum parameter size for fixed length datatypes, such as Number and Date, or for parameters that use the setDataBuffer method.

You can retrieve the current maximum parameter size setting by calling the getMaxParamSize method.

Executing an Iterative Operation

Once you have set the maximum number of iterations and (if necessary) the maximum parameter size, iterative execution using a parameterized statement is straightforward, as shown in the following example:

stmt->setSQL("INSERT INTO basket_tab VALUES(:1, :2)");

stmt->setString(1, "Apples");      // value for first parameter of first row
stmt->setInt(2, 6);                // value for second parameter of first row
stmt->addIteration();              // add the iteration

stmt->setString(1, "Oranges");     // value for first parameter of second row
stmt->setInt(1, 4);                // value for second parameter of second row

stmt->executeUpdate();             // execute statement

As shown in the example, you call the addIteration method after each iteration except the last, after which you invoke executeUpdate method. Of course, if you did not have a second row to insert, then you would not need to call the addIteration method or make the subsequent calls to the setxxx methods.

Iterative Execution Usage Notes

Iterative execution is designed only for use in INSERT, UPDATE and DELETE operations that use either standard or parameterized statements. It cannot be used for callable statements and queries.
The datatype cannot be changed between iterations. For example, if you use setInt for parameter 1, then you cannot use setString for the same parameter in a later iteration.

Executing SQL Queries

SQL query statements allow your applications to request information from a database based on any constraints specified. A result set is returned as a result of a query.

Result Set

Execution of a database query puts the results of the query into a set of rows called the result set. In OCCI, a SQL SELECT statement is executed by the executeQuery method of the Statement class. This method returns an ResultSet object that represents the results of a query.

ResultSet *rs = stmt->executeQuery("SELECT * FROM basket_tab");

Once you have the data in the result set, you can perform operations on it. For example, suppose you wanted to print the contents of this table. The next method of the ResultSet is used to fetch data, and the getxxx methods are used to retrieve the individual columns of the result set, as shown in the following code example:

cout << "The basket has:" << endl;

while (rs->next())
{
   string fruit = rs->getString(1);     // get the first column as string
   int quantity = rs->getInt(2);        // get the second column as int

   cout << quantity << " " << fruit << endl;
}

The next and status methods of the ResultSet class return an enumerated type of Status. The possible values of Status are:

DATA_AVAILABLE
END_OF_FETCH = 0
STREAM_DATA_AVAILABLE

If data is available for the current row, then the status is DATA_AVAILABLE. After all the data has been read, the status changes to END_OF_FETCH.

If there are any output streams to be read, then the status is STREAM_DATA__AVAILABLE until all the stream data is successfully read, as shown in the following code example:

ResultSet *rs = stmt->executeQuery("SELECT * FROM demo_tab"); 
ResultSet::Status status = rs->status();     // status is DATA_AVAILABLE 
while (rs->next()) 
{ 
   get data and process; 
}

When the entire result set has been traversed, then the status changes to END_OF_FETCH which terminates the WHILE loop.

The following is an example for streams for a result set:

char buffer[4096]; 
ResultSet *rs = stmt->executeQuery 
   ("SELECT col2 FROM tab1 WHERE col1 = 11"); 
ResultSet *rs = stmt->getResultSet (); 

while (rs->next ()) 
{ 
   unsigned int length = 0; 
   unsigned int size = 500; 
   Stream *stream = rs->getStream (2); 
   while (stream->status () == Stream::READY_FOR_READ) 
   { 
      length += stream->readBuffer (buffer +length, size); 
   } 
   cout << "Read "  << length << " bytes into the buffer" << endl; 
}

Specifying the Query

The IN bind variables can be used with queries to specify constraints in the WHERE clause of a query. For example, the following program prints only those items that have a minimum quantity of 4:

stmt->setSQL("SELECT * FROM basket_tab WHERE quantity >= :1");
int minimumQuantity = 4;
stmt->setInt(1, minimumQuantity);     // set first parameter
ResultSet *rs = stmt->executeQuery();
cout << "The basket has:" << endl;

while (rs->next())
   cout << rs->getInt(2) << " " << rs->getString(1) << endl;

Optimizing Performance by Setting Prefetch Count

Although the ResultSet method retrieves data one row at a time, the actual fetch of data from the server need not entail a network round-trip for each row queried. To maximize the performance, you can set the number of rows to prefetch in each round-trip to the server.

You effect this either by setting the number of rows to be prefetched (setPrefetchRowCount), or by setting the memory size to be used for prefetching (setPrefetchMemorySize).

If you set both of these attributes, then the specified number of rows are prefetched unless the specified memory limit is reached first. If the specified memory limit is reached first, then the prefetch returns as many rows as will fit in the memory space defined by the call to the setPrefetchMemorySize method.

By default, prefetching is turned on, and the database fetches an extra row all the time. To turn prefetching off, set both the prefetch row count and memory size to zero.

Note:

Prefetching is not in effect if LONG columns are part of the query. Queries containing LOB columns can be prefetched, because the LOB locator, rather than the data, is returned by the query.

Executing Statements Dynamically

When you know that you need to execute a DML operation, you use the executeUpdate method. Similarly, when you know that you need to execute a query, you use executeQuery.

If your application needs to allow for dynamic events and you cannot be sure of which statement will need to be executed at run time, then OCCI provides the execute method. Invoking the execute method returns one of the following statuses:

While invoking the execute method will return one of these statuses, you can also interrogate the statement by using the status method.

Statement stmt = conn->createStatement(); 
Statement::Status status = stmt->status();       // status is UNPREPARED 
stmt->setSQL("select * from emp"); 
status = stmt->status();                         // status is PREPARED

If a statement handle is created with a SQL string, then it is created in a PREPARED state. For example:

Statement stmt = conn->createStatement("insert into foo(id) values(99)"); 
Statement::Status status = stmt->status();       // status is PREPARED 
status = stmt->execute();            // status is UPDATE_COUNT_AVAILABLE

When you set another SQL statement on the Statement, the status changes to PREPARED. For example:

stmt->setSQL("select * from emp");               // status is PREPARED 
status = stmt->execute();            // status is RESULT_SET_AVAILABLE

Status Definitions

This section describes the possible values of Status related to a statement handle:

UNPREPARED

If you have not used the setSQL method to attribute a SQL string to a statement handle, then the statement is in an UNPREPARED state.

Statement stmt = conn->createStatement(); 
Statement::Status status = stmt->status();   // status is UNPREPARED

PREPARED

If a Statement is created with a SQL string, then it is created in a PREPARED state. For example:

Statement stmt = conn->createStatement("INSERT INTO demo_tab(id) 
   VALUES(99)"); 
Statement::Status status = stmt->status();    // status is PREPARED

Setting another SQL statement on the Statement will also change the status to PREPARED. For example:

status = stmt->execute();                    // status is UPDATE_COUNT_AVAILABLE
stmt->setSQL("SELECT * FROM demo_tab");      // status is PREPARED

RESULT_SET_AVAILABLE

A status of RESULT_SET_AVAILABLE indicates that a properly formulated query has been executed and the results are accessible through a result set.

When you set a statement handle to a query, it is PREPARED. Once you have executed the query, the statement changes to RESULT_SET_AVAILABLE. For example:

stmt->setSQL("SELECT * from EMP");           // status is PREPARED 
status = stmt->execute();                    // status is RESULT_SET_AVAILABLE

To access the data in the result set, issue the following statement:

ResultSet *rs = Statement->getResultSet();

UPDATE_COUNT_AVAILABLE

When a DDL or DML statement in a PREPARED state is executed, its state changes to UPDATE_COUNT_AVAILABLE, as shown in the following code example:

Statement stmt = conn->createStatement("INSERT INTO demo_tab(id) VALUES(99)"); 
Statemnt::Status status = stmt->status();    // status is PREPARED 
status = stmt->execute();                    // status is UPDATE_COUNT_AVAILABLE

This status refers to the number of rows affected by the execution of the statement. It indicates that:

The statement did not include any input or output streams.
The statement was not a query but either a DDL or DML statement.

You can obtain the number of rows affected by issuing the following statement:

Statement->getUpdateCount();

Note that a DDL statement will result in an update count of zero (0). Similarly, an update that does not meet any matching conditions will also produce a count of zero (0). In such a case, you cannot infer the kind of statement that has been executed from the reported status.

NEEDS_STREAM_DATA

If there are any output streams to be written, the execute does not complete until all the stream data is completely provided. In such a case, the status changes to NEEDS_STREAM_DATA to indicate that a stream must be written. After writing the stream, call the status method to find out if more stream data should be written, or whether the execution has completed.

In cases in which your statement includes multiple streamed parameters, use the getCurrentStreamParam method to discover which parameter needs to be written.

If you are performing an iterative or array execute, then the getCurrentStreamIteration method reveals to which iteration the data is to be written.

Once all the stream data has been handled, the status changes to either RESULT_SET_AVAILABLE or UPDATE_COUNT_AVAILABLE.

STREAM_DATA_AVAILABLE

This status indicates that the application requires some stream data to be read in OUT or IN/OUT parameters before the execution can finish. After reading the stream, call the status method to find out if more stream data should be read, or whether the execution has completed.

In cases in which your statement includes multiple streamed parameters, use the getCurrentStreamParam method to discover which parameter needs to be read.

If you are performing an iterative or array execute, then the getCurrentStreamIteration method reveals from which iteration the data is to be read.

Once all the stream data has been handled, the status changes to UPDATE_COUNT_REMOVE_AVAILABLE.

The ResultSet class also has readable streams and it operates similar to the readable streams of the Statement class.

Committing a Transaction

All SQL DML statements are executed in the context of a transaction. An application causes the changes made by these statement to become permanent by either committing the transaction, or undoing them by performing a rollback. While the SQL COMMIT and ROLLBACK statements can be executed with the executeUpdate method, you can also call the Connection::commit and Connection::rollback methods.

If you want the DML changes that were made to be committed immediately, you can turn on the auto commit mode of the Statement class by issuing the following statement:

Statement::setAutoCommit(TRUE)

Once auto commit is in effect, each change is automatically made permanent. This is similar to issuing a commit right after each execution.

To return to the default mode, auto commit off, issue the following statement:

Statement::setAutoCommit(FALSE)

Error Handling

Each OCCI method is capable of returning a return code indicating whether the method was successful or not. In other words, an OCCI method can throw an exception. This exception is of type SQLException. OCCI uses the C++ Standard Template Library (STL), so any exception that can be thrown by the STL can also be thrown by OCCI methods.

The STL exceptions are derived from the standard exception class. The exception::what() method returns a pointer to the error text. The error text is guaranteed to be valid during the catch block

The SQLException class contains Oracle specific error numbers and messages. It is derived from the standard exception class, so it too can obtain the error text by using the exception::what() method.

In addition, the SQLException class has two methods it can use to obtain error information. The getErrorCode method returns the Oracle error number. The same error text returned by exception::what() can be obtained by the getMessage method. The getMessage method returns an STL string so that it can be copied like any other STL string.

Based on your error handling strategy, you may choose to handle OCCI exceptions differently from standard exceptions, or you may choose not to distinguish between the two.

If you decide that it is not important to distinguish between OCCI exceptions and standard exceptions, your catch block might look similar to the following:

catch (exception &excp)
{
   cerr << excp.what() << endl;
}

Should you decide to handle OCCI exceptions differently than standard exceptions, your catch block might look like the following:

catch (SQLException &sqlExcp)
{
   cerr <<sqlExcp.getErrorCode << ": " << sqlExcp.getErrorMessage() << endl;
}
catch (exception &excp)
{
   cerr << excp.what() << endl;
}

In the preceding catch block, SQL exceptions are caught by the first block and non-SQL exceptions are caught by the second block. If the order of these two blocks were to be reversed, SQL exceptions would never be caught. Since SQLException is derived from the standard exception, the standard exception catch block would handle the SQL exception as well.

See Also:

Oracle9i Database Error Messages for more information about Oracle error messages.

Null and Truncated Data

In general, OCCI does not cause an exception when the data value retrieved by using the getxxx methods of the ResultSet class or Statement class is null or truncated. However, this behavior can be changed by calling the setErrorOnNull method or setErrorOnTruncate method. If the setErrorxxx methods are called with causeException=TRUE, then an SQLException is raised when a data value is null or truncated.

The default behavior is to not raise an SQLException. In this case, null data is returned as zero (0) for numeric values and null strings for character values.

For data retrieved through the setDataBuffer method and setDataBufferArray method, exception handling behavior is controlled by the presence or absence of indicator variables and return code variables as shown in Table 2-1, Table 2-2, and Table 2-3.

Table 2-1 Normal Data - Not Null and Not Truncated

Return Code	Indicator - not provided	Indicator - provided
Not provided	error = 0	error = 0 indicator = 0
Provided	error = 0 return code = 0	error = 0 indicator = 0 return code = 0

Table 2-2 Null Data

Return Code	Indicator - not provided	Indicator - provided
Not provided	SQLException error = 1405	error = 0 indicator = -1
Provided	SQLException error = 1405 return code = 1405	error = 0 indicator = -1 return code = 1405

Table 2-3 Truncated Data

Return Code	Indicator - not provided	Indicator - provided
Not provided	SQLException error = 1406	SQLException error = 1406 indicator = data_len
Provided	error = 24345 return code = 1405	error = 24345 indicator = data_len return code = 1406

In Table 2-3, data_len is the actual length of the data that has been truncated if this length is less than or equal to SB2MAXVAL. Otherwise, the indicator is set to -2.

Advanced Relational Techniques

The following advanced techniques are discussed in this section:

Utilizing a Shared Server Environment
Optimizing Performance

Utilizing a Shared Server Environment

Thread Safety

Threads are lightweight processes that exist within a larger process. Threads each share the same code and data segments, but have their own program counters, machine registers, and stack. Global and static variables are common to all threads, and a mutual exclusivity mechanism may be required to manage access to these variables from multiple threads within an application.

Once spawned, threads run asynchronously to one another. They can access common data elements and make OCCI calls in any order. Because of this shared access to data elements, a mechanism is required to maintain the integrity of data being accessed by multiple threads. The mechanism to manage data access takes the form of mutexes (mutual exclusivity locks), which ensure that no conflicts arise between multiple threads that are accessing shared resources within an application. In OCCI, mutexes are granted on an OCCI environment basis.

This thread safety feature of the Oracle database server and OCCI library enables developers to use OCCI in a shared server environment with these added benefits:

Multiple threads of execution can make OCCI calls with the same result as successive calls made by a single thread.
When multiple threads make OCCI calls, there are no side effects between threads.
Even if you do not write a shared server program, you do not pay any performance penalty for including thread-safe OCCI calls.
Use of multiple threads can improve program performance. You can discern gains on multiprocessor systems where threads run concurrently on separate processors, and on single processor systems where overlap can occur between slower operations and faster operations.

Thread Safety and Three-Tier Architectures

In addition to client/server applications, where the client can be a shared server program, a typical use of shared server applications is in three-tier (also called client-agent-server) architectures. In this architecture, the client is concerned only with presentation services. The agent (or application server) processes the application logic for the client application. Typically, this relationship is a many-to-one relationship, with multiple clients sharing the same application server.

The server tier in the three-tier architecture is an Oracle database server. The applications server (agent) is very well suited to being a shared server application server, with each thread serving a client application. In an Oracle environment, this middle-tier application server is an OCCI or precompiler program.

Implementing Thread Safety

In order to take advantage of thread safety by using OCCI, an application must be running on a thread-safe platform. Then the application must inform OCCI that the application is running in shared server mode by specifying THREADED_MUTEXED or THREADED_UNMUTEXED for the mode parameter of the createEnvironment method. For example, to turn on mutual exclusivity locking, issue the following statement:

Environment *env = Environment::createEnvironment(Environment::THREADED_
MUTEXED);

Note that once createEnvironment is called with THREADED_MUTEXED or THREADED_UNMUTEXED, all subsequent calls to the createEnvironment method must also be made with THREADED_MUTEXED or THREADED_UNMUTEXED modes.

If a shared server application is running on a thread-safe platform, then the OCCI library will manage mutexes for the application on a for each-OCCI-environment basis. However, you can override this feature and have your application maintain its own mutex scheme. This is done by specifying a mode value of THREADED_UNMUTEXED to the createEnvironment method.

Note:

Applications running on non-thread-safe platforms should not pass a value of THREADED_MUTEXED or THREADED_UNMUTEXED to the createEnvironment method.
If an application is single threaded, whether or not the platform is thread safe, the application should pass a value of Environment::DEFAULT to the createEnvironment method. This is also the default value for the mode parameter. Single threaded applications which run in THREADED_MUTEXED mode may incur performance degradation.

Shared Server Concurrency

As an application programmer, you have two basic options regarding concurrency in a shared server environment:

Automatic serialization, in which you utilize OCCI's transparent mechanisms
Application-provided serialization, in which you manage the contingencies involved in maintaining multiple threads

Automatic Serialization

In cases where there are multiple threads operating on objects (connections and connection pools) derived from an OCCI environment, you can elect to let OCCI serialize access to those objects. The first step is to pass a value of THREADED_MUTEXED to the createEnvironment method. At this point, the OCCI library automatically acquires a mutex on thread-safe objects in the environment.

When the OCCI environment is created with THREADED_MUTEXED mode, then only the Environment, Map, ConnectionPool, and Connection objects are thread-safe. That is, if two threads make simultaneous calls on one of these objects, then OCCI serializes them internally. However, note that all other OCCI objects, such as Statement, ResultSet, SQLException, Stream, and so on, are not thread-safe as, applications should not operate on these objects simultaneously from multiple threads.

Note that the bulk of processing for an OCCI call happens on the server, so if two threads that use OCCI calls go to the same connection, then one of them could be blocked while the other finishes processing at the server.

Application-Provided Serialization

In cases where there are multiple threads operating on objects derived from an OCCI environment, you can chose to manage serialization. The first step is to pass a value of THREADED_UNMUTEXED for the createEnvironment mode. In this case the application must mutual exclusively lock OCCI calls made on objects derived from the same OCCI environment. This has the advantage that the mutex scheme can be optimized based on the application design to gain greater concurrency.

When an OCCI environment is created in this mode, OCCI recognizes that the application is running in a shared server environment, but that OCCI need not acquire its internal mutexes. OCCI assumes that all calls to methods of objects derived from that OCCI environment are serialized by the application. You can achieve this two different ways:

Each thread has its own environment. That is, the environment and all objects derived from it (connections, connection pools, statements, result sets, and so on) are not shared across threads. In this case your application need not apply any mutexes.
If the application shares an OCCI environment or any object derived from the environment across threads, then it must serialize access to those objects (by using a mutex, and so on) such that only one thread is calling an OCCI method on any of those objects.

Basically, in both cases, no mutexes are acquired by OCCI. You must ensure that only one OCCI call is in process on any object derived from the OCCI environment at any given time when THREADED_UNMUTEXED is used.

Note:

OCCI is optimized to reuse handles as much as possible. Since each environment has its own heap, multiple environments result in increased consumption of memory. Having multiple environments may imply duplicating work with regard to connections, connection pools, statements, and result set objects. This will result in further memory consumption.
Having multiple connections to the server results in more resource consumptions on the server and network. Having multiple environments would normally entail more connections.

Optimizing Performance

When you provide data for bind parameters by the setxxx methods in parameterized statements, the values are copied into an internal data buffer, and the copied values are then provided to the database server for insertion. This data copying may be expensive, especially if large strings are involved. Also, for each new value, the string is reallocated, so there may be memory management overhead in repeated allocation and deallocation of strings.

For these reasons, OCCI provides several methods to help counter these performance drains. These methods are:

setDataBuffer
executeArrayUpdate
next (of the ResultSet class)

setDataBuffer Method

For high performance applications, OCCI provides the setDataBuffer method whereby the data buffer is managed by the application. The following example shows the setDataBuffer method:

void setDataBuffer(int paramIndex,
   void *buffer,
   Type type,
   sb4 size,
   ub2 *length,
   sb2 *ind = NULL, 
   ub2 *rc = NULL);

The following parameters are used in the previous method example:

paramIndex: Parameter number
buffer: Data buffer containing data
type: Type of the data in the data buffer
size: Size of the data buffer
length: Current length of data in the data buffer
ind: Indicator information. This indicates whether the data is NULL or not. For parameterized statements, a value of -1 means a NULL value is to be inserted. For data returned from callable statements, a value of -1 means NULL data is retrieved.
rc: Return code. This variable is not applicable to data provided to the Statement method. However, for data returned from callable statements, the return code specifies parameter-specific error numbers.

Not all datatypes can be provided and retrieved by means of the setDataBuffer method. For instance, C++ Standard Library strings cannot be provided with the setDataBuffer interface. Currently, only the following types can be provided or retrieved:

`OCCI_SQLT_CHR`	`OCCI_SQLT_NUM`	`OCCIINT`
`OCCIFLOAT`	`OCCI_SQLT_STR`	`OCCI_SQLT_VNU`
`OCCI_SQLT_PDN`	`OCCI_SQLT_LNG`	`OCCI_SQLT_VCS`
`OCCI_SQLT_NON`	`OCCI_SQLT_RID`	`OCCI_SQLT_DAT`
`OCCI_SQLT_VBI`	`OCCI_SQLT_BIN`	`OCCI_SQLT_LBI`
`OCCIUNSIGNED_INT`	`OCCI_SQLT_SLS`	`OCCI_SQLT_LVC`
`OCCI_SQLT_LVB`	`OCCI_SQLT_AFC`	`OCCI_SQLT_AVC`
`OCCI_SQLT_CUR`	`OCCI_SQLT_RDD`	`OCCI_SQLT_LAB`
`OCCI_SQLT_OSL`	`OCCI_SQLT_NTY`	`OCCI_SQLT_REF`
`OCCI_SQLT_CLOB`	`OCCI_SQLT_BLOB`	`OCCI_SQLT_BFILEE`
`OCCI_SQLT_CFILEE`	`OCCI_SQLT_RSET`	`OCCI_SQLT_NCO`
`OCCI_SQLT_VST`	`OCCI_SQLT_ODT`	`OCCI_SQLT_DATE`
`OCCI_SQLT_TIME`	`OCCI_SQLT_TIME_TZ`	`OCCI_SQLT_TIMESTAMP`
`OCCI_SQLT_TIMESTAMP_TZ`	`OCCI_SQLT_INTERVAL_YM`	`OCCI_SQLT_INTERVAL_DS`
`OCCI_SQLT_TIMESTAMP_LTZ`	`OCCI_SQLT_FILE`	`OCCI_SQLT_CFILE`
`OCCI_SQLT_BFILE`

There is an important difference between the data provided by the setxxx methods and setDataBuffer method. When data is copied in the setxxx methods, the original can change once the data is copied. For example, you can use a setString(str1) method, then change the value of str1 prior to execute. The value of str1 that is used is the value at the time setString(str1) is called. However, for data provided by means of the setDataBuffer method, the buffer must remain valid until the execution is completed.

If iterative executes or the executeArrayUpdate method is used, then data for multiple rows and iterations can be provided in a single buffer. In this case, the data for the ith iteration is at buffer + (i-1) *size address and the length, indicator, and return codes are at *(length + i), *(ind + i), and *(rc + i) respectively.

This interface is also meant for use with array executions and callable statements that have array or OUT bind parameters.

The same method is available in the ResultSet class to retrieve data without re-allocating the buffer for each fetch.

executeArrayUpdate Method

If all data is provided with the setDataBuffer methods or output streams (that is, no setxxx methods besides setDataBuffer or getStream are called), then there is a simplified way of doing iterative execution.

In this case, you should not call setMaxIterations and setMaxParamSize. Instead call the setDataBuffer (or getStream) method for each parameter with the appropriate size arrays to provide data for each iteration, followed by the executeArrayUpdate(int arrayLength) method. The arrayLength parameter specifies the number of elements provided in each buffer. Essentially, this is same as setting the number of iterations to arrayLength and executing the statement.

Since the stream parameters are specified only once, they can be used with array executes as well. However, if any setxxx methods are used, then the addIteration method is called to provide data for multiple rows. To compare the two approaches, consider an example that inserts two employees in the emp table:

Statement *stmt = conn->createStatement("insert into emp (id, ename)
                                         values(:1, :2)"); 
char enames[2][] = {"SMITH", "MARTIN"};
ub2 enameLen[2];
for (int i = 0; i < 2; i++)
   enameLen[i] = strlen(enames[i] + 1);
stmt->setMaxIteration(2);             // set maximum number of iterations
stmt->setInt(1, 7369);                // specify data for the first row
stmt->setDataBuffer(2, enames, OCCI_SQLT_STR, sizeof(ename[0]), &enameLen);
stmt->addIteration();
stmt->setInt(1, 7654);                // specify data for the second row
// a setDatBuffer is unnecessary for the second bind parameter as data 
// provided through setDataBuffer is specified only once.
stmt->executeUpdate();

However, if the first parameter could also be provided through the setDataBuffer interface, then, instead of the addIteration method, you would use the executeArrayUpdate method:

stmt ->setSQL("insert into emp (id, ename) values (:1, :2)");
char enames[2][] = {"SMITH", "MARTIN"};
ub2 enameLen[2];
for (int i = 0; i < 2; i++)
   enameLen[i] = strlen(enames[i] + 1);
int ids[2] = {7369, 7654}; 
ub2 idLen[2] = {sizeof(ids[0], sizeof(ids[1])};
stmt->setDataBuffer(1, ids, OCCIINT, sizeof(ids[0]), &idLen);
stmt->setDataBuffer(2, enames, OCCI_SQLT_STR, sizeof(ename[0]), &len);
stmt->executeArrayUpdate(2);          // data for two rows is inserted.

Array Fetch Using next Method

If the application is fetching data with only the setDataBuffer interface or the stream interface, then an array fetch can be executed. The array fetch is implemented by calling the ResultSet->next(int numRows) method. This causes up to numRows amount of data is fetched for each column. The buffers specified with the setDataBuffer interface should be big enough to hold data for multiple rows. Data for the ith row is fetched at buffer + (i - 1) * size location. Similarly, the length of the data is stored at *(length + (i - 1)).

int empno[5];
char ename[5][11];
ub2  enameLen[5];
ResultSet *resultSet = stmt->executeQuery("select empno, ename  from emp");
resultSet->setDataBuffer(1, &empno, OCCIINT);
resultSet->setDataBuffer(2, ename, OCCI_SQLT_STR, sizeof(ename[0]), enameLen);
rs->next(5);           // fetches five rows, enameLen[i] has length of ename[i]

2 Relational Programming

Connecting to a Database

Creating and Terminating an Environment

Opening and Closing a Connection

Creating a Connection Pool

Creating a Connection Pool

Proxy Connections

Executing SQL DDL and DML Statements

Creating a Statement Handle

Creating a Statement Handle to Execute SQL Commands

Creating a Database Table

Inserting Values into a Database Table

Reusing a Statement Handle

SHARED Mode

Terminating a Statement Handle

Types of SQL Statements in the OCCI Environment

Standard Statements

Parameterized Statements

Callable Statements

Callable Statements with Arrays as Parameters

Streamed Reads and Writes

Modifying Rows Iteratively

Setting the Maximum Number of Iterations

Setting the Maximum Parameter Size

Executing an Iterative Operation

Iterative Execution Usage Notes

Executing SQL Queries

Result Set

Specifying the Query

Optimizing Performance by Setting Prefetch Count

Executing Statements Dynamically

Status Definitions

UNPREPARED

PREPARED

RESULT_SET_AVAILABLE

UPDATE_COUNT_AVAILABLE

NEEDS_STREAM_DATA

STREAM_DATA_AVAILABLE

Committing a Transaction

Error Handling

Null and Truncated Data

Table 2-1 Normal Data - Not Null and Not Truncated

Table 2-2 Null Data

Table 2-3 Truncated Data

Advanced Relational Techniques

Utilizing a Shared Server Environment

Thread Safety

Thread Safety and Three-Tier Architectures

Implementing Thread Safety

Shared Server Concurrency

Automatic Serialization

Application-Provided Serialization

Optimizing Performance

setDataBuffer Method

executeArrayUpdate Method

Array Fetch Using next Method

2
Relational Programming