Oracle C++ OCI Database Example

Aim
The aim of this C++ tutorial is to create a simple client that uses Oracle C++ OCI (OCCI Oracle C++ Call Interface) to connect to an Oracle database. We will be using the windows command line C++ compiler cl.exe that comes with Visual Studio C++ for this example. This demonstrates connectiong to the Oracle database and manipulating data. Leave any questions or problems as comments and I will endeavour to answer them.

If you would like to see a database pool implemented for Oracle OCCI the please visit Oracle C++ OCI Database Connection Pool.

Assumptions
This article assumes that you have an Oracle database installed and running and you have downloaded and installed/unzipped the Oracle Client which contains the header files and libs required. For purposes of this example the the project directory is OracleOCCIExample. Also you have VC++ installed and configured. See Problems and Solutions Installing VC++ Express Edition, to install and run vcvars32.bat.

Versions used in this example

Sofware/Component	Image
Windows XP SP2	N/A
Oracle Client	10201_client_win32.zip
Visual Studio Express Editions 2008 VC++	N/A

Links to these files can be found here

Oracle client is installed (expanded) in the "D:\oracle" directory for this example. Please remember to substitute your own directories for these.

NOTE: OCCI uses tnsnames.ora to connect to the database. So make sure you have this file configured with the database details you wish to connect to.


Write and compile the Client

1. Write the client and save it as oracleexample.cpp in your working directory.
   

   1. #include <iostream> 2. #include <occi.h> 3. 4. using namespace std; 5. 6. int main(){ 7. 8. oracle::occi::Environment* environment; 9. oracle::occi::Connection *con; 10. oracle::occi::Statement* stmt; 11. oracle::occi::ResultSet* res; 12. 13. try{ 14. 15. environment = oracle::occi::Environment::createEnvironment(oracle::occi::Environment::DEFAULT); 16. con = environment->createConnection("gldbuser", "gldbuser", "MYDATABSE"); 17. 18. stmt = con->createStatement("select * from example"); 19. res = stmt->executeQuery(); 20. 21. while (res->next()) 22. std::cout<<res->getInt(1)<<" "<<res->getString(2)<<std::endl; 23. 24. stmt->closeResultSet(res); 25. con->terminateStatement(stmt); 26. environment->terminateConnection(con); 27. 28. }catch(oracle::occi::SQLException &e){ 29. std::cout<<e.what(); 30. } 31. 32. return 0; 33. } Hide line numbers

   
   
2. Open a prompt to the working directory and compile the code using the windows cl.exe compiler. Make sure to point '-I' (capital i) option to the include files and to include mysqlcppconn.lib
   

   ..workspace\OracleOCCIExample>cl -o oracletest.exe oracleexample.cpp -I D:\oracle\product\10.2.0\client_1\oci\include D:\oracle\product\10.2.0\client_1\oci\lib\msvc\oraocci10.lib

   Substitue your local directories.
   
   
3. This should now create the ocalceexample.exe file. However if you run it now you might get some errors saying that some DLLs are missing.
   
   
4. Add these paths to your existing path so that the executable can find the required libraries. Again, don't forget to replace the directories with the ones on your local machine. Basically you need to give the 'bin' diretory of your MySQL database installation and the 'lib' directory in your MySQL Connector C++ directory.
   
   

   ..\worksapce\OracleOCCIExample>set path=%path%;D:\oracle\product\10.2.0\client_1\oci\lib\msvc\vc7 ..\worksapce\OracleOCCIExample>set path=%path%;D:\oracle\product\10.2.0\client_1\BIN

   
   
5. Now run the oracleexample.exe and you should see the result. The most likely errors are errors stemming from problems with the tnsnames.ora and maybe DLL entry points when you run the program. To fix the DLL entry points you can set your classpath only to the paths specified above, to make sure you are not picking up any other dll.