SQL Server C++ ODBC Connection Pool

The aim of this bit of code is to implement an SQL Server ODBC C++ 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 ODBC and would like a simple tutorial first the please go to SQL Server C++ ODBC Example.
If you would like to see this DBPool implemented in MySQL the visit MySQL Connector C++ Database Pool .

This article assumes that you have SQL Server installed and running. 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
Windows XP SP2
Visual Studio Express Editions 2008 VC++
Links to these files can be found here

The files used in this are given below.

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 .

sqlserver/SQLSvrPool.h and sqlserver/SQLSvrPool.cpp - These classes actually inherit from the template above and implement the C++ ODBC specific tasks. You will be using this class but not editing it.

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

makefile.sqlsvr - 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. You should link to odbc32.lib binary for compile.

Below is the ExampleSQLSvr.cpp file.
 1. /* Copyright 2009 Righteous Ninja AKA P.S. Ching*/
 2. #include <iostream>
 3. #include "..\sqlserver\SQLSvrPool.h"
 5. using namespace std;
 7. int main(){
 9.         SQLSvrPool* sqlsvrpool = new SQLSvrPool(    "localhost",    //hostnam
10.                                                     "MyDatabase",    //database 
11.                                                     "sa",            //username
12.                                                     "Admin-123",    //password
13.                                                     300000,        //keepalive timeout (milliseconds)
14.                                                     "SELECT top 1 name FROM dbo.sysobjects");    //keepalive statement
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(sqlsvrpool->CreatePool(3, 5)<=0){
20.             cout<<"Error creating database pool\n";
21.             cout<<sqlsvrpool->GetLastSystemError()<<endl;    //If it's asystem error
22.             goto EXAMPLE_END;
23.         }
25.         /*Dispaly the pool information*/
26.         cout<<(*sqlsvrpool);
28.         SQLHANDLE* psqlconnectionhandle;
29.         SQLHANDLE  sqlstatementhandle;
32.         /*Test the validity of the pool at anytime - not really required*/
33.         if(!sqlsvrpool->IsPoolValid())
34.             goto EXAMPLE_END;
36.         /*Get a connection from the pool*/
37.         if((psqlconnectionhandle=sqlsvrpool->GetConnectionFromPool())==0){
38.             cout<<"You have reached the maximum amout allowed - 5 in this example\n";
39.             goto EXAMPLE_END;
40.         }
42.         /*Get a statement handle from the connection*/
43.         if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_STMT, *psqlconnectionhandle, &sqlstatementhandle)){
44.             sqlsvrpool->ShowSQLError(cout, SQL_HANDLE_DBC, *psqlconnectionhandle);
45.             goto EXAMPLE_END;
46.         }
48.         /*Execute the query and display the results. Do not Free the database connection handle.
49.           This will be released via the ReleaseConnectionToPool back to the pool.
50.          */
51.         if(SQL_SUCCESS!=SQLExecDirect(sqlstatementhandle, (SQLCHAR*)"select * from testtable", SQL_NTS)){
52.             sqlsvrpool->ShowSQLError(cout, SQL_HANDLE_STMT, sqlstatementhandle);
53.             goto EXAMPLE_END;
54.         }
55.         else{
56.             char name[64];
57.             char address[64];
58.             int id;
59.             while(SQLFetch(sqlstatementhandle)==SQL_SUCCESS){
60.                 SQLGetData(sqlstatementhandle, 1, SQL_C_ULONG, &id, 0, NULL);
61.                 SQLGetData(sqlstatementhandle, 2, SQL_C_CHAR, name, 64, NULL);
62.                 SQLGetData(sqlstatementhandle, 3, SQL_C_CHAR, address, 64, NULL);
63.                 cout<<id<<" "<<name<<" "<<address<<endl;
64.             }
65.         }
66.         SQLFreeHandle(SQL_HANDLE_STMT, sqlstatementhandle );
68.         /*Dispaly the pool information*/
69.         cout<<(*sqlsvrpool);
72.         /*Release the connection back into the pool*/
73.         sqlsvrpool->ReleaseConnectionToPool(psqlconnectionhandle);
75.         /*Dispaly the pool information*/
76.         cout<<(*sqlsvrpool);
78.         char c;
79.         cout<<"Enter character to exit\n";
80.         cin>>c;
84.         /*Destroy the database pool*/
85.         if(sqlsvrpool->DestroyPool()>0){
86.             cout<<"There are still some un-released connections in the pool\n";
87.         }
89.         delete sqlsvrpool;
91.  return 0;
92. }
Hide line numbers
As you can see the basic steps are..
  1. SQLSvrPool* sqlsvrpool = new SQLSvrPool("hostname", "mydatabase", "username","password", 300000, "SELECT top 1 name FROM dbo.sysobjects");
    The keepalive thread will issue the "SELECT top 1 name FROM dbo.sysobjects" statement every 300 seconds across all the inactive connections in the cache to keep them alive.
  2. sqlsvrpool ->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. pconnectionhandle=sqlsvrpool->GetConnectionFromPool()
  4. Do stuff with the connection. Remember not to release or delete this connection as it will be returned to the pool.
  5. sqlsvrpool ->ReleaseConnectionToPool(pconnectionhandle);
  6. sqlsvrpool ->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. `
  4. In the command prompt run 'testoracle.exe.' Make sure the database parameters are your own. ie. username, pasword, tns name etc.

Back to the tutorial trail | Home