|
following are some points worth remembering for occi.
for every kind of create there is a terminate. env = Environment::createEnvironment(Environment::DEFAULT); connPool = env->createConnectionPool("scott","tiger","adityadb", 3, 9, 2);nor conn = connPool->createConnection("scott","tiger"); stmt = conn->createStatement("drop table testTable"); conn->terminateStatement(stmt); env->terminateConnection(conn); Environment::terminateEnvironment(env); stmt->executeUpdate(); // the return value is scalar. stmt->executeQuery(); // the return is the resultset of a query stmt->setInt(1, ssn); // this is the way we set the values of the column to be passed to a prepared statement stmt->setString(2, name); ResultSet *rset = stmt->executeQuery(); while(rset->next()) rset->getInt(1), rset->getString(2) // this is the way to access the data returned in the form of result sets stmt->closeResultSet(rset); // note that the resultsets are closed ( and not terminated ). prepared statement vs normal query bind is helpful for scalability as oracle can find the statement in the shared pool. if it finds a statement in the shared pool then it retrieves and it is executed. thus oracle saves in parsing and finding the execution path (saves a hard parse which involves obtaining latches on key shared memory areas which can pose a bottleneck even if they are more number of CPU's or memory they are not going to salvage this situation. ) bind values cannot be used for table names and are meant only for literals e.g. INSERT INTO dept_tab VALUES(:1,:2,:3) Either this statement can be executed several times with different bind values, or an array insert can be performed to insert several rows in one round-trip to the server. DML statements also enables us to work with objects in the Oracle database. consider the statement
Statement *stmt = conn->createStatement(); it can be reusd as stmt->setSQL("INSERT INTO basket_tab VALUES(:1,:2)"); stmt->setSQL("SELECT * FROM basket_tab WHERE quantity >= :1"); there is no need to allocate and free the statement object and their associated resources again and again. we can retrieve the contents of the current statement object at any time by means of the getSQL() method. and then we can call execute() to execute all nonspecific statement types
or
executeUpdate() to execute DML and DDL statements.
it returns the number of rows affected by the SQL statement. it can also be used for create as well as insert update etc. as in the above example. or executeArrayUpdate(): to execute multiple DML statements or executeQuery(): to execute a query which returns a ResultSet used in select etc. //basically a sql query Parameterized Statements using bind variables. If our 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. stmt->setSQL("BEGIN countFruit(:1, :2); END:"); stmt->setString(1, "Apples"); // specify the first (IN) parameter of procedure int quantity; stmt->registerOutParam ( 2, Type::OCCIINT, sizeof(quantity)); // However, before calling a stored procedure, we need to specify the type and size of // any OUT parameters by calling the registerOutParam() method. For IN/OUT //Types of SQL Statements in the OCCI Environment // parameters, use the setXXX() methods to pass in the parameter, and getXXX() // methods to retrieve the results. // specify the type and size of the second (OUT) parameter stmt->executeUpdate(); // call the procedure quantity = stmt->getInt(2); // Finally, we obtain the output parameters by calling the relevant getxxx() method: // 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 );
Streaming interface: 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. getStream() method of the Statement Class returns a stream object that supports reading and writing for DML and callable statements: 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); // setBinaryStreamMode() 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
//use Stream::readBuffer/readLastBuffer to read data
For iterative execution (That means sending a number of queries in one shot thus saving roundtrip), first specify the maximum number of iterations that would be done for the statement by calling the setMaxIterations() method: Statement->setMaxIterations(int maxIterations); we can retrieve the current maximum iterations setting by calling the getMaxIterations() method. Setting the Maximum Parameter Size If the iterative execution involves variable length datatypes, such as string and Bytes, then we must set the maximum parameter size so that OCCI can allocate the maximum size buffer: 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 As shown in the example, we call the addIteration() method after each iteration except the last, after which we invoke executeUpdate() method. Of course, if we do not have a second row to insert, then we need not 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 we use setInt() for parameter 1, then we cannot use setString() for the same parameter in a later iteration. 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. 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, we can set the number of rows to prefetch in each round-trip to the server. we can get this effect 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.
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. When we know that we need to execute a DML operation, we use the executeUpdate method. Similarly, when we know that we need to execute a query, we use executeQuery() method. If our application needs to allow for dynamic events and we 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 status: 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 stmt->setSQL("select * from emp"); status = stmt->status(); // status is PREPARED Statement stmt = conn->createStatement("insert into foo(id) values(99)"); Statement::Status status = stmt->status(); // status is PREPARED status = stmt->execute(); stmt->setSQL("select * from emp"); // status is PREPARED status = stmt->execute(); // status is RESULT_SET_AVAILABLE 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(); 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, we cannot infer the kind of statement that has been executed from the reported status. NEEDS_STREAM_DATA If there are any output streams to be written, the execute does not complete until all the stream data is completely provided. In this case, the status changes to NEEDS_STREAM_DATA to indicate that a stream must be written. After writing the stream, call the status() method to find out if more stream data should be written, or whether the execution has completed. In cases where our statement includes multiple streamed parameters, use the getCurrentStreamParam() method to discover which parameter needs to be written. If we 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. 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, we can also call the Connection::commit() and Connection::rollback() methods. If we want the DML changes that were made to be committed immediately, then we 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); SET AUTO[COMMIT] { OFF | ON | IMM[EDIATE] | n } 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. 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. conn->setStmtCacheSize(10); Statement *stmt = conn->createStatement(sql); Return the statement to cache. Statement *stmt = conn->createStatement(sql, tag); conn->terminateStatement(stmt, tag); If we don't want to cache this statement, we use the disableCaching() call and an alternate from of terminateStatement(): stmt->disableCaching(); conn->terminateStatement(stmt); If we need to verify whether a statement has been cached, we issue an isCached() call on the Connection object. Enable statement caching for all Connections in the ConnectionPool by using a nonzero size parameter in the setStmtCacheSize() call. conPool->setStmtCacheSize(10); 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); This code example demonstrates how a standalone object is created: CREATE TYPE person_t AS OBJECT (name varchar2(30), age number(3)); CREATE TABLE person_tab OF person_t; Objects that are stored in the object table This code example demonstrates how an embedded object is created: CREATE TABLE department (deptno number, deptname varchar2(30), manager person_t); Objects which are stored in the manager column of the department table are embedded objects. They do not have object identifiers, and they cannot be referenced. This means they cannot be pinned in an OCCI application, and they also never need to be unpinned. They are always retrieved into the object cache by value. The Array Pin feature allows a vector of references to be dereferenced in one round-trip to return a vector of the corresponding objects. A new global method, pinVectorOfRefs(), takes a vector of Refs and populates a vector of PObjects in a single round-trip, saving the cost of pinning n-1 references in n-1 round-trips. CREATE TYPE ADDRESS AS OBJECT ( state CHAR(2), zip_code CHAR(5)); CREATE TABLE ADDR_TAB of ADDRESS; ADDRESS *addr = new(conn, "ADDR_TAB") ADDRESS("CA", "94065"); The persistent object is created in the database only when one of the following occurs: The transaction is committed using (Connection::commit()) The object cache is flushed (Connection::flushCache()) The object itself is flushed (PObject::flush()) ADDRESS *addr_trans = new ADDRESS("MD", "94111"); BFILE 4 gigabytes BINARY_DOUBLE 8 bytes BINARY_FLOAT 4 bytes CHAR 2,000 bytes DATE 7 bytes INTERVAL DAY TO SECOND REF 11 bytes INTERVAL YEAR TO MONTH REF 5 bytes LONG 2 gigabytes (2^31-1 bytes) LONG RAW 2 gigabytes (2^31-1 bytes) NCHAR 2,000 bytes NUMBER 21 bytes NVARCHAR2 4,000 bytes RAW 2,000 bytes REF BLOB 4 gigabytes CLOB 4 gigabytes NCLOB 4 gigabytes ROWID 10 bytes TIMESTAMP 11 bytes TIMESTAMP WITH LOCAL TIME ZONE 7 bytes TIMESTAMP WITH TIME ZONE 13 bytes UROWID 4000 bytes User-defined type (object type, VARRAY, nested table) VARCHAR2 4,000 bytes we can use five main Oracle internal datatypes to specify columns that contain either characters or arrays of bytes: CHAR, VARCHAR2, RAW, LONG, and LONG RAW. MetaData emptab_metaData = connection->getMetaData( "EMPLOYEES", MetaData::PTYPE_TABLE); vector<MetaData>listOfColumns; listOfColumns=emptab_metaData.getVector(MetaData::ATTR_LIST_COLUMNS); /* Each of the list elements represents a column metadata, so now you can access the column attributes*/ for (int i=0;i<listOfColumns.size();i++ ) { MetaData columnObj=listOfColumns[i]; cout<<"Column Name:"<<(columnObj.getString(MetaData::ATTR_NAME))<<endl; cout<<"Data Type:"<<(columnObj.getInt(MetaData::ATTR_DATA_TYPE))<<endl; ....... /* and so on to obtain metadata on other column specific attributes */ } 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. 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] |
|
|
