Example 3-1 How to Create an OCCI Environment

Environment *env = Environment::createEnvironment();

Example 3-2 How to Terminate an OCCI Environment

Environment::terminateEnvironment(env);

Example 3-3 How to Use Environment Scope with Blob Objects

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

Example 3-4 How to Create an Environment and then a Connection to the Database

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

Example 3-5 How to Terminate a Connection to the Database and the Environment

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

Example 3-9 How to Use a StatelessConnectionPool

Because the pool size is dynamic, in response to changing user requirements, up to the specified maximum number of connections. Assume that a stateless connection pool is created with the following parameters:

• minConn  =  5

• incrConn =  2

• maxConn  = 10

Five connections are opened when the pool is created:

• openConn =  5

Using get[AnyTagged][Proxy]Connection() methods, the user consumes all 5 open connections:

• openConn =  5

• busyConn =  5

When the user wants another connection, the pool opens 2 new connections and returns one of them to the user.

• openConn =  7

• busyConn =  6

The upper limit for the number of connections that can be pooled is maxConn specified at the time of creation of the pool.

The user can also modify the pool parameters after the pool is created using the call to setPoolSize() method.

If a heterogeneous pool is created, the incrConn and minConn arguments are ignored.

Example 3-10 How to Create and Use a Homogeneous Stateless Connection Pool

To create a homogeneous stateless connection pool, follow these basic steps and pseudocode commands:

1. Create a stateless connection pool in the HOMOGENEOUS mode of the Environment with a createStatelessConnectionPool() call.

   StatelessConnectionPool *scp = 
         env->createStatelessConnectionPool(
            username, passwd, connectString, maxCon, minCon, incrCon,
            StatelessConnectionPool::HOMOGENEOUS );
   

2. Get a new or existing connection from the pool by calling the getConnection() method.

   Connection *conn=scp->getConnection(tag);
   

   During the execution of this call, the pool is searched for a connection with a matching tag. If such a connection exists, it is returned to the user. Otherwise, an untagged connection authenticated by the pool username and password is returned.

   Alternatively, you can obtain a connection with getAnyTaggedConnection() call. It returns a connection with a non-matching tag if neither a matching tag or NULL tag connections are available. You should verify the tag returned by a getTag() call on Connection.

   Connection *conn=scp->getAnyTaggedConnection(tag);
   string tag=conn->getTag();
   

3. Use the connection.

4. Release the connection to the StatelessConnectionPool through the releaseConnection() call.

   scp->releaseConnection(conn, tag);
   

   An empty tag, "", untags the Connection.

   You have an option of retrieving the connection from the StatelessConnectionPool using the same tag parameter value in a getConnection() call.

   Connection *conn=scp->getConnection(tag);
   

   Instead of returning the Connection to the StatelessConnectionPool, you may want to destroy it using the terminateConnection() call.

   scp->terminateConnection(conn);
   

5. Destroy the pool through aterminateStatelessConnectionPool() call on the Environment object.

   env->terminateStatelessConnectionPool(scp);

Example 3-11 How to Create and Use a Heterogeneous Stateless Connection Pool

To create a heterogeneous stateless connection pool, follow these basic steps and pseudocode commands:

1. Create a stateless connection pool in the HETEROGENEOUS mode of the Environment with a createStatelessConnectionPool() call.

   StatelessConnectionPool *scp = 
         env->createStatelessConnectionPool(
            username, passwd, connectString, maxCon, minCon, incrCon, 
            StatelessConnectionPool::HETEROGENEOUS);
   

   If you are enabling external authentication, you must also activate the USES_EXT_AUTH mode in the createStatelessConnectionPool() call.

   StatelessConnectionPool *scp = 
         env->createStatelessConnectionPool(
            username, passwd, connectString, maxCon, minCon, incrCon, 
            StatelessConnectionPool::PoolType(
               StatelessConnectionPool::USES_EXT_AUTH|
               StatelessConnectionPool::HETEROGENEOUS));
   

2. Get a new or existing connection from the pool by calling the getConnection() method of the StatelessConnectionPool that is overloaded for the heterogeneous pool option.

   Connection *conn=scp->getConnection(username, passwd, tag);
   

   During the execution of this call, the heterogeneous pool is searched for a connection with a matching tag. If such a connection exists, it is returned to the user. Otherwise, an appropriately authenticated untagged connection with a NULL tag is returned.

   Alternatively, you can obtain a connection with getAnyTaggedConnection() call that has been overloaded for heterogeneous pools. It returns a connection with a non-matching tag if neither a matching tag or NULL tag connections are available. You should verify the tag returned by a getTag() call on Connection.

   Connection *conn=scp->getAnyTaggedConnection(username, passwd, tag);
   string tag=conn->getTag();
   

   You may also want to use proxy connections by getProxyConnection() or getAnyTaggedProxyConnection() calls on the StatelessConnectionPool.

   Connection *pconn = scp->getProxyConnection(proxyName, roles{}, 
                                              nuRoles, tag, proxyType);
   Connection *pconn = scp->getAnyTaggedProxyConnection( proxyName, tag,
                                                        proxyType);
   

   If the pool supports external authentication, use the following getConnection() call:

   Connection *conn=scp->getConnection();
   

3. Use the connection.

4. Release the connection to the StatelessConnectionPool through the releaseConnection() call.

   scp->releaseConnection(conn, tag);
   

   An empty tag, "", untags the Connection.

   You have an option of retrieving the connection from the StatelessConnectionPool using the same tag parameter value in a getConnection() call.

   Connection *conn=scp->getConnection(tag);
   

   Instead of returning the Connection to the StatelessConnectionPool, you may want to destroy it using the terminateConnection() call.

   scp->terminateConnection(conn);
   

5. Destroy the pool through a terminateStatelessConnectionPool() call on the Environment object.

   env->terminateStatelessConnectionPool(scp);

Example 3-15 How to Create a Statement

Statement *stmt = conn->createStatement();

Example 3-18 How to Specify a SQL Statement Using the setSQL() Method

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

Example 3-19 How to Reset a SQL Statement Using the setSQL() Method

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

Example 3-20 How to Terminate a Statement Using the terminateStatement() Method

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

Example 3-21 How to Use setxxx() Methods to Set Individual Column Values

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

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

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

Example 3-22 How to Specify the IN Parameters of a PL/SQL Stored Procedure

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

Example 3-23 How to Specify OUT Parameters of a PL/SQL Stored Procedure

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

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

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

Example 3-28 How to Fetch Data in Streaming Mode Using ResultSet

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

Example 3-29 SELECT with getUb8RowCount(); simple

The number of rows affected is the number of rows already fetched.

oraub8 largeRowCount = 0;
Statement *stmt = conn->createStatement("SELECT salary FROM employees");
ResultSet *rs = stmt->executeQuery ();
rs->next();
largeRowCount = stmt->getUb8RowCount();

Example 3-30 SELECT with getUb8RowCount(); with prefetch

Here the number of rows affected is the same as those fetched in previous iterations, plus the number of rows prefetched in the next() call.

oraub8 largeRowCount = 0;
Statement *stmt = conn->createStatement("SELECT salary FROM employees");
stmt -> setPrefetchRowCount(prefetch_count);
ResultSet *rs = stmt->executeQuery ();
rs->next();
largeRowCount = stmt->getUb8RowCount();

Example 3-31 SELECT with getUb8RowCount(); array fetch with prefetch

Here number of rows affected, value of largeRowCount, is the number of rows fetched into user buffer in previous iterations, plus the number of rows fetched in either next(max) or next() call. It is independent of the value of prefetch.

oraub8 largeRowCount = 0;
Statement *stmt=conn->createStatement("SELECT col1 FROM table1");
int max = 20;
int prefetch_count = 10;
ub2 lengthC1[max];
ub4 c1[max];

for (i = 0; i < max; ++i) {
   c1[i] = 0;
   lengthC1[i] = sizeof (c1[i]);
}

stmt -> setPrefetchRowCount(prefetch_count);
ResultSet *rs = stmt->executeQuery();
rs->setDataBuffer (1, c1, OCCIINT, sizeof (ub4), lengthC1);
rs->next(max);

largeRowCount = stmt->getUb8RowCount();

Example 3-32 INSERT with getUb8RowCount(); simple

The value of largeRowCount is the number of rows inserted, which is 1.

oraub8 largeRowCount = 0;
Statement *stmt = conn->createStatement("INSERT INTO table1 values (:1)");
stmt->setNumber(1, 100);
stmt->executeUpdate();
largeRowCount = stmt->getUb8RowCount();

Example 3-33 INSERT with getUb8RowCount(); with iterations

Here the value of largeRowCount is equal to max.

int max;
oraub8 largeRowCount = 0;
Statement *stmt=conn->createStatement("INSERT INTO table1 values (:1)");
stmt->setMaxIterations (max);

for(i = 0; i < max-1; i++) {
   stmt->setNumber(1, 100);
   stmt->addIteration ();
}

stmt->setNumber(1, 100);
stmt->executeUpdate();
largeRowCount = stmt->getUb8RowCount();

Example 3-34 UPDATE with getUb8RowCount()

Here the value of largeRowCount is the number of rows updated.

oraub8 largeRowCount = 0;
Statement *stmt=conn->createStatement(
   "UPDATE table1 SET COL1 = COL1+100 WHERE COL1=:1");
stmt->setNumber(1, 200);
stmt->executeUpdate();
largeRowCount = stmt->getUb8RowCount();