Connect to Oracle (C ++) through occi)

Occi Introduction

Occi: Oracle C ++ call interface (occi), that is, Oracle C ++ API, allows you to access the Oracle database using object-oriented features, local classes, and C ++ languages.

The advantages of occi are based on the standard C ++ and object-oriented design, with high efficiency. It is suitable for developing C/S-mode programs and software middle layers; full support for SQL and PLSQL provides elastic options for increasing users and requests to use user-defined types, such as classes in C, provides seamless interfaces to support all Oracle Data Types and LOB types to access database metadata occi header files and dynamic link libraries.

Occi header file

• Occi. h • occicommon. h • occicontrol. h • occidata. h • occiobjects. h

Occi Library

• Libocci. So/libocci. A/oraocci9.dll build the application

Step 1: InitializeOcci creates an environment object to complete initialization. You can use environment to create a database connection and perform other operations to create an environment. You should call the static method of the Environment class.Createenvironment ()Example:

// Include 1 header file for all occi classes/interfaces # include <occi. h> // create environmentenvironment * Env = environment: createenvironment (); // create a connection object pointer // use the environment instance to create connections, // database access... // Terminate environment by calling static method // environment: terminateenvironmentenvironment: terminateenvironment (ENV); // close

Step 2: connect to the database

Connect to the database through the object instance of the connection class to call the environment class Createconnection ()You can create a connection object;

Connection *Environment::createConnection( const string &userName,const string &password, const string &connectString )

After successfully connecting to the database, you can use it to access data and execute SQL statements. Use Environment: terminateconnection ()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); // disconnect

Step 3: Execute SQL/PLSQL

The statement class is used to execute SQL/PLSQL statements and obtain the returned results. The resultset class is used to process the SELECT query results. Occi provides a unified method for binding or obtaining all types of data.

-Setxxx Method for statement

-The getxxx method is used for Statement & resultset.

Occi automatically converts data types. Usage: Use connection :: Createstatement ()Create a statement object and specify the SQL command (DDL/DML/query) as the parameter.

Connection: createstatement (string & SQL );

Statement: setsql (string & SQL );

Statement: Execute (string & SQL);-can be used for any SQL, returnsstatus

Statement: executeupdate (string & SQL);-returns insert/update/delete count

Statement: executequery (string & SQL);-returns resultset (result set)

Use the setxxx method to pass the values to be bound for input. Use the appropriate execute method to execute SQL statements. For select queries, use the resultset object to process the returned results. A simple DML insert example:

//createStatement() on Connection class gives a Statement//instanceStatement *stmt = conn->createStatement(“ insert into Dept(Deptno,Dname, Loc) values (1, ‘ACCOUNTS’, ‘ZONE1’ ”);//executeUpdate for all INSERT/UPDATE/DELETEstmt->executeUpdate();conn->terminateStatement(stmt);

DML example of binding parameters:

Statement *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();

Execute the SELECT query and process the result:

Statement *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

Run PL/SQL:

To execute PL/SQL, you should specify the PL/SQL block when creating statement and use setxxx to pass all input parameters (in andin/out) to PLSQL functions or use statement in the process :: registeroutparam is used to specify the out parameter. The parameter size is specified using statement: setmaxparamsize to use statement: Execute () run PL/SQL and use the getxxx method to obtain the function execution result and the out/In out parameter example: Calling PL/SQL function/procedure

//PLSQL function : functionCalculateBonus(EmpNo INNumber,//                           EmpStatus IN OUT VARCHAR2,//                           Bonus OUT Number)RETURN VARCHAR2//call function usinganonymous blockStatement *stmt = conn->createStatement(“ begin :1 := CalculateBonus(            :2, :3, :4); end;”);//bind position 1 is thefunction’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’sreturn valuestmt->setMaxParamSize(1, 100);//setMaxParamSize for STRING typesstmt->registerOutParam(4, OCCIFLOAT);stmt->execute();//use getXXX methods of Statement to get OUTparameters, return valuestring msg = stmt->getString(1);//function return valuestring newstatus = stmt->getString(3);//IN OUT parameterfloat bonus = stmt->getFloat(4); //OUT parameter

Step 4: handle errorsOcci uses the C ++ exception mechanism to return all error messages. The application should use the try-catch structure to handle exceptions. Thrown exceptions belong to the sqlexception type, this type inherits from the exception class in Standard C ++. You can use the geterrorcode and getmessage methods to obtain Oracle error information.

Example:

Handle Oracle and C ++ STL errors respectively

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