occi
TRANSCRIPT
Oracle C++ Call Interface(OCCI) 1
ORACLE C++
CALL INTERFACE(OCCI)
Shankar Iyer, Oracle India.
Oracle C++ Call Interface(OCCI) 2
OCCI – Today’s Agenda
Session I
- Introduction
- SQL/PLSQL Execution
- Data classes
Session II
- Object Features
- MetaData access
Session III
- Scalability Features- 10i features
Session IV- Interoperability with OCI- OCCI Performance Tuning- Demo application- Q & A
Oracle C++ Call Interface(OCCI) 3
OCCI – Introduction
C++ API to access Oracle database
Designed as small set of well encapsulated classes and interfaces for ease of use
Extensive features for relational access, object-relational access and scalability
Introduced in 9i, growing customer base
Oracle C++ Call Interface(OCCI) 4
OCCI - Benefits
Easy to learn and use, similar to JDBC in relational access
Based on Standard C++ and object oriented design
Higher productivity and quality in application development
Develop client-server, middle-tier and complex object modeling applications
Continuing enhancements by Oracle to add more features
Oracle C++ Call Interface(OCCI) 5
OCCI – Features
Complete SQL/PLSQL execution support
Scalability options to serve increasing number of users and requests
Seamless interface to manipulate objects of user-defined types as C++ class instances
Support for all Oracle data types and large object(LOB) types
Database metadata access
Oracle C++ Call Interface(OCCI) 6
OCCI – Building an application
Application source files
(use OCCI API and classes)
C++ compiler
OCCI API header files
Linker
Application
OCCI library
(static or dynamic)
OTT generated C++ class implementations
OTT generated C++ class headers
OCCI header files
•occi.h
•occiCommon.h
•occiControl.h
•occiData.h
•occiObjects.h
OCCI library
•libocci.so/libocci.a/oraocci9.dll
Oracle C++ Call Interface(OCCI) 7
OCCI – Application Initialization
A OCCI application is initialized by creating an Environment class instance
A Environment instance specifies :-- Application modes : OBJECT/THREADED/MUTEXED etc- object cache settings- memory heap for OCCI classes
The Environment is the base for creating connections for further database access
To create an Environment, call createEnvironment static method of Environment class
Oracle C++ Call Interface(OCCI) 8
OCCI – Initialize Environment – Examples
//include 1 header file for all OCCI classes/interfaces#include <occi.h>
//create EnvironmentEnvironment *env = Environment::createEnvironment();//use the Environment instance to create connections,//database access…
//terminate Environment by calling static method//Environment::terminateEnvironmentEnvironment::terminateEnvironment(env);
Creating default Environment
//create Environment – specify OBJECT modeEnvironment *env = Environment::createEnvironment(Environment::OBJECT);
Creating Environment for object access
Oracle C++ Call Interface(OCCI) 9
OCCI – Control classes
Create
StatelessConnectionStatelessConnectionPoolPool
ConnectionConnectionPool
Create Create
Environment
GetGet
Statement
Create
ResultSet
Execute
MetaData
Get
Get
Oracle C++ Call Interface(OCCI) 10
OCCI – Connecting to database
A user connection is represented by a Connection class instance
Call the createConnection method of Environment class to create a connection
- Connection *Environment::createConnection( const string &userName, const string &password, const string &connectString)
Use the Connection object to access data, execute SQL commands, work with objects
End connection by calling Environment::terminateConnection
Advanced mechanisms like connection pooling, session pooling, proxy authentication also supported
Oracle C++ Call Interface(OCCI) 11
OCCI – Create Connection - Example //First need EnvironmentEnvironment *env = Environment::createEnvironment();
Connection *conn=env->createConnection(“scott”,”tiger”,””);//3rd parameter is db name/TNS alias
..//database access – use the Connection object
..
..//logoff and terminate connectionenv->terminateConnection(conn);
Oracle C++ Call Interface(OCCI) 12
OCCI – Executing SQL/PLSQL
Execute DDL/DML statements, SELECT queries, PL/SQL blocks and retrieve results
Statement class for preparing & executing SQL/PLSQL statements, getting PL/SQL OUT results
ResultSet class for fetching SELECT query results
Uniform interface for binding and getting values of all data types
- setXXX methods of Statement
- getXXX methods of Statement & ResultSet
Data type conversions automatically handled by OCCI
Oracle C++ Call Interface(OCCI) 13
OCCI – Executing SQL – Usage
Create a Statement object with Connection::createStatement()
Specify SQL command(DDL/DML/query) as argument to :- Connection::createStatement(string &sql); Statement::setSQL(string &sql); Statement::execute(string &sql); - can be used for any SQL, returns status Statement::executeUpdate(string &sql); - returns Insert/Update/Delete count Statement::executeQuery(string &sql); - returns ResultSet
Use setXXX methods of Statement to pass input bind values
Execute the SQL statement using one of the execute methods of Statement
For SELECT queries, fetch the results using ResultSet class object
Oracle C++ Call Interface(OCCI) 14
OCCI – Executing SQL – Examples Simple DML Insert//createStatement() on Connection class gives a Statement//instanceStatement *stmt = conn->createStatement(“ insert intoDept(Deptno,Dname, Loc) values (1, ‘ACCOUNTS’, ‘ZONE1’ ”);//executeUpdate for all INSERT/UPDATE/DELETEstmt->executeUpdate();conn->terminateStatement(stmt);
DML Insert with bindStatement *stmt = conn->createStatement(“ insert intoEmp(EmpNo,Ename) values(:1, :2) ”);//1 and 2 are bind placeholdersint empno = 2;string empname = “JOHN W”;//first parameter is bind position, second is valuestmt->setInt(1, empno); stmt->setString(2, empname);stmt->executeUpdate();
Oracle C++ Call Interface(OCCI) 15
OCCI – Executing SELECT – Examples Executing Select queries and fetching resultsStatement *stmt = conn->createStatement(“ select Empno, Ename, Sal from Emp where Hiredate >= :1”);
//automatically converted to Datestmt->setString(1, “01-JAN-1987”);
//executeQuery returns a ResultSetResultSet *rs = stmt->executeQuery();
//ResultSet::next fetches rows and returns FALSE //when no more rowswhile (rs->next() == true){ //get values using the getXXX methods of ResultSet empno = rs->getInt(1); empname = rs->getString(2); empsalary = rs->getFloat(3);}stmt->closeResultSet(rs);//to free resources
Oracle C++ Call Interface(OCCI) 16
OCCI – DML on multiple rows using Iterations
Statement *stmt = conn->createStatement(“insert into emp(empno, ename) values (:1, :2)”);//specify max iterationsstmt->setMaxIterations(10);//number of rows//specify maximum data size for types like stringstmt->setMaxParamSize(2, 100);//set values and add iterationsstmt->setInt(1, 1001);stmt->setString(2, “JOHN”);stmt->addIteration();stmt->setInt(1, 1002);stmt->setString(2, “JOE”);stmt->addIteration();…//repeat iterations,do not call addIteration after last setstmt->executeUpdate();//will insert 10 rows in single trip
DML(INSERT/UPDATE/DELETE) of multiple rows in single roundtrip
Oracle C++ Call Interface(OCCI) 17
OCCI – SQL Execution – Streaming Bind/fetch data in pieces, typically used for LONG columns
Set binary/character streaming mode on Statement/ResultSet and use getStream() to get Stream
Use read/write methods of StreamStatement *stmt = conn->createStatement(“Select LongCol…”);ResultSet *rs = rs->executeQuery();//indicate character streaming moders->setCharacterStreamMode(1, 100000);//col=1,maxsize=100000while (rs->next()){ Stream *col = rs->getStream(1); char buffer[1024]; while (col->readBuffer(buffer, 1024) != -1) //process data}//similary use Stream::writeBuffer(),writeLastBuffer()
Oracle C++ Call Interface(OCCI) 18
OCCI – Executing PL/SQL
Create a Statement object and specify PL/SQL block to be executed
Pass any input arguments(IN and IN/OUT) to the PLSQL function/procedure/block by setXXX methods of Statement
Specify any OUT parameters by Statement::registerOutParam, sizes of OUT parameters by Statement::setMaxParamSize
Execute the PL/SQL block using Statement::execute()
Retrieve function result/OUT/IN OUT parameters by getXXX methods of Statement
Oracle C++ Call Interface(OCCI) 19
OCCI – PLSQL – Examples Calling PL/SQL function/procedure//PLSQL function : function CalculateBonus(EmpNo IN Number,// EmpStatus IN OUT VARCHAR2,// Bonus OUT Number) RETURN VARCHAR2//call function using anonymous blockStatement *stmt = conn->createStatement(“ begin :1 := CalculateBonus( :2, :3, :4); end;”);//bind position 1 is the function’s return valuestmt->setInt(2, 100); //IN parameterstmt->setString(3, “Active”); //IN OUT parameter
//call registerOutParam for each OUT parameterstmt->registerOutParam(1, OCCISTRING, 1000);//function’s return valuestmt->setMaxParamSize(1, 100);//setMaxParamSize for STRING typesstmt->registerOutParam(4, OCCIFLOAT);stmt->execute();//use getXXX methods of Statement to get OUT parameters, return valuestring msg = stmt->getString(1); //function return valuestring newstatus = stmt->getString(3);//IN OUT parameterfloat bonus = stmt->getFloat(4); //OUT parameter
Oracle C++ Call Interface(OCCI) 20
OCCI – SQL/PLSQL – Data buffer interface
To provide and receive data in user buffers for Statement and ResultSet
Bypasses OCCI and C++ specific datatypes like string/Date etc, minimizing data copies
Used in array inserts(any DML) and array fetches
- array DML : Statement::executeArrayUpdate(int nrows)
- array fetch : ResultSet::next(int nrows)
setXXX and getXXX methods should not be used if data buffer interface is used for a column
Oracle C++ Call Interface(OCCI) 21
OCCI – SQL/PLSQL – Array fetch example char enames[10][20];//10 elements of length 20 chars eachub2 elens[10];//to receive the length of each element
Statement *stmt = conn->createStatement("Select Ename from EMP");ResultSet *rs = stmt->executeQuery();
//ResultSet::setDataBuffer(colIndex,buffer,type,elemsize,//lengths,ind,rc)//OCCI_SQLT_STR for char bufferrs->setDataBuffer(1, enames, OCCI_SQLT_STR, 20, elens, NULL,NULL);
rs->next(5); //will fetch 5 rows in enames//do not call rs->getString(1)
Oracle C++ Call Interface(OCCI) 22
OCCI – SQL/PLSQL – Array insert example int empnos[5];
empnos[0] = 801;empnos[1] = 802;
stmt = conn->createStatement("insert into emp (empno) values (:1)");//Statement::setDataBuffer(colIndex,buffer,type,elemsize,//lengths,ind,rc)//OCCIINT for int datatypestmt->setDataBuffer(1, empnos, OCCIINT, sizeof(int), NULL, NULL, NULL);stmt->executeArrayUpdate(2); //inserts 2 rows
conn->commit();
Oracle C++ Call Interface(OCCI) 23
OCCI – Error Handling
OCCI uses C++ exception mechanism to return all errors(in Oracle client/server or C++ STL)
Applications should have a try-catch block to handle exceptions
The exception object thrown is of SQLException class if error is in Oracle
SQLException is derived from standard C++ exception class
getErrorCode and getMessage methods of SQLException return Oracle error information
Oracle C++ Call Interface(OCCI) 24
OCCI – Error Handling – Example Handling Oracle and C++ STL errors separatelytry{ ResultSet *rs = stmt->executeQuery(); while (rs->next()) ……….}catch (SQLException &oraex) //Oracle/OCCI errors{ int errno = oraex->getErrorCode();//returns the ORA number string errmsg = oraex->getMessage(); //more application error handling}catch (exception &ex) //any other C++/STL error{ cout << “Error “ << ex.what() << endl;}
Oracle C++ Call Interface(OCCI) 25
OCCI – Data classes
Classes for using Oracle data types
Oracle data type OCCI class
NUMBER/REAL/INT etc Number
DATE Date
INTERVAL YEAR TO MONTH IntervalYM
INTERVAL DAY TO SECOND IntervalDS
TIMESTAMP WITH TZ/LTZ Timestamp
RAW Bytes
Easy to use with comprehensive functionality
Used with Statement and ResultSet to insert/fetch values of these types
Oracle C++ Call Interface(OCCI) 26
OCCI – Data classes - Number// inserting a numberNumber bignum;bignum.fromText(env, “43613923333.233”, “99999999999999.999”);stmt->setNumber(1, bignum);//stmt is Statement
// Create a Number from a double value double value = 2345.123;Number nu1 (value);
// Some common Number methods Number abs = nu1.abs(); /* absolute value */Number sin = nu1.sin(); /* sine */// Cast operators can be used long lnum = (long) nu1;// Unary increment/decrement prefix/postfix notationnu1++;
Oracle C++ Call Interface(OCCI) 27
OCCI – Data classes – Date
// inserting a Date// Create a Date object and bind it to the statementDate edate(env, 2000, 9, 3, 23, 30, 30);stmt->setDate(1, edate);//stmt is Statement
// fetching and displaying a DateDate odate = rs->getDate(1); //rs is ResultSetCout << odate.toText(“DD-MON-YYYY”, “GERMAN”);
// interval between 2 datesIntervalDS diff;diff = odate.daysBetween(edate);
Oracle C++ Call Interface(OCCI) 28
OCCI – Large Object(LOB) support
Support for BLOB, CLOB/NCLOB and BFILE data types
Simple, consistent, object-oriented interface
OCCI classes :- Bfile, Clob, Blob
Used in relational insert/fetch(column of LOB type) or as object attributes :-
- Statement::setBfile(int column, Bfile &value); - Statement::setClob(int column, Clob &value); - Statement::setBlob(int column, Blob &value); - Bfile Statement/ResultSet::getBfile(int column); - Clob Statement/ResultSet::getClob(int column); - Blob Statement/ResultSet::getBlob(int column);
Streamed read/write support
Oracle C++ Call Interface(OCCI) 29
OCCI – LOB support – Examples Reading a BLOBStatement *stmt = conn->createStatement(“select resume from emp”);ResultSet *rs = stmt->executeQuery();rs->next(); //fetch 1 row
//Blob ResultSet::getBlob(int column)Blob resume = rs->getBlob(1);char buffer[100];int bytesRead, offset = 1;while ((bytesRead = resume.read(100, buffer, 100, offset)) > 0){ //process data read //move offset to read next offset = offset + bytesRead;}
Oracle C++ Call Interface(OCCI) 30
OCCI – LOB support - Examples Inserting/updating a CLOBStatement *stmt = conn->createStatement(“ insert into documents values (:1) ”);//create empty ClobClob doccontents(conn);doccontents.setEmpty();//1st insert will create LOB locator in databasestmt->setClob(1, doccontents);stmt->executeUpdate();conn->commit();
//now select the Clob again and add contentsstmt = conn->createStatement(“select body from documents for update”);ResultSet *rs = stmt->executeQuery()doccontents = rs->getClob(1);
doccontents.write (100, buffer, 100, 1);
conn->commit();
Oracle C++ Call Interface(OCCI) 31
OCCI – Objects
Access data from tables as C++ class instances
Automatic and intuitive mapping of object types to C++ class, no user code
Single data model for application and database
Develop complex and powerful object-oriented applications using Oracle’s object features, OCCI and C++
Client-side cache for database transparency and performance
Oracle C++ Call Interface(OCCI) 32
OCCI – Objects – Oracle basics Create object types to model application entities :-
create type employee as object ( empno number(5), name varchar2(30), hiredate date
);
Object types can be used to create object tables or as column types in relational table :-
create table emptab of employee; -- object tablecreate table person (name varchar2, age number, addr Address); -- column
Terminology :-- Object : a row/item in a object table
- Reference : logical pointer/address of a object.
Every object in a object table has a reference
Oracle C++ Call Interface(OCCI) 33
REF datatype represents a reference to a object in a object table
create type employee as object (… Dept REF Department, -- Reference to another object );
Use REFs to model relationships (one-to-one or one-to-many) - create type LineItem as object (… Item REF Product; -- reference to Product in Products table ) - create LineItems as varray(1000) of REF LineItem; -- collection type create type PurchaseOrder as object (… ItemList LineItems; -- references to all LineItem’s of this order …
In a client application, references are used to fetch objects and navigate object relationships
OCCI – Objects – Oracle basics
Oracle C++ Call Interface(OCCI) 34
OCCI – Objects – Access methods
Navigational access :- - no SQL - Access database objects through references (REFs) - create/modify/delete/fetch ‘persistent’ objects with normal C++ code - complete transaction support - objects are maintained in the client cache
Associative access :- - use SQL - create/fetch object values with Statement::setObject/Statement::getObject and ResultSet::getObject - no caching of objects since they are ‘values’
Objects in a object table are accessed with navigational access. Columns of object types can be accessed with associative access
Oracle C++ Call Interface(OCCI) 35
OCCI – Object Type Translator(OTT)
Generates C++ class representations for Oracle object types
Application uses the C++ classes for creating & accessing persistent and transient objects
Object attributes are declared as member variables, with optional get/set access methods
Simple format input specification file
Generated code to be compiled & linked with the application
Oracle C++ Call Interface(OCCI) 36
OCCI – OTT – Data type mappings
Oracle data type of attribute C++/OCCI type of class member
All numeric types : NUMBER,INT,REAL etc
Number
VARCHAR/CHAR string(C++)
BFILE Bfile
BLOB Blob
CLOB Clob
DATE Date
INTERVAL YEAR TO MONTH IntervalYM
INTERVAL DAY TO SECOND IntervalDS
TIMESTAMP WITH TZ/LTZ Timestamp
NESTED TABLE/VARRAY vector<attribute_type>(C++)
REF Ref<attribute_type>
Embedded object Pointer to C++ class of the embedded object type.
Oracle C++ Call Interface(OCCI) 37
OCCI – OTT – Example generated class//Database type :-create type employee as object( empno number(5), name varchar2(30), hiredate date);
//C++ class generated by OTT :-class EmployeeT : public oracle::occi::PObject {private: oracle::occi::Number EMPNO; string NAME; oracle::occi::Date HIREDATE;public: oracle::occi::Number getEmpno() const; void setEmpno(const oracle::occi::Number &value); string getName() const; void setName(const string &value); oracle::occi::Date getHiredate() const; void setHiredate(const oracle::occi::Date &value);
Oracle C++ Call Interface(OCCI) 38
OCCI – Using OTT#input type specification file (emp.typ)$ cat emp.typTYPE employee As EmployeeT$
#Running OTT$ ott attraccess=private code=cpp cppfile=empo.cpp hfile=emph.h intype=emp.typ mapfile=empm.cpp userid=scott/tiger#generates emph.h, empo.cpp, empm.h, empm.cpp
#OTT command-line options :-attraccess : attributes to be private(with get/set methods) or protectedcode : cpp for OCCIcppfile : name of C++ file for class implementationshfile : name of include file containing class declarationsintype : input type specification filemapfile : name of C++ file containing the mapping register functionuserid : userid & password of the schema owning the object types
Oracle C++ Call Interface(OCCI) 39
OCCI – OTT – Example code fragments1.//Database type :-create type employee as object( Dept REF Department, -- Reference to another object);//C++ class generated by OTT :-class EmployeeT : public oracle::occi::PObject {private: Ref< DepartmentT > DEPT;
2.//Database type :-create type employee as object( Addr Address, -- embedded object);//C++ class generated by OTT :-class EmployeeT : public oracle::occi::PObject {private: AddressT *ADDR;
Oracle C++ Call Interface(OCCI) 40
OCCI – OTT – Example code fragments3.//Database type :-create type PhoneList as varray(10) of varchar2(30);create type customer as object( PhoneNumbers PhoneList, -- attribute of collection type);//C++ class generated by OTT :-class CustomerT : public oracle::occi::PObject {private: vector< string > PHONENUMBERS;
4.//Database type :-create type contractemployee under employee -- inheritance();//C++ class generated by OTT :-class ContractEmployeeT : public EmployeeT { //C++ inheritance
Oracle C++ Call Interface(OCCI) 41
OCCI – Navigational Access
Retrieve objects and navigate relationships using references
A reference is represented in OCCI by Ref<T> class type, where T is the class generated by OTT for the object type
- Ref <AddressT> addrref;
Fetch initial REFs using SQL - Statement *stmt = conn->createStatement(“Select Ref(a) from EmpTab a”); ResultSet *rs = rs->executeQuery(); rs->next(); Ref<EmployeeT> empref = rs->getRef(1);
Access the object (‘pin’) using the C++ dereference operator
(->) on the Ref<T> variable string empname = empref->getName();
//the -> operator returns a object pointer to type T
Oracle C++ Call Interface(OCCI) 42
OCCI – Object cache
Client-side memory and fast lookup for objects
Maintains a fast lookup table between a reference and the corresponding C++ object
When a reference is dereferenced the first time, the cache fetches the object from the server, subsequent accesses get the object from the cache
Objects modified in the cache are updated in the database on transaction commit
When the cache exhausts memory, it frees up unused objects in a LRU style garbage collector
Oracle C++ Call Interface(OCCI) 43
OCCI – Pinning/unpinning of objects
A object is ‘pinned’ in the object cache when a reference (Ref<T>) to the object is dereferenced (-> operator)
The pin count of a object is incremented when additional Ref<T>’s point to the same object
Application can access/modify(get/set attributes) a object after it is dereferenced and pinned
A object is ‘unpinned’ and pin count decremented when the reference to it goes out of scope or points to a different object
When the pin count of a object is zero, it is eligible for garbage collection and will be freed when cache memory is full
Oracle C++ Call Interface(OCCI) 44
OracleDatabase
Client Object Cache
Ref<EmployeeT> empref = rs->getRef(1)
//fetch and pin object
string ename = empref->getName()
1
//pass to another function by value
//pin count increases by 1
PrintEmployee(empref);
21//pin count decreases by 1 on return function
//navigate to address object
Ref <AddressT> addrref = empref->getAddr();
string city = addrref->getCity();
//Modify street in address
addrref->setStreet(“1, PARK AV”);
addrref->markModify();
//process another Employee object
//earlier object is unpinned
empref = rs->getRef(1);
0
OCCI – Object cache – Example
Oracle C++ Call Interface(OCCI) 45
OCCI – Modifying/Deleting objects – Example//fetch initial REF using SQLStatement *stmt = conn->createStatement(“Select Ref(a) FromEmpTab a”);ResultSet *rs = stmt->executeQuery();rs->next(); //fetch the Ref
Ref<EmployeeT> empref = rs->getRef(1);
//to modify a object, change attributes using set methodsempref->setSalary(newsal);//pin and modify object//call markModified() method to indicate to OCCI/cacheempref->markModified();//Modified object will be written to database on commitconn->commit();
//to delete a object, call markDelete on Ref or pinned objectempref.markDelete();//orempref->markDelete();
Oracle C++ Call Interface(OCCI) 46
OCCI – Creating new persistent objects
OTT generates a overloaded new operator in each class :- void *operator new(size_t size, const oracle::occi::Connection * sess, const string& table);
Use the new operator to create persistent objects in a database table :-
EmployeeT *newemp = new (conn, “SCOTT.EMPTAB”) EmployeeT()
Set attribute values of the new object
Object is saved in database when commit is done
New object will be managed by client cache after commit
Oracle C++ Call Interface(OCCI) 47
OCCI – Creating persistent object - ExampleConnection *conn = env->createConnection(“scott”,”tiger”);
//EmployeeT class generated by OTT for type Employee//EMPTAB is object table of type EmployeeEmployeeT *newemp = new (conn, “SCOTT.EMPTAB”) EmployeeT();
newemp->setEmpno(1000);newemp->setEmpname(“JOHN W”);
//use OCCI Date classDate hdate(env, 1, 1, 2003);newemp->setHiredate(hdate);
//object will be saved in database on commitconn->commit();
//to get REF of object, use getRef()Ref <EmployeeT> = newemp->getRef();
Oracle C++ Call Interface(OCCI) 48
OCCI – Objects – Associative Access
Use SQL to create and fetch object values
Can be used with object tables or with object type columns - create type DepartmentT; - create table DeptTab of DepartMentT; -- object table - create table Region (…., Dept DepartmentT, …); -- object type column
Objects are not managed by cache since they are transient
Extensive support for collections
Easy way of using Oracle Object-Relational features
Oracle C++ Call Interface(OCCI) 49
OCCI – Associative Access – Examples
Statement *stmt = conn->createStatement(“ insert into DeptTab values (:1) ”);DepartmentT *dept = new DepartmentT();//create transient instance
//set values into dept membersdept->setDeptNo(1); dept->setDeptName(“HR”);
stmt->setObject(1, dept);stmt->executeUpdate();
Statement *stmt = conn->createStatement(“ insert intoRegion(Area, Dept) values (:1, :2) ”);
stmt->setString(1, “North”);
DepartmentT *dept = new DepartmentT();//create transient instance
//set values into dept members
stmt->setObject(2, dept);stmt->executeUpdate();
Insert into a object table
Insert into a table with object type column
Oracle C++ Call Interface(OCCI) 50
OCCI – Associative Access – Examples
Statement *stmt = conn->createStatement(“select value(a) fromDeptTab a”);//or “select dept from Region”ResultSet *rs = stmt->executeQuery();
rs->next();//fetch
DepartmentT *dept = rs->getObject(1);
//access dept memberscout << dept->getDeptNo() << endl;cout << dept->getDeptName() << endl;
Selecting a object value
Oracle C++ Call Interface(OCCI) 51
OCCI – Collections
Oracle provides VARRAY and Nested Table data types for modeling a set/list of values of a type
OCCI uses the C++ STL vector class for representing varray and nested table of any type :-
- Database type : create type PhoneList as varray(50) of varchar2(100) OCCI representation : vector< string >
- Database type : create type Addresses as table of Address OCCI representation : vector< AddressT * >
vector<T> can be used in relational/associative or navigational access :-
- Relational/Associative access :- Statement IN/OUT binds and ResultSet fetch - Navigational access : Attributes of object types
Oracle C++ Call Interface(OCCI) 52
OCCI – Collections – Relational access
setVector and getVector interfaces on Statement and ResultSet for inserting/fetching data from varray/nested table columns
Comprehensive support for collections of all Oracle data types :- //to insert/pass a collection - void setVector(Statement *stmt, unsigned int column, const vector<T> &vect, const string &sqltype) ;
//to get a PL/SQL OUT param/result of collection type - void getVector( Statement *rs, unsigned int column, vector<T> &vect) ;
//to fetch a collection column - void getVector(ResultSet *rs, unsigned int column, vector<T> &vect) ; ‘T’ can be any type : int/Number/Date/Blob/string/PObject */Ref<T> …
Oracle C++ Call Interface(OCCI) 53
OCCI – Collections – Example
-- create type PhoneList as varray(50) of varchar2(100);-- create table customer(cname varchar2(100), cphones PhoneList);
Statement *stmt = conn->createStatement (“ insert into Customer values (:1, :2) “);
stmt->setString (1, “JOHN W”);
//add 3 phone numbersvector<string> phonenos;phonenos.push_back(“111-222-3333”);phonenos.push_back(“447-555-2323”);phonenos.push_back(“575-232-5885”);
//setVector(Statement *, int col, vector<T> &val, string dbtype)setVector(stmt, 2, phonenos, “SCOTT.PHONELIST”);
stmt->executeUpdate();
Insert into a VARRAY column
Oracle C++ Call Interface(OCCI) 54
OCCI – Collections – Example
-- create type Marks as varray(50) of Number;-- create table student(cname varchar2(100), cphones PhoneList);
Statement *stmt = conn->createStatement (“ select * from customer“);ResultSet *rs = stmt->executeQuery();
while (rs->next()){ cout << “customer name : “ << rs->getString(1); vector<string> phonenos; getVector(rs, 2, phonenos); for (int i = 0; i < phonenos.size(); i++) cout << phonenos[i] << “,”;}
Selecting a VARRAY column
Oracle C++ Call Interface(OCCI) 55
OCCI – Collections – Objects support
VARRAY and Nested table types can be used as attribute types of objects
In the OTT generated C++ class, collection type attributes are declared as vector<T> type members
create type PhoneNumbersType as varray(10) of varchar2(20);
create type Customer as object
(…
PhoneNumbers PhoneNumbersType;
)
OTT class :- class CustomerT : public oracle::occi::PObject { private: vector< string > PHONENUMBERS;
Oracle C++ Call Interface(OCCI) 56
OCCI – MetaData access
Dynamically discover the attributes(e.g name,type,size, count) of database objects(e.g tables,procedures,types) and query results
MetaData class and its getXXX methods provide extensive attribute information of database objects and query result columns
Use Connection::getMetaData() and ResultSet::getColumnListMetaData() to retrieve needed MetaData objects
Oracle C++ Call Interface(OCCI) 57
OCCI – MetaData access – Examples
//Connection::getMetaData(string &object, ParamType ptype)
//ptype = PTYPE_TABLE/PTYPE_VIEW etc or PTYPE_UNK
MetaData md = conn->getMetaData(“EMP”, PTYPE_UNK);
//ATTR_OBJ_PTYPE returns PTYPE_TABLE/PTYPE_VIEW …
int objectType = md.getInt(MetaData::ATTR_OBJ_PTYPE);
int columnCount = md.getInt(MetaData::ATTR_OBJ_NUM_COLS);
Timestamp objts = md.getTimestamp(MetaData::ATTR_TIMESTAMP);
vector<MetaData> cols = md.getVector(MetaData::ATTR_LIST_COLUMS);
//each MetaData in the vector is for 1 column
cout << “column 1 name = “ << cols[0].getString(MetaData::ATTR_NAME);
MetaData of a schema object
Oracle C++ Call Interface(OCCI) 58
OCCI – MetaData Access - Examples
Statement *stmt = conn->createStatement(“select * from emp”);
ResultSet *rs = rs->executeQuery();
//each element in the vector<> is a column
vector<MetaData> selectcols = rs->getColumnListMetaData();
int columnCount = selectcols.size();
for (int i = 0; i < columnCount; i++)
{
cout <<“column name“<<selectcols[i].getString(MetaData::ATTR_NAME);
cout <<“column type”<<selectcols[i].getInt(MetaData::ATTR_DATA_TYPE);
}
MetaData of a ResultSet
Oracle C++ Call Interface(OCCI) 59
OCCI – Scalability features
Connection pooling
Stateless Connection pooling(10i)
Statement caching(10i)
Thread safety
Oracle C++ Call Interface(OCCI) 60
OCCI – Connection pooling
Use a small group of physical connections to support many user sessions
Saves on connection time and server-side processes & resources
Oracle dynamically selects one of the free connections to execute a statement, and then releases the connection to the pool immediately after the execution
Support for proxy connections through the connection pool owner
Suited for middle-tier, multi-threaded applications that need to handle many users
Oracle C++ Call Interface(OCCI) 61
OCCI – Connection pooling – Usage
To create a connection pool :-ConnectionPool* Environment::createConnectionPool(const string &poolUserName, const string &poolPassword,const string &connectString ="",unsigned int minConn=0,unsigned int maxConn=1,unsigned int incrConn=1)
Maximum connections, minimum connections, increment can be reconfigured after the pool has been created
Getting a connection from the pool :- Connection* ConnectionPool::createConnection( const string &userName, const string &password)
Releasing a connection to the pool :- void ConnectionPool::terminateConnection(Connection *conn);
A Connection will be automatically released if it has been idle for the timeout value specified by setTimeOut
Oracle C++ Call Interface(OCCI) 62
OCCI – Connection pooling – Other interfaces
To create proxy connections :- - Connection* ConnectionPool::createProxyConnection(cont string &name, Connection::ProxyType proxyType = Connection::PROXY_DEFAULT); - Connection* ConnectionPool::createProxyConnection(const string &name, string roles[], int numRoles, Connection::ProxyType proxyType = Connection::PROXY_DEFAULT);
Get number of open/busy connections in the pool - int ConnectionPool::getOpenConnections(); - int ConnectionPool::getBusyConnections();
Reconfigure the pool min/max/incr connections - void ConnectionPool::setPoolSize(int minConn, int maxConn, int incr)
Oracle C++ Call Interface(OCCI) 63
OCCI – Connection Pool – Example//Create a connection pool, max connections = 10,//min connections = 4, increment connections = 2ConnectionPool *appPool = env->createConnectionPool(“app1”, ”app1”, “”, 4, 10, 2);//get a connection from the poolConnection *conn = appPool->createConnection(“scott”,”tiger”);
//database access – use the Connection object....//release connection to poolappPool->terminateConnection(conn);
Oracle C++ Call Interface(OCCI) 64
OCCI – Thread Safety
Multiple threads in a application improve performance and response times
Multiple threads can make OCCI calls concurrently
OCCI classes : Environment, Connection, ConnectionPool are thread-safe.
Statement, ResultSet … are not thread-safe, multiple threads should not operate on the same object simultaneously
Either application or OCCI can handle serialization for thread-safe objects, by specifying mode in createEnvironment :-
THREADED_MUTEXED – OCCI manages serialization(using mutexes) THREADED_UNMUTEXED – Application manages serialization
Oracle C++ Call Interface(OCCI) 65
OCCI – New features in 10i
Advanced Queuing
Globalization and Unicode
XA support
Stateless connection pooling
Statement caching
IEEE float and double datatypes
Oracle C++ Call Interface(OCCI) 66
OCCI – Advanced Queuing
Database integrated message queuing for enterprise applications
OCCI provides classes and interfaces for applications to create, send, receive, and access messages
Access queues of Raw, AnyData, Object types
Numerous options for messages, enqueue and dequeue. E.g : priority, recipient list, dequeue mode, listen etc
Integrate messaging, object and relational access in a single application
Oracle C++ Call Interface(OCCI) 67
OCCI – AQ Classes
Queue user Data + options Read Message Send Message Wait for messages
Oracle C++ Call Interface(OCCI) 68
OCCI – AQ – Examples
//instantiate a Message objectMessage msg(conn);//a Agent identifies a Queue user – consumer/producervector<Agent> receiveList;//construct a Bytes objectchar msgstring[] = “ABCDE”;Bytes rawBytes(msgstring, strlen(msgstring);//set Bytes as message payloadmsg.setBytes(rawBytes);
//instantiate a ProducerProducer prod(conn);prod.send(msg, “QUEUE1”);//enqueue the message
//now dequeueConsumer cons(conn);Message msg2 = cons.receive(“QUEUE1”, “RAW”, “SYS”);
Enqueue/Dequeue RAW messages
Oracle C++ Call Interface(OCCI) 69
OCCI – AQ – Examples
//AnyData class is for generic dataAnyData any1(conn);any1.setFromString(“STRING MSG”);//use setFromXXX methodsMessage msg;
msg.setAnyData(any1);//set AnyData as payloadprod.send(msg, “ANYQUEUE”);//enqueueany1.setFromDate(dt);//dt is of type Datemsg.setAnyData(any1);//now set Date as the payloadprod.send(msg, “ANYQUEUE”);//same queue
//dequeueMessage msg2;msg2 = cons.receive(“ANYQUEUE”,”ANYDATA”,”SYS”);//dequeue//get valueAnyData msgany = msg2.getAnyData();string stringmsg = msgany.getAsString();//use other getAsXXX
Enqueue/Dequeue AnyData messages
Oracle C++ Call Interface(OCCI) 70
OCCI – AQ – Examples
//construct transient instance of objectOrderT *neworder = new OrderT();//OrderT is OTT generated class//set attributesneworder->setOrderDate(…);…//set object as Message payloadMessage newmessage(conn);newmessage.setObject(neworder);//enqueueprod.send(newmessage, “ORDERSQUEUE”);
//now dequeueMessage recv = cons.receive(“ORDERSQUEUE”, “ORDERTYPE”, “SCOTT”);//get the object from the messageOrderT *processorder = (OrderT *)recv.getObject();
Enqueue/Dequeue Object Type messages
Oracle C++ Call Interface(OCCI) 71
OCCI – Globalization & Unicode
Supports multibyte charactersets(including UTF8) and Unicode (UTF16) characterset for application development
Applications can specify client characterset and national characterset when the Environment is initialized
Environment *Environment::createEnvironment(string charset, string ncharset)
Client characterset is for all SQL statements, schema elements & data for all CHAR types. National characterset is for data of all NCHAR types
New datatype UString represents Unicode data in UTF16, each character is 2 bytes
Use existing string interfaces for multibyte data
Oracle C++ Call Interface(OCCI) 72
OCCI – Unicode Interfaces
All string interfaces have new equivalent UTF16 interfaces that take/return Ustring. E.g :-
- Statement::setUString(int col, UString &value) - UString ResultSet::getUString(int col) - Environment::createConnection(UString &user, UString &password, UString &dbname) - Statement::execute(UString &sqlquery) - getVector( ResultSet *rs, unsigned int index, vector<UString> &vect)
In Windows platforms, UString is equivalent to Standard C++ wstring datatype
OTT generates CHAR/VARCHAR attributes as UString type member variables for a Unicode application
Oracle C++ Call Interface(OCCI) 73
OCCI – Globalization support – Examples
//OCCIUTF16 is for indicating UTF16 characterset
Environment *env1 = Environment::createEnvironment(“WE8DEC”,”OCCIUTF16”);
Environment *env2 = Environment::createEnvironment(“JA16SJIS”,”JA16SJIS”);
Environment *env3 = Environment::createEnvironment(“ZHT16BIG5”,”UTF8”);
//complete Unicode application :-
Environment *env4 =
Environment::createEnvironment(“OCCIUTF16”,”OCCIUTF16”);
Specifying client application charactersets
//client national characterset is OCCIUTF16
//for column types NCHAR/NVARCHAR, call setDatabaseNCHARParam(col,true)
stmt->setDatabaseNCHARParam(3, true);
//the 2 Katakana Unicode characters for 'Japan' in japanese
unsigned short japanunicodechars[] = {0x65E5,0x672C};
UString uJapan(japanunicodechars, 2);
stmt->setUString(3, uJapan);//binding UTF16 string
Binding UTF16 data(UString)
Oracle C++ Call Interface(OCCI) 74
OCCI – Globalization Support - Examples
//complete Unicode application
Environment *env = Environment::createEnvironment(“OCCIUTF16”,”OCCIUTF16”);
//wstring datatype is same as UString in Windows
wstring wusername = L”scott”;//’L’ creates Unicode string
wstring wpassword = L”tiger”;
Connection *conn = env->createConnection(wusername, wpassword, L””);
//create a Statement with Unicode query
//this will call Connection::createStatement(UString *sql)
Statement *stmt = conn->createStatement(L”Select * From …”);
//bind wstring
wstring name = //from input terminal
stmt->setUString(1, name);
Using wstring in Windows
Oracle C++ Call Interface(OCCI) 75
OCCI – XA Support
Oracle XA conforms to X/Open DTP standard for transaction monitors(TM)
Application uses TM for connecting to multiple, distributed ‘resources’ and transaction control(2-phase commit)
OCCI interfaces for getting the underlying Oracle Environment & Connection in a XA application
The Environment & Connection object can then be used as in a typical application for further database access
Oracle C++ Call Interface(OCCI) 76
OCCI – XA Application Architecture
Uses OCCI
Oracle C++ Call Interface(OCCI) 77
OCCI – XA Support – Interfaces
Get/release Oracle environment - Environment *Environment::getXAEnvironment(const string &dbname)
- void Environment::releaseXAEnvironment(Environment *env)
Get/release Oracle connection - Connection *Environment::getXAConnection(const string &dbname)
- void Environment::releaseXAConnection(const string &dbname)
Check if a error(exception) is in XA or Oracle - int SQLException::getXAErrorCode(const string &dbname)
dbname is the xa_open string specified by the application to the TM to connect to Oracle
Oracle C++ Call Interface(OCCI) 78
OCCI – XA Support – Example //define the db open string according to Oracle XA specificationstring xaopen_str = "oracle_xa+ACC=P/SCOTT/TIGER+SESTM=50+logdir=.+SqlNet=inst1";
//connect to database and other ‘resources’ using TM interfacestx_open();//to manipulate data need access to ConnectionEnvironment *xaenv = Environment::getXAEnvironment(xaopen_str);Connection *xaconn = xaenv->getXAConnection(xaopen_str);//access database – use SQL etctx_begin();//TM callStatement *stmt = xaconn->createStatement(“Update Emp..”);stmt->executeUpdate();
//for transaction control use TM calls, not Oracle callstx_commit();
//release Connection & Environmentxaenv->releaseXAConnection(xaconn);Environment::releaseXAEnvironment(xaenv);
Oracle C++ Call Interface(OCCI) 79
OCCI – Stateless Connection Pooling
Maintain a pool of open, authenticated user connections
No roundtrip for authentication, connection is ready to be used when got from pool
Connections are ‘stateless’, applications should not leave open transactions
Connections can be ‘tagged’ with a descriptive name and then retrieved using tag
Support for proxy connections through the connection pool owner
Oracle C++ Call Interface(OCCI) 80
OCCI – Stateless Connection Pooling – II
Suited for middle-tier, multi-threaded applications that do short database access
Stateless Connection pool can be HOM0GENOUS – All connections with same user
HETEROGENOUS – Connections can have different user/proxy
OCCI will not do commit/rollback, user’s responsibility to not leave any ‘state’ when connection is released
Interfaces similar to ConnectionPool
Oracle C++ Call Interface(OCCI) 81
OCCI – Stateless Connection Pool – Examples
StatlessConnectionPool *spool =Environment::createStatelessConnectionPool(“scott”, “tiger”, “”,10, 4, 2, HOMOGENOUS);//MaxConn=10, MinConn=4, IncrConn=2
//get a connection from the poolConnection *conn = spool->getConnection();//use Connection as earlierStatement *stmt = conn->createStatement(“alter session setNls_Language=‘French’”);…//release Connection with tagspool->releaseConnection(conn, “FR”);
//get Connection with tagConnection *conn2 = spool->getConnection(“FR”);
//destroy poolenv->terminateConnectionPool(spool);
Homogenous ConnectionPool
Oracle C++ Call Interface(OCCI) 82
OCCI – Stateless Connection Pool - Examples
StatlessConnectionPool *spool =Environment::createStatelessConnectionPool(“scott”, “tiger”, “”,10, 4, 2, HETEROGENOUS);//MaxConn=10, MinConn=4, IncrConn=2
//get a connection from the pool with a different userConnection *conn = spool->getConnection(“acme”, “acme”);//use Connection as earlierStatement *stmt = conn->createStatement(“Select * From Emp”);…//release Connection without tagspool->releaseConnection(conn);
//get Connection againConnection *conn2 = spool->getConnection(“acme”, “acme”);
//get Proxy ConnectionConnection *conn3 = spool->getProxyConnection(“proxyuser1”);//destroy poolenv->terminateConnectionPool(spool);
Heterogenous ConnectionPool
Oracle C++ Call Interface(OCCI) 83
OCCI – Statement Caching
Cache and reuse frequently executed SQL statements
In the server, cursors are ready to be used without the need to parse the statement again
Client-side resources and data structures are effectively reused
Statements can be ‘tagged’ with a descriptive name and then retrieved from the cache by tag
Works with connection pooling and stateless connection pooling
Oracle C++ Call Interface(OCCI) 84
OCCI – Statement Caching – Usage
Enable statement caching on a Connection :- - Connection::setStmtCacheSize(unsigned int stmtCacheSize)
On createStatement, the cache will be searched for statement. If not found, then a new statement is created
On terminateStatement(with optional tag), the statement will be added to cache
On next createStatement, the statement will be got from the cache
Oracle C++ Call Interface(OCCI) 85
OCCI – Statement Caching – Example //enable statement cachingconn->setStmtCacheSize(10);//get cache sizeint csize = conn->getStmtCacheSize();
//create a statementStatement *stmt = conn->createStatement(sqlquery);//release the statement to cache with tagconn->terminateStatement(stmt, “tagA”);
//retrieve statement again with SQL or tagstmt = conn->createStatement(sqlquery);//Orstmt = conn->createStatement(“”,”tagA”);
Oracle C++ Call Interface(OCCI) 86
OCCI – Interoperability with OCI
OCCI is designed and implemented on top of OCI
Underlying OCI ‘handles’ of database access classes can be retrieved :-
- OCIEnv *Environment::getOCIEnv() - OCIServer *Connection::getOCIServer() - OCISvcCtx *Connection::getOCIServiceContext() - OCISession *Connection::getOCISession() - OCIStmt *Statement::getOCIStatement()
Handles can be used when migrating and reusing existing OCI code
OCI ‘C’ code for relational access can be mixed in a OCCI application
OCI objects interfaces cannot be used in OCCI application
Oracle C++ Call Interface(OCCI) 87
OCCI – OCI Interoperability – Examples //get OCI Environment & Service ContextOCIEnv *ocienv = env->getOCIEnv();//env is OCCI EnvironmentOCISvcCtx *ocisvc = conn->getOCISvcCtx();//conn is Connection
//use OCI callsOCIHandleAlloc(ocienv, &dpctx, OCI_HTYPE_DIRPATH_CTX,…);//do more OCI stuffDoDirectPathLoad(ocienv, ocisvc, dpctx, …);
//reuse of existing codeStatement *stmt = conn->createStatement(“Select * From Emp”);ResultSet *rs = stmt->executeQuery();
//display MetaData using existing C functionOCIStmt *stmthp = stmt->getOCIStmt();DisplayMetaDataInGUI(stmthp);
//do not use OCIObject* calls
Oracle C++ Call Interface(OCCI) 88
OCCI – Performance Tuning
Relational access - Use iterative DML execution to reduce roundtrips
Scalability
- Use Stateless Connection pooling to get authenticated connections
- Use Connection pooling to use less number of physical connections
Objects access & navigation
- Complex Object Retrieval for prefetching related objects
- Tune the object cache
Oracle C++ Call Interface(OCCI) 89
OCCI – Complex Object Retrieval(COR)
Object oriented applications model their data as a set of interrelated objects to form graphs (using REFs)
Performance of such models can be increased using COR to prefetch a set of linked objects in a single network trip
Prefetch attributes(type and depth) are specified on the ‘root’ Ref<T>. When the root is pinned, linked objects are also fetched
- Ref<T>::setPrefetch(const string &typeName, unsigned int depth); - Ref<T>::setPrefetch(unsigned int depth);
PurchaseOrder
Cust REF Customer;RelatedOrder REF PurchaseOrder;LineItems LineItemRefArray
Customer PurchaseOrder LineItem 1 LineItem 2
Oracle C++ Call Interface(OCCI) 90
OCCI – COR – Example //OTT class :-//class PurchaseOrder : {// Date OrderDate;// Ref <Customer> Customer;// Ref <PurchaseOrder> RelatedOrder;// vector < Ref<LineItem> > LineItems; }
Ref<PurchaseOrder> poref;//set COR prefetching to get Customer object alsoporef.setPrefetch(“Customer”, 1);
Date odate = poref->getOrderDate();//this will fetch & pin PO//the linked Customer object will also now be in the cache
Ref<Customer> cust = poref->getCustomer();string cname = cust->getName();//no round-trip
//to get all linked objects at depth 1 – customer, related//purchase order, line items :-poref.setPrefetch(1);
Oracle C++ Call Interface(OCCI) 91
OCCI – Tuning object cache
The cache is configured by optimal size and maximum size(%) parameters :-
- Maximum cache size = optimal size + (optimal size * maximum size/100)
- Environment::setCacheOptSize(), Environment::setCacheMaxSize()
For C++ datatype attributes(string, vector<T>), the memory comes from C++ heap
Size the cache according to application needs
Consider periodic flushes of objects instead of sending all objects at commit
- ref->flush()
Oracle C++ Call Interface(OCCI) 92
OCCI – In closing…
Natural interface to Oracle object features
C++ and object oriented paradigm for better productivity and quality
Simple and easy to use for relational access
References :- Oracle C++ Call Interface Programmer’s Guide
Oracle Application Developer’s Guide – Object-Relational Features
OTN Discussion forums – otn.oracle.com
Oracle C++ Call Interface(OCCI) 93
Demo
and
Q & A