Oracle C++ OCI Database Connection Pool

Aim
The aim of this bit of code is to implement an Oracle C++ (OCCI) Database Pool in Windows. A Database pool is normally used by applications to speed up the process of getting a Connection.

If you are just starting with Oracle C++ OCCI and would like a simple tutorial first the please go to Oracle OCCI C++ Database Example.
If you would like to see this DBPool implemented in MySQL the visit MySQL Connector C++ Database 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 CppDatabasePool. Also it is assumed that 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 Database
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.

The files used in this are given below.
Workspace
_|_CrossHelper.h
_|_CrossHelper.cpp
_|_CommonDatabasePool.cpp
_|_mysql
_|__|_MySQLPool.h
_|__|_MySQLPool.cpp
_|_oracle
_|__|_OraclePool.h
_|__|_OraclePool.cpp
_|_sqlserver
_|__|_SQLSvrPool.h
_|__|_SQLSvrPool.cpp
_|_examples
_|__|_ExampleOracle.cpp
_|__|_ExampleMySQL.cpp
_|__|_ExampleSQLSvr.cpp
_|__|_makefile.oracle
_|__|_makefile.mysql
_|__|_makefile.sqlsvr

You can download the zipped files here.

CrossHelper.h and CrossHelper.cpp - Define and implement the crossplatform functionality between windows and linux - I haven't finished the linux part yet.

CommonDatabasePool.cpp - This is the primary file that has a template and a map that stores the cached connections and a thread that runs to ping the database connections to keep them alive and prevent an inactive timeout. The idea is to potentially be able to use this template with different database implementations .

oracle/OraclePool.h and oracle/OraclePool.cpp - These classes actually inherit from the template above and implement the Oracle specific tasks. You will be using this class but not editing it.

examples/ExampleOracle.cpp - This has an example of how you use the pool.

makefile.oracle - Used to build the example. DO NOT forget the "/D WINDOWSXX" flag as this is needed to compile the widows version. Also remember to substitute your local directories for the ones in the bat file. There should be the oracle oci include directory and the oraocci10.lib binary for compile.

Below is the ExampleOracle.cpp file.
 1. /* Copyright 2009 Righteous Ninja AKA P.S. Ching*/
 2. #include <iostream>
 3. #include "..\oracle\OraclePool.h"
 4. 
 5. using namespace std;
 6. 
 7. int main(){
 8.     try{
 9. 
10.         OraclePool* oraclepool = new OraclePool(    "MYDATBASE",    //database (TNS)
11.                                                     "gldbuser",        //username
12.                                                     "gldbuser",        //password
13.                                                     300,            //keepalive timeout (seconds)
14.                                                     "select 0 from dual");    //keepalive statement
15. 
16.         /*Create a pool that will have 3 cached connections and will swell upto a 
17.           total of 5 connections. Returns the number of cached connections or -1 on error
18.           */
19.         if(oraclepool->CreatePool(3, 5)<=0){
20.             cout<<"Error creating database pool\n";
21.             cout<<oraclepool->GetLastPoolError()<<endl;    //If it's asystem error
22.             goto EXAMPLE_END;
23.         }
24. 
25.         /*Dispaly the pool information*/
26.         oraclepool->DisplayInfo(cout);
27. 
28.         oracle::occi::Connection *con;
29.         oracle::occi::Statement* stmt;
30.         oracle::occi::ResultSet* res;
31. 
32.         /*Get a connection from the pool*/
33.         if((con=oraclepool->GetConnectionFromPool())==0){
34.             cout<<"You have reached the maximum amout allowed - 5 in this example\n";
35.             goto EXAMPLE_END;
36.         }
37.         /*Select the schema and do a query. DO NOT delete the 'con' after the query.
38.           This will be released via the ReleaseConnectionToPool back to the pool
39.           */
40.         stmt = con->createStatement("select * from example");
41.         res = stmt->executeQuery();
42.         while (res->next())
43.             std::cout<<res->getInt(1)<<"  "<<res->getString(2)<<std::endl;
44.         
45.         stmt->closeResultSet(res);
46.         con->terminateStatement(stmt);
47. 
48.         /*Dispaly the pool information*/
49.         oraclepool->DisplayInfo(cout);
50. 
51.         /*Release the connection back into the pool*/
52.         oraclepool->ReleaseConnectionToPool(con);
53. 
54.         /*Dispaly the pool information*/
55.         oraclepool->DisplayInfo(cout);
56. 
57. 
58. EXAMPLE_END:
59. 
60.         /*Destroy the database pool*/
61.         if(oraclepool->DestroyPool()>0){
62.             cout<<"There are still some un-release connections in the pool\n";
63.         }
64.         
65.         delete oraclepool;
66. 
67.         char c;
68.         cout<<"Enter character to exit\n";
69.         cin>>c;
70. 
71.     }catch(oracle::occi::SQLException &e){
72.         std::cout<<e.what();
73.     }
74. 
75.  return 0;
76. }
Hide line numbers

As you can see the basic steps are..
  1. OraclePool* oraclepool = new OraclePool("mydatabase", "username","password", 300, "select 0 from dual");
    The keepalive thread will issue the "select 0 from dual" statement every 300 seconds across all the inactive connections in the cache to keep them alive.

  2. oraclepool ->CreatePool(3, 5);
    This creates a cache with 3 stored connections that will swell to a max of 5 connections when required. If you go above 5 connections without releasing any then you will get a 0 when you next call GetConnectionFromPool().

  3. con=oraclepool ->GetConnectionFromPool()
  4. Do stuff with the connection. Remember not to release or delete this connection as it will be returned to the pool.

  5. oraclepool ->ReleaseConnectionToPool(con);
  6. oraclepool ->DestroyPool();

Compiling and running the code
  1. Open a command prompt and go to the "examples" direcotory. Compile the files by running nmake. Make sure to substitute your local directories for the ones in the file. There should be the oracle oci include directory and the oraocci10.lib binary for compile.

    ..\CppDatabasePool\examples>nmake -f makefile.oracle

  2. 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 and the 'oci\lib\msvc\vc7' directory in your of your Oracle client installation.

    ..\CppDatabasePool\examples>set path=%path%;D:\oracle\product\10.2.0\client_1\oci\lib\msvc\vc7

    ..\CppDatabasePool\examples>set path=%path%;D:\oracle\product\10.2.0\client_1\BIN

  3. In the command prompt run 'testoracle.exe.' Make sure the database parameters are your own. ie. username, pasword, tns name etc.