SQLSetConnectOption Option '102' SQL_AUTOCOMMIT failed

[coreybrett]

• Python: 3.7 32bit
• pyodbc: 4.0.32
• OS: Win 10 x64
• DB: really old school cobal thing
• driver: [Transoft][TSODBC][usqlsd]

Issue

When attempting the following...

cnxn = pyodbc.connect(
    "DSN=nds.udd;UID=xxx;PWD=xxx;", readonly=True, autocommit=False
)

print(cnxn)

I get...

(venv) PS C:\Users\boylec\Documents\pynds> python.exe .\test.py
Traceback (most recent call last):
  File ".\test.py", line 5, in <module>
    "DSN=nds.udd;UID=xxx;PWD=xxx;", readonly=True, autocommit=False
pyodbc.Error: ('01S02', "[01S02] [Transoft][TSODBC][usqlsd]SQLSetConnectOption Option '102' SQL_AUTOCOMMIT failed  (0) (SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT))")
(venv) PS C:\Users\boylec\Documents\pynds>

How would I get past this?

[v-chojas]

Does your driver not support manual commit ? Here is where the pyODBC attempts to turn off autocommit (admittedly, the == false is a little odd) if you specify so:

https://github.com/mkleehammer/pyodbc/src/connection.cpp#L381

Try autocommit=True , which will prevent pyODBC from attempting to change the commit option upon connect, since ODBC drivers default to autocommit.

[v-chojas]

Here: https://github.com/mkleehammer/pyodbc/blob/master/src/connection.cpp#L381

[coreybrett]

Awesome! That fixed it.

The following works perfect now.

Thank you!!!

import pyodbc
from icecream import ic

cnxn = pyodbc.connect(
    "DSN=nds.udd;UID=xxx;PWD=xxx;", readonly=True, autocommit=True
)

cursor = cnxn.cursor()

q = r"""SELECT SA_HEADER.VH_WORKORDER, SA_HEADER.VH1_STATUS
FROM root.SA_HEADER SA_HEADER
WHERE (SA_HEADER.VH_WORKORDER Like 'A%') AND (SA_HEADER.VH1_STATUS='O')"""

cursor.execute(q)

rows = cursor.fetchall()

ic(rows)