MySQL Connector C++ Database Connection Pool

Aim
The aim of this bit of code is to implement a MySQL connector C++ Database Pool in Windows. A Database pool is normally used by applications to speed up the process of getting a Connection. For an introduction to using MySQL Connector C++ see MySQL Connector C++ Example - Windows cl.exe (VC++).

If you'd like to see this in DBPool implemented for Oracle, then visit Oracle C++ OCI Database Pool .

Assumptions
This article assumes that you have the MySQL database installed and running and you have donwloaded and unzipped/installed the MySQL Connector C++ API. For purposes of this example the the project directory is CppDatabasePool. Also it is assumed 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/ComponentImage
Windows XP SP2N/A
MySQL Databasemysql-noinstall-5.1.32-win32.zip
MySQL Connector C++mysql-connector-c++-noinstall-1.0.5-win32.zip
Visual Studio Express Editions 2008 VC++N/A
Links to these files can be found here

MySQL is installed (expanded) in the "D:\downloads\mysql\mysql-5.1.32-win32" for this example. Also MySQL Connector C++ is expanded in the "D:\downloads\mysql-connector-c++-noinstall-1.0.5-win32" directory. Please remember to substitute your own directories for these.

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.slqsvr

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 other implementations and not just MySQL Connector C++ only.

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

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

buildmysql.bat - 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 file. They should be the mysql connector c++ include directory and the mysqlcppconn.lib.

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

As you can see the basic steps are..
  1. MySQLPool* mysqlpool = new MySQLPool("tcp://127.0.0.1:3306", "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. mysqlpool->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=mysqlpool->GetConnectionFromPool()
  4. Do stuff with the connection. Remember not to release or delete the connection as it will be returned to the pool.
  5. mysqlpool->ReleaseConnectionToPool(con);
  6. mysqlpool->DestroyPool();

Compiling and running the code
  1. Open a command prompt and compile the files by running nmake Make sure to substitute your local directories for the ones in the file. These should be the mysql connector c++ include directory and the mysqlcppconn.lib.

    ..\CppDatabasePool\examples>nmake -f makefile.mysql
  2. `
  3. 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.

    ..\CppDatabasePool\examples>set path=%path%;D:\downloads\mysql\mysql-5.1.32-win32\bin

    ..\CppDatabasePool\examples>set path=%path%;D:\downloads\mysql-connector-c++-noinstall-1.0.5-win32\lib
  4. `
  5. In the command prompt run 'testmysql.exe.' Make sure the database parameters are your own. ie. username, pasword, url, and schema.

Back to the tutorial trail | Home

13 comments:

Anonymous said...

This is just what I was looking for. The templates are a nice idea. Good Luck

jacks said...

Really great ideas. I like every example. Just might have to try these... So cute! Thank you!
more templates easy to download

Andrey said...

thank you!
would not it be better to use poolmap.clear() after the loop exits instead of using poolmap.erase(iter) in int CommonDatabasePool::DestroyPool()?

I am frankly not sureif it is legal at all to use erase inside of a loop.

Thanks again for the code!

Anonymous said...

It's very nice code. But I think we should implement 3 kinds of connection in pool.

1> Concrete connection (never release)
2> Semi-Concrete Connection (Create then after 3 minutes for example, release)
3> Non-Concrete Connection (create then release right after using)

haimai
thekingofmind@gmail.com
thekingofmind@gmail.com

hai said...

1> Concrete connection (never release)
2> Semi-Concrete Connection (Create then after 3 minutes IDLE for example, release)
3> Non-Concrete Connection (create then release right after using)

Tank said...

Thanks very much.
BTW,MySQLPool.cpp MyPingConcreteConnection has memory leaks, fixed code:

int MySQLPool::MyPingConcreteConnection(sql::Connection *con){
try{
sql::Statement* stmt = con->createStatement();
sql::ResultSet *res = stmt->executeQuery(keepalivequery);

stmt->close();
delete stmt;
stmt = NULL;

res->close();
delete res;
res = NULL;

return 0;
}
catch(sql::SQLException &e)
{
std::cout<<"SQL error "<<e.getErrorCode()<<" at "<<__FUNCTION__
<<std::cout<<"\nDescription :"<<e.what()<<std::endl;
return -1;
}
}

Tank said...

@Andrey
the code throws exception on using erase in function DestroyPool.
I suggest save the release list, then releasing them in another for loop,outside the first for loop.
Or like this, release all the connection no matter whether the connection is using, but it may cause some work lost. To avoid this problem, we should make sure commit all the connection before DestroyPool. Code:
for( iter = poolmap.begin(); !poolmap.empty()&& iter != poolmap.end(); iter++ )
{
/*if(iter->second == DBPOOL_POOLEDCON_FREE)
{
MyReleaseConcreteConnection(iter->first);
poolmap.erase(iter);
}*/
MyReleaseConcreteConnection(iter->first); // by tank
}
poolmap.clear(); // by tank

Jacky.Huang said...

Thank you very much!
This is what I am looking for.

When I used mysql template on windows, I found a bug, I would like to feedback to you.

template
int CommonDatabasePool::DestroyPool(){
typename map::iterator iter;
for( iter = poolmap.begin(); !poolmap.empty()&& iter != poolmap.end(); iter++ ) {
if(iter->second == DBPOOL_POOLEDCON_FREE){
MyReleaseConcreteConnection(iter->first);
poolmap.erase(iter);
}
}
exitkeepalivepool = true;

if(XSemPost(&semhandle)==-1){
}
if(XThreadJoin(&threadhandle)==-1){
}

return poolmap.size();
}

When we use map earase method (poolmap.erase(iter);) like that,
there will throw out an error like "map/set iterator not incrementable".

It will be solved when I change something as follow,
because when invoke the erase method, current iterator is invalid.

template
int CommonDatabasePool::DestroyPool(){
typename map::iterator iter;
for( iter = poolmap.begin(); !poolmap.empty()&& iter != poolmap.end();) {
if(iter->second == DBPOOL_POOLEDCON_FREE){
MyReleaseConcreteConnection(iter->first);
poolmap.erase(iter++);
}
else
{
iter++;
}
}
exitkeepalivepool = true;

if(XSemPost(&semhandle)==-1){
}
if(XThreadJoin(&threadhandle)==-1){
}

return poolmap.size();
}

Thank you for you code again.

Jacky.Huang

Jacky.Huang said...

Linux part in CrossHelper.cpp file is not implemented.

#else /***Linux implementations***/
/*
TO DO
*/
#endif

Oh, No!! Do you have any plan to do that?
It's very import for me, I think it is the same to the others.

Sergei Sobolev said...

is it safe to use connection in one thread created in another?
why you don't call mysql_thread_init()?

Naviya Nair said...

Very interesting and good Explanation
ASP NET Training
ASP NET Training
ASP NET Online Training
C-Sharp Training
Dot Net Training in Chennai
Online .Net Training


MVC Training
WCF Training
Web-API Training
LINQ Training
Entity Framework
Training

Dot Net Interview Questions

Rasool Bevi said...

I feel satisfied with your blog, you have been delivering useful & unique information to our vision even you have explained the concept as deep clean without having any uncertainty, keep blogging.

DOT NET Training in Chennai
DOT NET Course in Chennai
DOT NET Course Chennai
DOT NET Training Institute in Chennai
DOT NET Training Institutes in Chennai

sivanesan said...

Excellent post. I have read your blog it's very interesting and informative. Keep sharing.
erp in chennai