3 Relational Programming

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 the following object types created in the context of that environment: Agent, Bytes, Date, Message, IntervalDS, IntervalYM, Subscription and Timestamp. This rule does not apply to BFile, Blob, and Clob objects. 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);

If the application requires access to objects in the global scope, such as static or global variables, these objects must be set to NULL before the environment is terminated. In the preceding example, if b was a global variable, a b.setNull() call has to be made prior to the terminateEnvironment() call.

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)

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.

You should remember that all objects (Refs, Bfiles, Producers, Consumers, and so on) created or named within a Connection instance must be within the inner scope of that instance; the scope of these objects must be explicitly terminated before the Connection is terminated.

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

Creating a Connection Pool

For many middle-tier applications, connections to the database should be enabled for a large number of threads. Since each thread exists for a relatively short time, 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 create a small set of connections that can be used by a large number of threads. This enables you to use database resources very efficiently.

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;

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);

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

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

Stateless Connection Pooling

Stateless Connection Pooling is specifically designed for use in applications that require short connection times and don't need to deal with state considerations. The primary benefit of Stateless Connection Pooling is increased performance, since the time consuming connection and authentication protocols are eliminated.

Stateless Connection Pools create and maintain a group of stateless, authenticated connection to the database that can be used by multiple threads. Once a thread finishes using its connection, it should release the connection back to the pool. If no connections are available, new ones are generated. Thus, the number of connections in the pool can increase dynamically.

Some of the connections in the pool may be tagged with specific properties. The user may request a default connection, set certain attributes, such as Globalization Support settings, then tag it and return it to the pool. When a connection with same attributes is needed, a request for a connection with the same tag can be made, and one of several connections in the pool with the same tag can be reused. The tag on a connection can be changed or reset.

Proxy connections may also be created and maintained through the Stateless Connection Pooling interface.

Stateless connection pooling improves the scalability of the mid-tier applications by multiplexing the connections. However, connections from a StatelessConnectionPool should not be used for long transactions, as holding connections for long periods leads to reduced concurrency.

There are two types of stateless connection pools:

• A homogeneous pool is one in which all the connections will be authenticated with the username and password provided at the time of creation of the pool. Therefore, all connections will have the same authentication context. Proxy connections are not allowed in such pools.

• Different connections can be authenticated by different usernames in heterogeneous pools. Proxy connections can also exist in heterogeneous pools, provided the necessary privileges for creating them are granted on the server.

Example 3-1 illustrates a basic usage scenario for connection pools. Example 3-2 presents the usage scenario for creating and using a homogeneous stateless connection pool, while Example 3-3 covers the use of heterogeneous pools.

Creating a Statement Object

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

Statement *stmt = conn->createStatement();

Creating a Statement Object to Execute SQL Commands

Once you have created a Statement object, 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

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

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

Reusing a Statement Object

You can reuse a Statement object 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 object:

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 object. For example:

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

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

Terminating a Statement Object

You should explicitly terminate and deallocate a Statement:

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

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 object:

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 first (IN) parameter of procedure

However, before calling a stored procedure, you need to specify the type and size of any OUT parameters by calling the registerOutParam() method. For IN/OUT parameters, use the setXXX() methods to pass in the parameter, and getXXX() methods to retrieve the results.

stmt->registerOutParam(2, Type::OCCIINT, sizeof(quantity));
   // specify 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 value of the second (OUT) parameter

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

Streamed Reads and Writes

OCCI supports a streaming interface for insertion and retrieval of very large columns by breaking the data into a series of small chunks. This approach minimizes client-side memory requirements. This streaming interface can be used with parameterized statements such as SELECT and various DML commands, and with callable statements in PL/SQL blocks. The datatypes supported by streams are BLOB, CLOB, LONG, LONG RAW, RAW, and VARCHAR2.

Streamed data is of three kinds:

• A writable stream corresponds to a bind variable in a SELECT/DML statement or an IN argument in a callable statement.

• A readable stream corresponds to a fetched column value in a SELECT statement or an OUT argument in a callable statement.

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

Methods of the StreamClass support the stream interface.

The getStream() method of the Statement Class returns a stream object that supports reading and writing for DML and callable statements:

• For writing, it passes data to a bind variable or to an IN or IN/OUT argument

• For reading, it fetches data from an OUT or IN/OUT argument

The getStream() method of the ResultSet Class returns a stream object that can be used for reading data.

The status() method of these classes determines the status of the streaming operation.

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);

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

Statement *stmt = conn->createStatement(
  "Insert into testtab(longcol1, longcol2) 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 coll stream

col1->writeLastBuffer(buffer, len);    //finish first stream and move to col2

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

//reading multiple streams
stmt = conn->createStatement("select longcol1, longcol2 from testtab");
ResultSet *rs = stmt->executeQuery();
rs->setBinaryStreamMode(1, 100000);
rs->setBinaryStreamMode(2, 100000);

while (rs->next())
{
   Stream *s1 = rs->getStream(1)
   while (s1->status() == Stream::READY_FOR_READ)
   {
      s1->readBuffer(buffer,size);    //process
   }                                  //first streaming column done
   rs->closeStream(s1);

//move onto next column. rs->getCurrentStreamColumn() will return 2
 
   Stream *s2 = rs->getStream(2)
   while (s2->status() == Stream::READY_FOR_READ)
   {
      s2->readBuffer(buffer,size);    //process
   }                                  //close the stream
   rs->closeStream(s2);
}

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

Statement->setMaxIterations(int maxIterations);

Statement->setMaxParamSize(int parameterIndex, int maxParamSize);

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

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 Status, as defined in Table 12-37.

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 streamed data are read succcessfully.

Example 3-7 illustrates how to fetchstreaming data into a result set, while section "Streamed Reads and Writes" provides the general backgound.

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

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 through the setPrefetchRowCount() method, or by setting the memory size to be used for prefetching through the setPrefetchMemorySize() method.

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

Status Definitions

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

• UNPREPARED

• PREPARED

• RESULT_SET_AVAILABLE

• UPDATE_COUNT_AVAILABLE

• NEEDS_STREAM_DATA

• STREAM_DATA_AVAILABLE

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

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

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

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

ResultSet *rs = Statement->getResultSet();

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 

Statement->getUpdateCount();

Handling 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 not to raise an SQLException. A column or parameter value can also be NULL, as determined by a call to isNull() for a ResultSet or Statement object returning TRUE:

rs->isNull(columnIndex);
stmt->isNull(paramIndex);

If the column or parameter value is truncated, it will also return TRUE as determined by a isTruncated() call on a ResultSet or Statement object:

rs->isTruncated(columnIndex);
stmt->isTruncated(paramIndex);

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 3-1, Table 3-2, and Table 3-3.

error = 0

error = 0
indicator = 0

error = 0
return code = 0

error = 0
indicator = 0
return code = 0

SQLException
error = 1405

error = 0
indicator = -1

SQLException
error = 1405
return code = 1405

error = 0
indicator = -1
return code = 1405

SQLException
error = 1406

SQLException
error = 1406
indicator = data_len

error = 24345
return code = 1405

error = 24345
indicator = data_len
return code = 1406

In Table 3-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.