search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.88k stars 561 forks source link

pyodbc uses odbc 3.x features instead of 3.8 #1277

Closed jabbera closed 8 months ago

jabbera commented 9 months ago

When connection pooling is on in unixodbc there is a connection reset procedure that needs to be run: https://github.com/lurcher/unixODBC/blob/9b31cd228d01e768a7ad9126d5da8860f1ed72a9/DriverManager/SQLConnect.c#L3904 Not running this causes connections in the pool to be left tainted will all sorts of badness such as the following not being done:

It resets all error states and numbers (like @@error)
It stops all EC's (execution contexts) that are child threads of a parent EC executing a parallel query
It will wait for any outstanding I/O operations that is outstanding
It will free any held buffers on the server by the connection
It will unlock any buffer resources that are used by the connection
It will release all memory allocated owned by the connection
It will clear any work or temporary tables that are created by the connection
It will kill all global cursors owned by the connection
It will close any open SQL-XML handles that are open
It will delete any open SQL-XML related work tables
It will close all system tables
It will close all user tables
It will drop all temporary objects
It will abort open transactions
It will defect from a distributed transaction when enlisted
It will decrement the reference count for users in current database; which release shared database lock
It will free acquired locks
It will releases any handles that may have been acquired
It will reset all SET options to the default values
It will reset the @@rowcount value
It will reset the @@identity value
It will reset any session level trace options using dbcc traceon()

This feature is only enabled if the environment is set to 3.8 or higher. In pyodbc it's hard coded to 3.X which doesn't include this vital behavior. https://github.com/mkleehammer/pyodbc/blob/ee0ad21e7ab00c4ab974f332ab0ce22bb7ad776d/src/pyodbcmodule.cpp#L299

I'm happy to author a PR to enable this if it's something you would be open to.

v-chojas commented 9 months ago

Not all drivers support ODBC 3.8, but it does seem like a good idea to make this configurable.

jabbera commented 9 months ago

I think each connection can have its own environment, which means its own odbc version. That seems like a major change versus allowing the default to be optionally 3.8. Considering 3.8 came out in 2009 all or nothing might be fine.