3 Relational Programming

Creating and Terminating an Environment

All OCCI processing takes place inside the Environment class. An OCCI environment provides application modes and user-specified memory management functions. Example 3-1 illustrates how to create an OCCI environment.

Environment *env = Environment::createEnvironment();

All OCCI objects created with the createxxx() methods (connections, connection pools, statements) must be explicitly terminated. When appropriate, you must also explicitly terminate the environment. Example 3-2 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 inside that environment: Agent, Bytes, Date, Message, IntervalDS, IntervalYM, Subscription, and Timestamp. This rule does not apply to BFile, Blob, and Clob objects, as demonstrated in Example 3-3.

const string userName = "HR";
const string password = "password";
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 before 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 through the createConnection() method.

Example 3-4 creates an environment instance and then uses it to create a database connection for a database user HR with the appropriate password.

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

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. Example 3-5 demonstrates how to terminate the connection and the environment.

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

Using Connection Pools

For many middle-tier applications, connections to the database should be enabled for a large number of threads. Because each thread exists for a relatively short time, opening a connection to the database for every thread would be inefficient use of connections, and would result in poor performance.

By employing the connection pooling feature, your application can create a small set of connections available to a large number of threads, enabling 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 do not 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 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 are authenticated with the username and password provided at the time of creation of the pool. Therefore, all connections 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. Additionally, heterogeneous pools support external authentication.

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

Database Resident Connection Pooling

Enterprise-level applications must typically handle a high volume of simultaneous user sessions that are implemented as persistent connections to the database. The memory overhead of creating and managing these connections has significant implications for the performance of the database.

Database Resident Connection Pooling solves the problem of too many persistent connections by providing a pool of dedicated servers for handling a large set of application connections, thus enabling the database to scale to tens of thousands of simultaneous connections. It significantly reduces the memory footprint on the database tier and increases the scalability of both the database and middle tiers. Database Resident Connection Pooling is designed for architectures with multi-process application servers and multiple middle tiers that cannot accommodate connection pooling in the middle tier.

Database Resident Connection Pooling architecture closely follows the default dedicated model for connecting to an Oracle Database instance; however, it removes the overhead of assigning a specific server to each connection. On the server tier, most connections are inactive at any given time, and each of these connections consumes memory. Therefore, database systems that support high connection volumes face the risk of quickly exhausting all available memory. Database Resident Connection Pooling allows a connection to use a dedicated server, which combines an Oracle server process and a user session. Once the connection becomes inactive, it returns its resources to the pool, for use by similar connections.

In multithreaded middle tiers that are capable of comprehensive connection pooling, the issue of unused connections is somewhat different. As the number of middle tiers increases, each middle tier privately holds several connections to the database; these connections cannot be shared with other middle tiers. Locating the connection pool on the database instead enables the sharing of connections across similar clients.

Database Resident Connection Pooling supports password-based authentication, statement caching, tagging, and Fast Application Notification. You can also use client-side stateless connection pooling with the database resident connection pooling.

Note that clients that hold connections from the database resident connection pool are persistently connected to a background Connection Broker process. The Connection Broker implements the pool functionality and multiplexes inbound client connections to a pool of dedicated server processes. Clients that do not use the connection pool use dedicated server processes instead.

conn1 = env->createConnection (/*username */"hr",
              /*password*/ "password", /* database*/ "inst1_cmon",
              /* connection class */"TESTCC", /* purity */Connection::SELF);
stmt1 = conn1->createStatement("select count(*) from emp");
rs=stmt1->executeQuery();
while (rs->next())
     {
        int num = rs->getInt(1);
        sprintf((char *)tmp, "%d", num);
        cout << tmp << endl;
     }
stmt1->closeResultSet(rs);
conn1->terminateStatement(stmt1);
env->terminateConnection(conn1);

StatelessConnectionPool *scPool;
OCCIConnection *conn1, *conn2;
 scPool = env->createStatelessConnectionPool
      (poolUserName, poolPassword, connectString, maxConn,
       minConn, incrConn, StatelessConnectionPool::HOMOGENEOUS);
 
conn1= scPool->getConnection( /* Connection class name */"TESTCC",
                              /* Purity */ Connection::SELF);
 /* or, for proxy coonnections */
 conn2= scPool->getProxyConnection(/* username*/ "HR_PROXY",
                     /*Connection class */"TESTCC", /* Purity */Connection::SELF);
/* or, for getting a tagged connection */
conn3 = scPool->getConnection(/*connection class */"TESTCC", 
                              /*purity*/ Connection::SELF, 
                              /*tag*/ "TESTTAG");
/* Releasing a tagged connection is done presently */
scPool->releaseConnection(conn3, "TESTTAG");
 
/* To specify purity as new */
conn4 = scPool->getConnection(/* connection class */"TESTCC",/* purity of new */
                              Connection::NEW);
 
/* Get a connection using username and password */
conn5 = scPool->getConnection (username, password,"TESTCC", Connection::SELF);
 
/* Using roles when asking for a connection */
conn6 = scPool->getProxyConnection (username, roles, nRoles,"TESTCC",
                                    Connection::SELF);
 
...
 
/* The other code continues as is...writing for clarity */
   ...
    stmt1=conn1->createStatement  ("INSERT INTO emp values (:c1, :c2)");
    stmt1->setInt(1, thrid);
    stmt1->setString(2, "Test");
    int count = stmt1->executeUpdate ();
    conn1->commit();
    conn1->terminateStatement(stmt1);
/* Release the connection */
    scPool->releaseConnection (conn1);
...
  env->terminateStatelessConnectionPool (scPool);

Creating a Statement Object

To create a Statement object, call the createStatement() method of the Connection object, as demonstrated in Example 3-15,

Statement *stmt = conn->createStatement();

Creating a Statement Object that Executes SQL Commands

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

• execute() executes all nonspecific statement types

• executeUpdate() executes DML and DDL statements

• executeArrayUpdate() executes multiple DML statements

• executeQuery() executes a query

stmt->executeUpdate("CREATE TABLE shopping_basket
   (item_number VARCHAR2(30), quantity NUMBER(3))");

stmt->executeUpdate("INSERT INTO shopping_basket
   VALUES('MANGO', 3)");

Reusing the Statement Object

You can reuse a Statement object to execute SQL statements multiple times. To repeatedly execute the same statement with different parameters, you should specify the statement by the setSQL() method of the Statement object, as demonstrated in Example 3-18.

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

You may now execute this INSERT statement as many times as required. If at a later time you want to execute a different SQL statement, you simply reset the statement object, as demonstrated in Example 3-19.

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

By using the setSQL() method, OCCI statement objects and their associated resources are not allocated or freed unnecessarily. To retrieve the contents of the current statement object at any time, use the getSQL() method.

Terminating a Statement Object

You should explicitly terminate and deallocate a Statement object using the terminateStatement() method, as demonstrated in Example 3-20.

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

Standard Statements

Both Example 3-16 and Example 3-17 demonstrate standard statements in which you must explicitly define the values of the statement. In Example 3-16, the CREATE TABLE statement specifies the name of the table shopping_basket. In Example 3-17, the INSERT statement stipulates the values that are inserted into the table, ('MANGO', 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 can accept parameter input from a user or a program.

If you want to execute an INSERT statement with different parameters, you must first specify the statement by the setSQL() method of the Statement object, as demonstrated in Example 3-18.

You then call the setxxx() methods to specify the parameters, where xxx stands for the type of the parameter. Provided that the value of the statement object is "INSERT INTO shopping_basket VALUES(:1,:2)", as specified in Example 3-18, you can use the code in Example 3-21 to invoke the setString() method and setInt() method to input the values of these types into the first and second parameters, and the executeUpdate() method to insert the new row into the table.You can reuse the statement object by re-setting the parameters and again calling the executeUpdate() method. If your application is executing the same statement repeatedly, you should avoid changing the input parameter types because this initiates a rebind operation, and affects application performance.

stmt->setString(1, "Banana");     // value for first parameter
stmt->setInt(2, 5);               // value for second parameter
stmt->executeUpdate();            // execute statement
...
stmt->setString(1, "Apple");      // value for first parameter
stmt->setInt(2, 9);               // value for second parameter
stmt->executeUpdate();            // execute statement

Callable Statements

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

If you want to call a procedure countGroceries(), that returns the quantity of a specified kind of fruit, you must first specify the input parameters of a PL/SQL stored procedure through the setXXX() methods of the Statement class, as demonstrated in Example 3-22.

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

However, before calling a stored procedure, you must specify the type and size of any OUT parameters by calling the registerOutParam() method, as demonstrated in Example 3-23. 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 data types 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 Stream Class 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() returns 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. 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

Using the 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 13-38.

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

Example 3-28 illustrates how to fetch streaming data into a result set, while section "Streamed Reads and Writes" provides the general background.

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

Prefetching is not in effect if LONG, LOB or Opaque Type columns (such as XMLType) are part of the query.

Statement 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 also returns 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.