| Oracle® C++ Call Interface Programmer's Guide 10g Release 1 (10.1) Part Number B10778-01 |
|
|
View PDF |
| Oracle® C++ Call Interface Programmer's Guide 10g Release 1 (10.1) Part Number B10778-01 |
|
|
View PDF |
This chapter describes the basics of developing C++ applications using Oracle C++ Call Interface (OCCI) to work with data stored in relational databases.
This chapter contains these topics:
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:
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 that environment, such as Bytes, BFile, Blob, Clob, IntervalDS, IntervalYM, and Timestamp. 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.
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);
This section discusses how to use the connection pooling feature of OCCI. The information covered includes the following topics:
The primary difference between the two is that StatelessConnectionPools are used for applications that don't depend on state considerations; these applications can benefit from performance improvements available through use of pre-authenticated connections.
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.
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.
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.
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.
|
Caution:
|
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. Proxy connections can also exist in such pools.
Example 2-1 presents the usage scenario for creating and using a homogeneous stateless connection pool, while Example 2-2 covers the use of heterogeneous pools.
Example 2-1 How to Create and Use a Homogeneous Stateless Connection Pool
To create a homogeneous stateless connection pool, follow these basic steps and pseudocode commands:
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 );
Get a new or existing connection from the pool by calling the getConnection() method of the StatelessConnectionPool.
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 appropriately authenticated untagged connection with a NULL tag is returned.
Alternatively, you can obtain a connection with getAnyTaggedConnection() call. It will return 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();
Use the connection.
Release the connection to the StatelessConnectionPool through the releaseConnection() call.
scp->releaseConnection(conn, tag1);
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 *conn1=scp->getConnection(tag1);
Instead of returning the Connection to the StatelessConnectionPool, you may wish to destroy it using the terminateConnection() call.
scp->terminateConnection(conn1);
Destroy the pool through aterminateStatelessConnectionPool() call on the Environment object.
env->terminateStatelessConnectionPool(scp);
Example 2-2 How to Create and Use a Heterogeneous Stateless Connection Pool
To create a heterogeneous stateless connection pool, follow these basic steps and pseudocode commands:
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, HETEROGENEOUS);
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 will return 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 wish to use proxy connections by getProxyConnection() or getAnyTaggedProxyConnection() calls on the StatelessConnectionPool.
Connection *pcon = scp->getProxyConnection(proxyName, roles{},
nuRoles, tag, proxyType);
Connection *pcon = scp->getAnyTaggedProxyConnection( proxyName, tag,
proxyType);
Use the connection.
Release the connection to the StatelessConnectionPool through the releaseConnection() call.
scp->releaseConnection(conn, tag1);
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 *conn1=scp->getConnection(tag1);
Instead of returning the Connection to the StatelessConnectionPool, you may wish to destroy it using the terminateConnection() call.
scp->terminateConnection(conn1);
Destroy the pool through a terminateStatelessConnectionPool() call on the Environment object.
env->terminateStatelessConnectionPool(scp);
SQL is the industry-wide language for working with relational databases. In OCCI you execute SQL commands by means of the Statement class.
To create a Statement object, call the createStatement() method of the Connection object, as shown in the following example:
Statement *stmt = conn->createStatement();
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
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)");
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: $ORACLE_HOME/rdbms/demo for a code example that demonstrates how to perform insert, select, update, and delete operations on the table row. |
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.
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.
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).
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.
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 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 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
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
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.
To bind data in a streaming mode, follow these steps and review Example 2-3:
Create a SELECT/DML or PL/SQL statement with appropriate bind placeholders.
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.
Execute the statement; the status() method of the Statement Class will return NEEDS_STREAM_DATA.
Obtain the stream object through a getStream() method of the Statement Class.
Use writeBuffer() and writeLastBuffer() methods of the StreamClass to write data.
Close the stream with closeStream() method of the Statement Class.
After all streams are closed, the status() method of the Statement Class will change to an appropriate value, such as UPDATE_COUNT_AVAILABLE.
Example 2-3 How to Bind Data in a Streaming Mode
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);
To fetch data from a streaming mode, follow these steps and review Example 2-4:
Create a SELECT/DML statement with appropriate bind placeholders.
Call the setBinaryStreamMode() or setCharacterStreamMode() method of the Statement Class for each bind position into which data will be retrieved from the streaming mode.
Execute the statement; the status() method of the Statement Class will return STREAM_DATA_AVAILABLE.
Obtain the stream object through a getStream() method of the Statement Class.
Use readBuffer() and readLastBuffer() methods of the StreamClass to read data.
Close the stream with closeStream() method of the Statement Class.
Example 2-4 How to Fetch Data in a Streaming Mode Using PL/SQL
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
Executing SQL Queries and Example 2-6 provide an explanation of how to use the streaming interface with result sets.
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.
Example 2-5 How to Work with Multiple 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
...
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
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.
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.
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 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.
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.
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.
Example 2-6 illustrates the previously described steps.
Example 2-6 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;
}
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;
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.
|
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. |
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() method.
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 object 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
This section describes the possible values of Status related to a statement object:
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
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
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();
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.
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.
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.
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);
The statement caching feature establishes and manages a cache of statements within a session. It improves performance and scalability of application by efficiently using prepared cursors on the server side and eliminating repetitive statement parsing.
Statement caching can be used with connection and session pooling, and also without connection pooling. Please review Example 2-7 and Example 2-8 for typical usage scenarios.
Example 2-7 Statement Caching without Connection Pooling
These steps and accompanying pseudocode implement the statement caching feature without use of connection pools:
Create a Connection by making a createConnection() call on the Environment object.
Connection *conn = env->createConnection(username, password, connecstr);
Enable statement caching on the Connection object by using a nonzero size parameter in the setStmtCacheSize() call.
conn->setStmtCacheSize(10);
Subsequent calls to getStmtCacheSize() would determine the size of the cache, while setStmtCacheSize() call changes the size of the statement cache, or disables statement caching if the size parameter is set to zero.
Create a Statement by making a createStatement() call on the Connection object; the Statement is returned if it is in the cache already, or a new Statement with a NULL tag is created for the user.
Statement *stmt = conn->createStatement(sql);
To retrieve a previously cached tagged statement, use the alternate form of the createStatement() method:
Statement *stmt = conn->createStatement(sql, tag);
Use the statement to execute SQL commands and obtain results.
Return the statement to cache.
conn->terminateStatement(stmt, tag);
If you don't want to cache this statement, use the disableCaching() call and an alternate from of terminateStatement():
stmt->disableCaching(); conn->terminateStatement(stmt);
If you need to verify whether a statement has been cached, issue an isCached() call on the Connection object.
Terminate the connection.
Example 2-8 Statement Caching with Connection Pooling
These steps and accompanying pseudocode implement the statement caching feature with connection pooling:
Create a ConnectionPool by making a call to the createStatelessConnectionPool() of the Environment object.
ConnectionPool *conPool = env->createConnectionPool(
username, password, connecstr,
minConn, maxConn, incrConn);
If using a StatelessConnectionPool, call createStatelessConnectionPool() instead. Subsequent operations are the same for ConnectionPool and StatelessConnectionPool objects.
Stateless ConnectionPool *conPool = env->createStatelessConnectionPool(
username, password, connecstr,
minConn, maxConn, incrConn, mode);
Enable statement caching for all Connections in the ConnectionPool by using a nonzero size parameter in the setStmtCacheSize() call.
conPool->setStmtCacheSize(10);
Subsequent calls to getStmtCacheSize() would determine the size of the cache, while setStmtCacheSize() call changes the size of the statement cache, or disables statement caching if the size parameter is set to zero.
Get a Connection from the pool by making a createConnection() call on the ConnectionPool object; the Statement is returned if it is in the cache already, or a new Statement with a NULL tag is created for the user.
Connection *conn = conPool->createConnection(username, password, connecstr);
To retrieve a previously cached tagged statement, use the alternate form of the createStatement() method:
Statement *stmt = conn->createStatement(sql, tag);
Create a Statement by making a createStatement() call on the Connection object; the Statement is returned if it is in the cache already, or a new Statement with a NULL tag is created for the user.
Statement *stmt = conn->createStatement(sql);
To retrieve a previously cached tagged statement, use the alternate form of the createStatement() method:
Statement *stmt = conn->createStatement(sql, tag);
Use the statement to execute SQL commands and obtain results.
Return the statement to cache.
conn->terminateStatement(stmt, tag);
If you don't want to cache this statement, use the disableCaching() call and an alternate from of terminateStatement():
stmt->disableCaching(); conn->terminateStatement(stmt);
If you need to verify whether a statement has been cached, issue an isCached() call on the Connection object.
Release the connection terminateConnection().
conPool->terminateConnection(conn);
|
Note:
|
|
See Also:
|
Each OCCI method is capable of generating an exception if it is not successful. 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.
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.
The following advanced techniques are discussed in this section:
This section covers the following topics:
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.
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.
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.
|
Note:
|
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
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, StatelessConnectionPool 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.
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 multithreaded application, 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:
|
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. To reduce overhead of copying string type data that is available in user buffers, use the setDataBuffer() and next() methods of the ResultSet Class and the executeArrayUpdate() method of the Statement Class.
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.
|
See Also: Table 4-2, "External Datatypes and Corresponding C++ and OCCI Types" in Chapter 4, " Datatypes" for specific cases |
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.
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.
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 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]
|
 Copyright © 1999, 2003 Oracle Corporation All Rights Reserved. |
|