Oracle C++ Call Interface

Geoff Lee Sr. Product Manager Oracle Corporation

Oracle C++ Call Interface A New API for HighPerformance Internet Applications

Agenda      

Survey Customer Requirements Meeting the Needs Realizing the Benefits Summary Q&A

Agenda      

Survey Customer Requirements Meeting the Needs Realizing the Benefits Summary Q&A

Customer Requirements  Comprehensive Support –

Oracle Relational and Object-Relational access

 High performance and Scalable API –

Reduce round-trip access, minimize data copying, caching, connection pooling, etc.

 High Productivity – –

Simple: hiding complexity Easy: familiar constructs

Agenda      

Survey Customer Requirements Meeting the Needs Realizing the Benefits Summary Q&A

Meeting the Needs  Comprehensive Support (OCI) –

Oracle Relational and Object-Relational access

 High performance and Scalable API (OCI) –

Reduce round-trip access, minimize data copying, cacheing, connection pooling, etc.

 High Productivity (C++/Java) – –

Simple: hiding complexity Easy: familiar constructs

Oracle C++ Call Interface  Built on top of OCI –

Comprehensive Support  Oracle Relational and Object-Relational access

–

High performance and scalable API  Reduce round-trip access, minimize data copying, cacheing, connection pooling, etc.

 C++ API modeled after JDBC –

High Productivity (C++)  Simple: encapsulation  Easy: JDBC-like constructs

OCCI Functionalities  Associative Relational Access – –

Basics Advanced Techniques

 Objects Access – –

Navigational Associative

 Metadata Access

Agenda  Survey  Customer Requirements  Meeting the Needs – – –

Associative Relational Access Objects Access Metadata Access

 Realizing the Benefits  Summary Q&A

Using OCCI  setup and connection  executing SQL statements  using objects – –

Navigational Interface Associative (SQL) Interface

OCCI Factory Hierarchy Env Create Create ConnPool

Conn Create getMD

Create Stmt

getMD

getResultSet ResultSet

MetaData

Environment Class  Allows multiple class instances  Manages memory and other resources for OCCI objects  An Environment instance maps to an OCI environment handle

Create/Terminate Environment Create an instance by calling the static method Environment::createEnvironment(Mode mode=DEFAULT, ...) – –

DEFAULT mode is neither thread-safe nor object Other parameters allow custom memory management functions

 Free an instance and its resources with the static Environment::terminateEnvironment (Environment *env)

Environment *env = Environment::createEnvironment(); ... Environment::terminateEnvironment(env);

Open/Close Connection  Environment is the factory class for creating Connection instances Connection *conn; Environment *env = Environment::createEnvironment(); conn = env->createConnection(“scott”, “tiger”); … env->terminateConnection(conn); Environment::terminateEnvironment(env);

Executing SQL DML Statement public int executeUpdate(const string &sql="");  Executes a non-query statement – – –

INSERT, UPDATE, DELETE, CREATE/ALTER, etc.), or a stored procedure Returns the row count for INSERT, UPDATE, DELETE Returns 0 for statements that return nothing

executeUpdate() Example  Creating a Table Statement *stmt = conn->createStatement(); stmt->executeUpdate(“ CREATE TABLE basket ( fruit VARCHAR2(20), quantity NUMBER)”);

 SQL DML statement stmt->executeUpdate( “ INSERT INTO basket values (‘MANGO’, 3)”);

Executing Prepared Statement

 SQL DML statement with IN bind variables stmt->setSQL(“ INSERT INTO basket VALUES(:1, :2)”); stmt->setString(1, “BANANA”); // value for first param stmt->setInt(2, 5); // value for second param stmt->executeUpdate();

Executing Callable Statements

 Calling procedures stored on the database server int quantity; stmt->setSQL("BEGIN countFruit(:1, :2); END:"); // specify the first (IN) parameter of procedure stmt->setString(1, "Apples"); // specify the type and size of the second (OUT) parameter stmt->registerOutParam(2, Type::OCCIINT, sizeof(quantity)); stmt->executeUpdate(); // get the value of the second (OUT) parameter quantity = stmt->getInt(2);

Streamed Reads and Writes

 The OCCI supports streamed parameters for prepared and callable statements – – – –

Read/write binary or character data Writeable stream corresponds to IN bind variable Readable stream corresponds to OUT bind variable Bi-directional stream corresponds to IN/OUT bind variable

An Example char buffer[4096]; ResultSet *rs = stmt->executeQuery ("SELECT blobCol FROM tab1 WHERE col1 = 11"); ResultSet *rs = stmt->getResultSet (); while (rs->next ()) { unsigned int length = 0; unsigned int size = 500; Stream *stream = rs->getCharacterStream (2, 4000); while (stream->status () == Stream::READY_FOR_READ) { length += stream->readBuffer (buffer +length, size); } cout << "Read " << length << " bytes into the buffer" << endl; }

Executing SQL Queries public ResultSet * executeQuery(const string &sql=" ")

 Execute a SQL Select statement – –

Should not be called for a non-query statement Does not allow streamed parameters

 Returns an ResultSet that contains the data produced by the query

Executing SQL Queries ResultSet *rs = stmt->executeQuery( “SELECT quantity FROM basket WHERE fruit = ‘ORANGE’ “); rs->next();

// fetch the next row

int quantity = rs->getInt(1); // get first col as int

Executing SQL Queries rs =stmt->executeQuery(“SELECT * FROM basket”); cout << “The basket has :” << endl; while (rs->next()) { string fruit = rs->getString(1); int quantity = rs->getInt(2); cout << quantity << “ “ << fruit << endl; }

Prefetching Result Set  Optimizing performance by setting prefetch size  Use a method of Statement class to set prefetch size for each round trip – –

void setPrefetchRowCount (int rowCount); void setPrefetchMemorySize (int bytes);

stmt-> setPrefetchRowCount (10);

Executing Statements Dynamically Status = Statement->execute(const string &sql="");  Executes statements that are either queries or updates  Returns one of the following status values: – – – – – –

UNPREPARED PREPARED RESULT_SET_AVAILABLE UPDATE_COUNT_AVAILABLE NEEDS_STREAM_DATA STREAM_DATA_AVAILABLE

Committing a Transaction  All SQL DML statements are executed in the context of a transaction – – – –

Changes become permanent by committing the transaction Undo changes by performing a rollback SQL COMMIT and ROLLBACK can be executed with executeUpdate method Connection::commit() and Connection::rollback() methods can also be used

Connection Pooling  Problem – – –

Mid-tier mult-threaded applications need multiple connections to the database Opening and closing connection each time is very expensive Opening all the necessary connections before hand is wasteful

 Solution – – –

Maintain a pool of connections Allocate/release a connection for execution Connection Pool is dynamically configurable

Connection Pooling ConnectionPool* createConnectionPool( const string &poolUserName, const string &poolPassword, const string &connectString ="", unsigned int minConn =0, unsigned int maxConn =1, unsigned int incrConn =1); ConnectionPool* connPool = env->createConnectionPool (“scott”, “tiger”, 1, 5, 1); void setPoolSize(unsigned int minConn =1, unsigned int maxConn =1, unsigned int incrConn =1); connPool->setPoolSize(5, 20, 1);

Connection Poolingection Pooling t1 t2 t3 t4 t5

s1 s2 s3 s4 s5 s6

Pool 1

c1

db1

c2

c3 Pool 2

db2

c4

t6 Application Layer

OCI Layer

Databases

Data Buffer and Array Execute  Performance optimization techniques  Statement class provides setDataBuffer method to minimize data copying cost  executeArrayUpdate method allows iterative execution to save network roundtrip

An Example 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); // inserting data for two rows

Data Buffer and Array Fetch 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); // fetches five rows, enameLen[i] has length of ename[i] rs->next(5);

Multi-Threaded Environment

 Threads are lightweight processes that exist within a larger process –

– –

Share the same code and data segments but have their own program counters, machine registers, and stack Global and static variables are shared Mutex (mutually exclusive lock) manages access to shared resources

OCCI Thread Safety  An application runs on a thread-safe platform  Sets the mode parameter of the creatEnvironment method –

THREADED_MUTEXED  OCCI manages mutexing

–

THREADED_UNMUTEXED  Application maintain its own mutexing scheme

Environment *env = Environment::createEnvironment (Environment::THREADED_MUTEXED);

Agenda  Survey  Customer Requirements  Meeting the Needs – – –

Associative Relational Access Objects Access Metadata Access

 Realizing the Benefits  Summary Q&A

OCCI Object Features  Access database objects as C++ instances  Oracle Type Translator (OTT 9i) –

map object types to C++ classes

 client-side caching of C++ objects  support all object OCI features

Accessing Objects  Navigational Interface – – –

no SQL navigate using the object references (REFs) create/access/lock/delete/flush

 Associative Interface – –

use SQL or PL/SQL no cost of transporting data to client

Navigational Access  The OCCI navigational interface: –

– –

Allows access and modification of object-relational data as C++ objects without using SQL Provides a mechanism to create, access, lock, delete and flush objects Accesses object references and navigate through them

Representing Objects CREATE TYPE ADDRESS AS OBJECT ( state CHAR(2), zip CHAR(2)); class ADDRESS : public PObject { protected : string state; string zip; public : void *operator new(size_t size); // create a transient instance void *operator new(size_t size, Connection *conn, string& table); // create a persistent instance }

Creating a Persistent Object

CREATE TABLE ADDRESS_TAB OF ADDRESS; ADDRESS *addr; addr = new (conn, “ADDRESS_TAB”) ADDRESS(“CA”, “94002”);

Fetching an Object Reference

 Object Reference – logical pointer to an object instance – Ref ResultSet *rs = stmt->executeQuery( “SELECT REF(address) FROM ADDRESS_TAB address where zip = ‘94065’ “); rs->next(); Ref

addr_ref = rs->getRef(1);

Pinning an Object string ADDRESS::getState() { return state; } string state = addr_ref->getState(); Ref::operator -> – overloaded – pins the object instance during the first time

Client-side Object Cache Client side cache REF

Server buffer cache flush load

Pin

Network

Modifying an Object void ADDRESS::setState(string new_state) { state = new_state; } addr_ref->setState(“PA”); addr_ref->mark_modified(); dirty

// mark object as

Flushing Changes to Server  PObject::flush

flush the dirty object Example: addr->flush(); –

 Connection::commit – –

commit the transaction flush all the dirty objects

Complex Object Retrieval (COR) Object-oriented application has a set of graph- or tree-structured objects  COR prefetches multiple objects in one network round trip to improve performance – –

–

The desired root object is explicitly fetched The depth level is the minimum number of references to be traversed from the root to an object Constrained by available memory in the object cache

Invoking COR  COR is invoked by setting attributes of Ref before de-referencing –

prefetch attributes of the specified type name up to the the specified depth Ref::setPrefetch(const string &typeName, unsigned int depth);

–

prefetch all the attribute types up to the specified depth Ref::setPrefetch(unsigned int depth);

Type Inheritance  Oracle9i provides SQL-99 standard support of type inheritance – –

Polymorphic method dispatch Polymorphic object substitutibility

OTT for Type Inheritance  Generates classes from inheritance hierarchy class : public <parentTypename> { protected: ; ... public: void *operator new(size_t size); // transient object void *operator new(size_t size, const Session* sess, const string& table); string getSQLTypeName(size_t size); // persistent object ... virtual void readSQL(AnyData& stream); virtual void writeSQL(AnyData& stream); }

Collections  Collection represented as Vector CREATE TYPE ADDR_LIST AS VARRAY(5) OF REF ADDRESS; CREATE TYPE PERSON AS OBJECT(name VARCHAR2(20), addr_list ADDR_LIST); class PERSON : public PObject { protected : string name; vector > addr_list; … }

Associative Object Access ResultSet *rs = stmt->executeQuery( “SELECT VALUE(address) from ADDRESS_TAB address”); while (rs->next()) { ADDRESS *addr_val = rs->getObject(1); cout << “state : “ << addr_val->getState(); }

Insert a new object using SQL // create a transient instance ADDRESS *addr_val = new ADDRESS(“NV”, “23567”); stmt->setSQL(“INSERT INTO ADDRESS_TAB values(:1)”); stmt->setObject(1, addr_val); stmt->execute();

Example :- (OTT code) class PERSON : public PObject { protected : string name; vector > addr_list; … } void RegisterMappings(Environment *env) { Map *map = env->getMap(); map->put(“SCOTT.ADDRESS”, ADDRESS::readSQL, ADDRESS::writeSQL); map->put(“SCOTT.PERSON”, PERSON::readSQL, PERSON::writeSQL);

}

Example :- (user class def. )

// ADDRESS, PERSON : OTT generated classes class MyAddress : public ADDRESS { public: MyAddress(string state_i, string zip_i); void displayInfo(); } class MyPerson : public PERSON { public: MyPerson(string name, Ref<MyAddress>& addr); void move(const Ref<MyAddress>& new_addr); void displayInfo(); }

Example (user class methods)

void MyAddress::displayInfo() { cout << "STATE is" << state << endl; cout << "ZIP is" << zip << endl; } void MyPerson::displayInfo() { cout << "NAME is" << name << endl; cout << "Address List: " << endl; for (int i = 0; i < addr_list.size(); i++) // operator [] on vector, operator -> on vector element, REF addr_list[i]->displayInfo(); }

Example (contd.) void MyPerson::move(const Ref<MyAddress>& new_addr) { addr_list.push_back(new_addr); this->mark_modified(); // mark the object as dirty }

Example (contd.) int main() { Environment *env = Environment::createEnvironment(); Connection *conn = env->createConnection("scott", "tiger"); /* Call the OTT generated function to register the mappings */ RegisterMappings(env); /* create a persistent object of type ADDRESS */ MyAddress *addr = new(conn, "ADDR_TAB") MyAddress("CA", "94065");

Example (contd.) /* create a persistent object of type Person */ MyPerson *person = new(conn, "PERSON_TAB") MyPerson(“Joe”, addr->getRef()); conn->commit(); // flush newly created objects to server Statement *stmt = conn->createStatement(); ResultSet *rs = stmt->executeQuery("SELECT REF(Person) from person_tab where name = ‘Bill’ "); Ref<MyPerson> bill_ref = (Ref<MyPerson>) rs.getRef(1);

Example (contd.) // create a new address object MyAddress *new_addr = new(conn, "ADDR_TAB") MyAddress("PA", "92140"); // note : object is marked dirty in move() bill_ref->move(new_addr->getRef()); // display the contents of the PERSON object bill_ref->displayInfo(); conn->commit(); // flush the dirty object

Example

(contd.)

// delete the objects from the object cache delete addr; delete person; delete new_addr; conn->closeStatement(stmt); env->terminateConnection(conn); Environment::terminateEnvironment(env); return 0; }

Agenda  Survey  Customer Requirements  Meeting the Needs – – –

Associative Relational Access Objects Access Metadata Access

 Realizing the Benefits  Summary Q&A

Metadata MetaData type_md; // describe a user defined type type_md = conn->getMetaData(“ADDRESS”); // get number of type attributes unsigned int num_attrs = type_md.getInt(ATTR_NUM_TYPE_ATTRS);

Metadata

(contd.)

// describe each attribute of the type vector<MetaData> attr_list = type_md.getVector(ATTR_LIST_TYPE_ATTRS); for (int I = 0; I < attr_list.length(); I++) { MetaData attr_md = attr_list[I]; cout << “Attribute “ << I+1 << “ is “ << attr_md.getString(ATTR_TYPE_NAME) << endl; }

Agenda      

Survey Customer Requirements Meeting the Needs Realizing the Benefits Summary Q&A

Benefits of OCCI  Simplified application design  High degree of control over program execution  Familiar 3GL programming techniques  Thread safety  Exposes layers of server metadata

Partners and Customers  Major GIS cartridge partners –

High performance N-tiered object-relational data access

 A major Telecom partner –

High performance N-tiered Oracle database access

 A major research institution –

Enormous and complex Oracle database access

 Many others… –

High performance application mid-tier servers

Agenda      

Survey Customer Requirements Meeting the Needs Realizing the Benefits Summary Q&A

Summary  OCCI (Oracle C++ Call Interface) – – – –

Simple and easy High-performance and Scalable API Comprehensive support of Oracle Database features Ideal choice for high performance application mid-tier implementation

Packaging  Oracle Programmer component  Both Client and Database Server CD  Static and shared library –

libocci9.a

 Header files –

public/occi.h, etc.

 Demos and makefiles –

demos/occi.mk

Platforms and Compilers Platform

O/S

C++ Compiler

Solaris Sparc 6.1

2.6

Forte C++

Solaris 64

2.8

Forte C++ 6.1

HP-UX 64

11.0

aC++ A.03.25

IBM AIX 64

4.3.3

VA C++ 5.0

Linux 2.95.2 NT Tru64 6.3

kernel 2.4 GNU GCC 4.0 5.0A

VC++ 5 & 6 Compaq C++

For Further Information . ..  Oracle Technology Network – otn.oracle.com – Discussion forums  Oracle C++ Call Interface

Oracle Technology Network

 Complete technical resource for Internet developers  Online technical discussion forum  Sample downloads & live demos  Whitepapers, technical information, documentation

http://otn.oracle.com

Q& A

Q U E S T I O N S A N S W E R S