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

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Mark Michelson
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
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Connection pooling: Use unixODBC built-in or roll my own?

Nick Gorham-2
On 18/12/15 15:18, Mark Michelson wrote:

> 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

Hi,

Well, to the best of my knowledge, pooling is in the driver manager and
works as described (copy of the way MS pooling was done). There have
been a few fixes mostly relating to making sure its thread safe. I don't
know of any outstanding problems with it at the moment. If there were ,
they would get fixed. Thats mainly how it goes, AFAIK, it works
perfectly well unless someone else knows otherwise. i can't at the end
of the day make your choices for you, but it sounds like you are already
using unixODBC as part of your product, so it may not be that big a
jump. And you have looked at the code, it should be as easy to fix the
problem in unixOBC if you find one than your own code, and that way the
rest of the user base gets the benefit as well.

--
Nick


_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Connection pooling: Use unixODBC built-in or roll my own?

Kevin Adler
In reply to this post by Mark Michelson
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 <[hidden email]>
To:     [hidden email]
Date:   12/18/2015 09:37 AM
Subject:        [unixODBC-support] Connection pooling: Use unixODBC
built-in or     roll my own?
Sent by:        [hidden email]



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
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support





_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Connection pooling: Use unixODBC built-in or roll my own?

Mark Michelson
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 <[hidden email]>
> To:     [hidden email]
> Date:   12/18/2015 09:37 AM
> Subject:        [unixODBC-support] Connection pooling: Use unixODBC
> built-in or     roll my own?
> Sent by:        [hidden email]
>
>
>
> 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
> [hidden email]
> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
>
>
>
>
>
> _______________________________________________
> unixODBC-support mailing list
> [hidden email]
> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Loading...