[egenix-users] No commit in connection context mgr

[M.-A. Lemburg]

Hi Jan,

we have debugged the problem and indeed found an issue with
the way the .__exit__() methods of connections and cursors
were implemented. This resulted in exceptions unintentionally
being silenced and even tricked our tests into appearing to
work fine.

We will issue a patch level release to address this in mxODBC.
An update for mxODBC Connect will follow as well.

Thanks again for reporting the problem.

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

Professional Python Services directly from the Experts (#1, Nov 14 2016)
>>> 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/


On 11.11.2016 10:48, M.-A. Lemburg wrote:
> Hi Jan,
> 
> On 08.11.2016 17:09, Jan Murre wrote:
>> Hi,
>>
>> I am using the following code with a MS SQL database and the Microsoft
>> driver:
>>
>> -----------
>> con = Connect(dsn, user=user,
>>     password=password, clear_auto_commit=1)
>>
>> with con:
>>     with con.cursor() as cursor:
>>         cursor.callproc('proc_AddReservation_web', (12, None, 'nl-nl'))
>>
>>         result = []
>>         while True:
>>             result.append(cursor.fetchall())
>>             if not cursor.nextset():
>>                 break
>> -----------
>>
>> Based on the documentation I would have expected a commit inside the
>> context manager.
> 
> The connection con will get committed if the outer block
> is left without exception (this is how connections work as
> context managers).
> 
> The inner block will only close the cursor, not commit
> the transaction (cursors as connection managers only
> close the cursor).
> 
>> When I use a conn.commit() or even a cursor.close() in the inner with block
>> (the cursor context manager), the transactions gets committed.
>>
>> Setting clear_auto_commit=0 results in the following exception:
>>
>> "mx.ODBC.Error.NotSupportedError: connection has AUTOCOMMIT turned on"
>> So, it seems one of the context mgr. does try to commit the transaction.
>>
>>
>>
>> Furthermore, I want to add a try except block around the two context mgrs
>> to catch/log/reraise exceptions.
>> However, it seems that the context managers effectively swallow exceptions
>> and do not reraise them on __exit__.
> 
> When using cursors and connections as context managers, they
> both will return False in case of an error. This should result in
> Python reraising the exception.
> 
> However, looking at the code, you may have uncovered a bug
> here. We'll have a closer look and report back.
> 
> Thanks for reporting.
> 
> Best Regards,
>