2 Relational Programming

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 connectString = "";
unsigned int maxConn = 5;
unsigned int minConn = 3;
unsigned int incrConn = 2;

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

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 OCCI terminates idle connections periodically so as to maintain an optimum number of open connections.

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.

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.

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

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

or

ConnectionPool->createProxyConnection( 
   const string &username,
   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.

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.

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(
   unsigned int paramIndex,
   void *buffer, 
   Type type, 
   ub4 arraySize, 
   ub4 *arrayLength, 
   sb4 elementSize, 
   ub2 *elementLength,
   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

• elementLength: Pointer to an array of lengths. elementLength[i] has the current length of the ith element of the array

• ind: Indicator information

• rc: Return code

Binding Data in a Streaming Mode; SELECT/DML and PL/SQL

To bind data in a streaming mode, follow these steps and review Example 2-3:

1. Create a SELECT/DML or PL/SQL statement with appropriate bind placeholders.

2. Call the setBinaryStreamMode() or setCharacterStreamMode() method of the Statement Class for each bind position that will be used in the streaming mode. If the bind position is a PL/SQL IN or IN/OUT argument type, indicate this by calling the three-argument versions of these methods and setting the inArg parameter to TRUE.

3. Execute the statement; the status() method of the Statement Class will return NEEDS_STREAM_DATA.

4. Obtain the stream object through a getStream() method of the Statement Class.

5. Use writeBuffer() and writeLastBuffer() methods of the StreamClass to write data.

6. Close the stream with closeStream() method of the Statement Class.

7. After all streams are closed, the status() method of the Statement Class will change to an appropriate value, such as UPDATE_COUNT_AVAILABLE.

Statement *stmt = conn->createStatement(
   "Insert Into testtab(longcol) values (:1)");   //longcol is LONG type column
stmt->setCharacterStreamMode(1, 100000);
stmt->executeUpdate();

Stream *instream = stmt->getStream(1);
char buffer[1000];
instream->writeBuffer(buffer, len);               //write data
instream->writeLastBuffer(buffer, len);           //repeat
stmt->closeStream(instream);                      //stmt->status() is
                                                  //UPDATE_COUNT_AVAILABLE

Statement *stmt = conn->createStatement("BEGIN testproc(:1); END;");

//if the argument type to testproc is IN or IN/OUT then pass TRUE to
//setCharacterStreamMode or setBinaryStreamMode 
stmt->setBinaryStreamMode(1, 100000, TRUE);

Fetching Data in a Streaming Mode: PL/SQL

To fetch data from a streaming mode, follow these steps and review Example 2-4:

1. Create a SELECT/DML statement with appropriate bind placeholders.

2. Call the setBinaryStreamMode() or setCharacterStreamMode() method of the Statement Class for each bind position into which data will be retrieved from the streaming mode.

3. Execute the statement; the status() method of the Statement Class will return STREAM_DATA_AVAILABLE.

4. Obtain the stream object through a getStream() method of the Statement Class.

5. Use readBuffer() and readLastBuffer() methods of the StreamClass to read data.

6. Close the stream with closeStream() method of the Statement Class.

Statement *stmt = conn->createStatement("BEGIN testproc(:1); END;");
                           //argument 1 is OUT type
stmt->setCharacterStreamMode(1, 100000);
stmt->execute();

Stream *outarg = stmt->getStream(1);
                           //use Stream::readBuffer/readLastBuffer to read data

Fetching Data in Streaming Mode: ResultSet

Executing SQL Queries and Example 2-6 provide an explanation of how to use the streaming interface with result sets.

Working with Multiple Streams

If you have to work with multiple read and write streams, you have to ensure that the read or write of one stream is completed prior to reading or writing on another stream. To determine stream position, use the getCurrentStreamParam() method of the Statement Class or ResultSet Class. Example 2-5 illustrates how to work with concurrent streams.

Statement *stmt = conn->createStatement(
   "Insert into testtab(longcol1, longcal2) values (:1,:2)");
                                           //longcol1 AND longcol2 are 2 columns
                                           //inserted in streaming mode
stmt->setBinaryStreamMode(1, 100000);
stmt->setBinaryStreamMode(2, 100000);
stmt->executeUpdate();

Stream *col1 = stmt->getStream(1);
Stream *col2 = stmt->getStream(2);

col1->writeBuffer(buffer, len);            //first stream
...                                        //complete writing col1 stream
col1->writeLastBuffer(buffer, len);        //first and then move to col2

col2->writeBuffer(buffer, len);            //second stream
...

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.

UNPREPARED

If you have not used the setSQL() method to attribute a SQL string to a statement object, 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 an 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 object 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 this 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 where 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, the getCurrentStreamIteration() method reveals to which iteration the data is to be written.

Once all the stream data has been processed, 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.

Overview ofThread 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 multithreaded application 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 multithreaded 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 multithreaded program, thread safety is typically used in three-tier or 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) supports multithreading, with each thread serving a separate 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 in a thread-safe operating system. Then the application must inform OCCI that the application is running in multithreaded 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 multithreaded application is running in a thread-safe operating system, 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.

Serialization

As an application programmer, you have two basic options regarding concurrency in a multithreaded application:

• Automatic serialization, in which you utilize OTIS's transparent mechanisms

• Application-provided serialization, in which you manage the contingencies involved in maintaining multiple threads

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.

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 to be fetched for each column. The buffers specified with the setDataBuffer() interface should be big enough to hold data for multiple rows. Data for the i^th 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]