MySQL C++ Connection Pool

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/Component	Image
Windows XP SP2	N/A
MySQL Database	mysql-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. 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

`
3. In the command prompt run 'testmysql.exe.' Make sure the database parameters are your own. ie. username, pasword, url, and schema.

http://www.tidytutorials.com/2009/07/mysql-connector-c-database-pool.html