Effective way to test ODBC connections

Question

Our product is a TCP listener transaction processor. Incoming connections are assigned a thread to handle the connection and connect to the DB to work with.

Instead of an expensive approach to establishing a new database connection for each incoming client connection, we support a database connection pool.

The database connection pool is quite customizable: minimum / maximum sizes, growth rates, etc.

Some information:

• Windows 2003/2008 R2 Platform
• DB is SQL Server 2005/2008 R2
• Connection Method - ODBC
• C ++ programming language

Finally, the question is:

Since the service can run for several months without rebooting, there is a real chance that some database connections in the pool become invalid. I want to have the fastest way to verify the validity of a given connection before assigning it to an incoming connection.

I am currently doing this by executing the simple SQL statement “SELECT 123;”, however I have found that this has significant negative consequences when using parallel execution plans.

Very briefly in the code, what I do:

// ... at some point we decide pool needs another connection... // Set up database connection SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env); SQLAllocHandle(SQL_HANDLE_DBC, env, &conn); SQLDriverConnect(conn, 0, in_str, in_len, out_str, DIM(out_str), &out_len, SQL_DRIVER_NOPROMPT); // 'conn' is placed in DB connection pool // ... some time later a new client connection comes in ... // Execute simple statement to test if 'conn' is still OK SQLAllocHandle(SQL_HANDLE_STMT, conn, &stmt); SQLExecDirect(stmt, (SQLCHAR*)"SELECT 1;", SQL_NTS); // If 'conn' is OK, give it to incoming connection; // if not, get another connection from pool 

Cheers
Dave

Answer 1

Well, the official way is SQLGetConnectAttr (SQL_ATTR_CONNECTION_DEAD), which checks if the connection works on the last attempt.

Or SQLGetConnectAttr (conn, SQL_COPT_SS_CONNECTION_DEAD, ...), which checks if the connection is working now.