[unixODBC-support] Connection pooling: Use unixODBC built-in or roll my own?
kadler at us.ibm.com
Fri Dec 18 18:16:19 GMT 2015
Last I looked at it, there was some differences between the MS
specification and the unixODBC implementation:
Here's how it works on Windows:
- enable connection pooling in the registry or using ODBC Administrator to
enable connection pooling in driver manager
- set CPTimeout value for your driver in the registry or using ODBC
Administrator to enable connection pooling for the driver
- call SQLSetEnvAttr with a NULL env handle for attribute
SQL_ATTR_CONNECTION_POOLING with the value SQL_CP_ONE_PER_DRIVER or
SQL_CP_ONE_PER_HENV, prior to allocating an environment handle to enable
connection pooling for your application. Setting the value to SQL_CP_OFF
will disable connection pooling (default to off)
- if you set SQL_CP_ONE_PER_DRIVER, connection pooling is done at a
process level. If you set SQL_CP_ONE_PER_HENV, connection pooling is done
at an environment level
- an application can set env attribute SQL_ATTR_CP_MATCH to either
SQL_CP_STRICT_MATCH (default) or SQL_CP_RELAXED_MATCH to determine how
matches are found from the connection pool. Strict matching ensures that
connection options and attributes must match for reusing connections from
the pool. Relaxed matching only requires connection string keywords to
Here's how it works on unixODBC:
- to enable connection pooling, you must set Pooling=Yes in odbc.ini
- set CPTimeout to a non-zero value to enable connection pooling for the
- once connection pooling has been enabled in odbc.ini and CPTimeout set,
all applications using that driver will automatically use pooling
- an application can call SQLSetEnvAttr for SQL_ATTR_CONNECTION_POOLING,
but it is just ignored
- no support for SQL_CP_ONE_PER_HENV
- no support for SQL_CP_STRICT_MATCH, relaxed matching is the default
- no way to enable/disable connection pooling per application
If you don't care about those limitations, connection pooling does work in
unixODBC. I'd also recommend getting on the latest unixODBC, at least past
2.3.0. I seem to recall hitting a mutex deadlock issue, but can't remember
if it was fixed in 2.2.14 or possibly 2.3.2.
From: Mark Michelson <mmichelson at digium.com>
To: unixodbc-support at mailman.unixodbc.org
Date: 12/18/2015 09:37 AM
Subject: [unixODBC-support] Connection pooling: Use unixODBC
built-in or roll my own?
Sent by: unixodbc-support-bounces at mailman.unixodbc.org
Our project makes use of the unixODBC C API. We currently have a
poorly-implemented version of connection pooling within our application,
and we want to make improvements.
Ideally, we would switch to a model where our application has no
knowledge of connection pooling whatsoever and rely on unixODBC to take
care of it under the hood. However, documentation on the matter is
pretty scarce. The only official document I found refers to a version of
unixODBC released in 2001 and claims that connection pooling "[is] still
in development"  . The only other things I found were discussions
about whether the use of connection pooling would be a good fit for the
type of application being used. In our case, connection pooling is
definitely something useful.
I have a few questions about this: First, is the claim of the feature
still being in development true today? Second, does anyone have any
first-hand knowledge, good or bad, about using unixODBC connection
I've had a look at the source code of unixODBC, and from an "on-paper"
point of view, it looks like a simple solution that we could rely on.
However, when it comes to making fundamental changes like this to our
code, I'd like to have more confidence that we're making the correct move.
unixODBC-support mailing list
unixODBC-support at mailman.unixodbc.org
More information about the unixODBC-support