Oracle C++ Call Interface: A New API for High-Performance Internet Applications

Geoff LeeSr. Product Manager Oracle Corporation

Oracle C++ Call Interface A New API for High-Performance Internet Applications

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

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

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 Conn ConnPool Create getMD Create Stmt getMD MetaData getResultSet ResultSet

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

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

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

ConnectionPoolingection Pooling c1 t1 Pool 1 db1 t2 s1 c2 s2 t3 s3 s4 t4 s5 c3 db2 Pool 2 t5 s6 c4 t6 Databases Application Layer OCI Layer

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<T> ResultSet *rs = stmt->executeQuery( “SELECT REF(address) FROM ADDRESS_TAB address where zip = ‘94065’ “); rs->next(); Ref<ADDRESS> addr_ref = rs->getRef(1);

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

Client-side Object Cache Clientside cache Server buffer cache REF flush load Pin Network

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

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<T> before de-referencing • prefetch attributes of the specified type name up to the the specified depth Ref<T>::setPrefetch(const string &typeName, unsigned int depth); • prefetch all the attribute types up to the specified depth Ref<T>::setPrefetch(unsigned int depth);

OTT for Type Inheritance • Generates classes from inheritance hierarchy class <typename> : public <parentTypename> { protected: <OCCItype1> <attributename1>; ... 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<Ref<ADDRESS> > 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(); }

Example :- (OTT code) class PERSON : public PObject { protected : string name; vector<Ref<ADDRESS> > 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; }

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

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

Oracle C++ Call Interface: A New API for High-Performance Internet Applications