[egenix-users] Pooling connections

M.-A. Lemburg mal at egenix.com
Sun Nov 12 16:47:33 CET 2017


On 09.11.2017 21:02, Jan Murre wrote:
> Hi,
> 
> We are using mxODBC  with the native Microsoft MS SQL Linux driver (version
> 13.0) and we are opening and closing a connection for every database call.
> 
> This is considered to be  not very efficient, although I am not sure if
> mxODBC does some clever pooling of connections or not.
> 
> We tried to add pooling at the unixODBC level, according to this:
> http://www.unixodbc.org/doc/conn_pool.html
> 
> [ODBC]
> Pooling=Yes
> 
> And in the drivers section:
> CPTimeout=120
> 
> 
> After that, our connections fail to work with this error:
> [unixODBC][Driver Manager]Driver does not support this function

unixODBC connection pooling often creates issues with drivers.
We recommend against using this.

> My question is, what is the best way to do connection pooling with mxODBC
> and the native Microsoft MS SQL linux driver?

The best way is to do connection pooling at the application
level. You typically only need to open connections once
per application (and reopen them in case they fail for some
reason).

In the application itself, you then just run conn.commit()
and conn.rollback() to delimit the transactions and then open
cursors for running queries.

The simplest way is to open a new cursor for every query,
but it's, of course, also possible to use cursors for multiple
queries.

You can also go one step further and cache cursors, i.e. always
using the same cursor for the same common query, without closing
them. You only have to make sure that you close the result sets
when passing them back to the pool (using cursor.flush()).

mxODBC it self does not provide connection pooling, since getting
this right is difficult if you don't know what the application is
doing (e.g. adjusting connection settings or keeping cursors alive).

We do have connection pooling in mxODBC Zope DA and it's working well
for Zope.

Best Regards,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Experts (#1, Nov 12 2017)
>>> Python Projects, Coaching and Consulting ...  http://www.egenix.com/
>>> Python Database Interfaces ...           http://products.egenix.com/
>>> Plone/Zope Database Interfaces ...           http://zope.egenix.com/
________________________________________________________________________

::: We implement business ideas - efficiently in both time and costs :::

   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/
                      http://www.malemburg.com/



More information about the egenix-users mailing list