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

Mark Michelson mmichelson at digium.com
Fri Dec 18 20:19:13 GMT 2015


Thanks for the exhaustive list of differences. This is definitely the 
best information I've seen on the subject. As it turns out, the 
restrictions that unixODBC has should not be a problem for me 
personally, but again thanks!

On 12/18/2015 12:16 PM, Kevin Adler wrote:
> 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
>
>
>
>
>
> _______________________________________________
> 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