[unixODBC-support] Connection pooling: Use unixODBC built-in or roll my own?

Kevin Adler 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 
match.

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

unixODBC documentation:
http://www.unixodbc.org/doc/conn_pool.html

Microsoft documentation:
https://msdn.microsoft.com/en-us/library/ms709288%28v=vs.85%29.aspx
https://msdn.microsoft.com/en-us/library/ms716319%28v=vs.85%29.aspx
https://msdn.microsoft.com/en-us/library/ms709285%28v=vs.85%29.aspx




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



Hi folks,

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" [1] . 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 
pooling?

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.

Thank you,
Mark Michelson

[1] http://www.unixodbc.org/doc/conn_pool.html
_______________________________________________
unixODBC-support mailing list
unixODBC-support at mailman.unixodbc.org
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support







More information about the unixODBC-support mailing list