Connecting to Oracle via OCCI (c + +)

OCCI Introduction

The occi:oracle C + + Call Interface (OCCI), Oracle's C++API, allows you to access the Oracle database using object-oriented features, native classes, and C + + language methods.

Occi Advantage is based on standard C + + and object-oriented design, high efficiency, suitable for developing C/S mode program, software middle layer; Occi feature full support Sql/plsql provides resiliency options for growing users and requests to use user-defined types, such as classes in C, Provides a seamless interface 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 Building Applications Step 1: InitializeOcci by creating aobjects of the environmentComplete the initialization work. You can create a database connection through environment for other operations to create environment, you should call the static method of the Environment class createenvironment ()Example: [CPP]View Plaincopy

2. <span style= "COLOR: #000000;" > //include 1 header file for all OCCI classes/interfaces   
4. #include <occi.h>   
8. //create Environment   
10. Environment *env = environment::createenvironment (); //Create a Connection object pointer   
12. //use the environment instance to create connections,   
14. //database Access   
16. ...
20. //terminate environment by calling static method   
22. //environment::terminateenvironment   
24. Environment::terminateenvironment (env); //Close </span>   

Step two: Connect to the databaseConnect to the database byConnection Classobject instance implementation that calls the environment class. createconnection ()Method can create a connection object;

[CPP]View Plaincopy

1. Connection *environment::createconnection ( const string &username,const string &password, Const string &connectstring)

After a successful connection to the database, you can use it to access the data, execute the SQL statement; environment::terminateconnection ()Disconnect; Example: [CPP]View Plaincopy

2. //first Need Environment   
4. Environment *env = environment::createenvironment ();
8. Connection *conn=env->createconnection ("Scott", "Tiger", "" ");
10. //3rd parameter is db name/tns alias   
14. .. //database Access–use the Connection object   
16. ..
18. ..
20. //logoff and Terminate connection   
22. Env->terminateconnection (conn); //Disconnect   

Step Three: Execute Sql/plsqlStatement ClassUsed to execute the SQL/PLSQL statement and get the return result.ResultSet classUsed to process the results of a select query. OCCI provides a unified approach to binding or acquiring all types of data

-Setxxx method for statement

-GetXXX method for statement & ResultSet

Occi automatically handles conversions between types. How to use: Use Connection:: createstatement ()Creates a statement object specifying the SQL command (ddl/dml/query) as a 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 value you want to bind to the input using the appropriate execute method to execute SQL for the select query, using the ResultSet object to handle the simple DML insert example that returns the result: [CPP]View Plaincopy

1. //createstatement () on Connection class gives a Statement   
2. //instance   
3. Statement *stmt = conn->createstatement ("INSERT into Dept (Deptno,dname, Loc) VALUES (1, ' ACCOUNTS ', ' ZONE1 '");
4. //executeupdate for all Insert/update/delete   
5. Stmt->executeupdate ();
6. Conn->terminatestatement (stmt);

Examples of DML using binding parameters: [CPP]View Plaincopy

2. Statement *stmt = conn->createstatement ("INSERT INTO
4. EMP (Empno,ename) VALUES (: 1,: 2) ");
6. //1 and 2 are bind placeholders   
8. int  empno = 2;
10. String empname = "JOHN W";
12. //first parameter is bind position, second is value   
14. Stmt->setint (1, empno);
16. Stmt->setstring (2, EmpName);
18. Stmt->executeupdate ();

[CPP]View Plaincopy

Execute a SELECT query and process the results: [CPP]View Plaincopy

2. Statement *stmt = conn->createstatement ("Select Empno,
4. ename, Sal from EMP where hiredate >=: 1 ");
8. //automatically converted to Date   
10. Stmt->setstring (1, "01-jan-1987");
14. //executequery returns a ResultSet   
16. ResultSet *rs = Stmt->executequery ();
20. //resultset::next fetches rows and returns FALSE   
22. //when no more rows   
24. while (rs->next () = = true)
26. {
28. //get values using the GetXXX methods of ResultSet   
30. Empno = Rs->getint (1);
32. EmpName = rs->getstring (2);
34. Empsalary = Rs->getfloat (3);
36. }
38. Stmt->closeresultset (RS); //to Free Resources   

Execute PL/sql:

To perform PL/SQL, you should specify a PL/SQL block to use SETXXX to pass all input parameters (in andin/out) to the Plsql function or procedure using statement when creating statement:: Registeroutparam to specify the out parameter, the size of the parameter is specified by Statement::setmaxparamsize using Statement::execute () execution pl/ SQL uses the GetXXX method to get the results of function execution and out/in out parameters example: calling PL/SQL function/procedure

[CPP]View Plaincopy

2. //plsql Function:functioncalculatebonus (EmpNo innumber,
4. //Empstatus in Out VARCHAR2,   
6. //Bonus out number) RETURN VARCHAR2
8. //call function usinganonymous block   
10. Statement *stmt = Conn->createstatement ("Begin:1: = Calculatebonus (
12. : 2,: 3,: 4); end; ");
14. //bind position 1 is thefunction ' s return value   
16. Stmt->setint (2, 100); //in parameter   
18. Stmt->setstring (3, "Active"); //in out parameter   
20. //call Registeroutparam for each out parameter   
22. Stmt->registeroutparam (1, occistring, 1000); //function ' Sreturn value   
24. Stmt->setmaxparamsize (1, 100); //setmaxparamsize for STRING types   
26. Stmt->registeroutparam (4, occifloat);
28. Stmt->execute ();
30. //use GetXXX methods of Statement to get outparameters, return value   
32. String msg = stmt->getstring (1); //function return value   
34. String newstatus = stmt->getstring (3); //in out parameter   
36. float bonus = stmt->getfloat (4); //out parameter   

Step four: error handlingOcci uses the C + + exception mechanism to return all error messages applications should use the Try-catch structure to handle exceptions thrown by the exception belonging to the SqlException type, which inherits from standard C + + The exception class in can use the GetErrorCode and GetMessage methods to obtain an Oracle error message

Example:

Handling Oracle and C + + STL errors separately

[CPP]View Plaincopy

2. Try   
4. {
6. ResultSet *rs = Stmt->executequery ();
8. while (Rs->next ())
10. ..........
12. }
14. Catch (SQLException &oraex) //oracle/occi Errors   
16. {
18. int errno = Oraex->geterrorcode (); //returns the ORA number   
20. String errmsg = Oraex->getmessage ();
22. //more Application Error handling   
24. }
26. Catch (Exception &ex) //any other C++/stl error   
28. {
30. cout << "Error" << ex.what () << Endl;
32. }