odbc: missing pyodbc.connect autocommit parameter

[joaoolavobv]

Summary

I'm using odbc community module to connect to a databricks base:

  - name: Get some values in the test db
    community.general.odbc:
      dsn: "Driver=/opt/simba/spark/lib/64/libsparkodbc_sb64.so;Host=xxx.azuredatabricks.net;Port=443;HTTPPath=sql/1.0/warehouses/xxx; SSL=1;ThriftTransport=2;AuthMech=3;UID=token;PWD=xxx"
      query: "select name from xxx limit 5"
      commit: false
    changed_when: false

But got the error bellow:

fatal: [localhost]: FAILED! => {"changed": false, "msg": "Failed to connect to DSN: ('HYC00', '[HYC00] [Simba][ODBC] (11470) Transactions are not supported. (11470) (SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT))')"}

Changing the line bellow (#119) on file .../ansible_collections/community/general/plugins/modules/odbc.py makes it work:

• from: connection = pyodbc.connect(dsn)
• to: connection = pyodbc.connect(dsn, autocommit=True)

But it does not work when setting autocommit=False (got the same error above). This is compatible with using pyodbc directly on python interpreter.

Also, if I let autocommit=True and commit: true, got the error bellow:

fatal: [localhost]: FAILED! => {"changed": false, "msg": "Exception while reading rows: ('HY010', '[HY010] [unixODBC][Driver Manager]Function sequence error (0) (SQLFetch)')"}

The error above is similar to issue #1137.

In my case, I won't do any SQL query that needs a commit. But it seems a bit strange to have to set autocommit=True and commit: false in order to get thing working.

Please, let me know if you need more info.

Issue Type

Bug Report

Component Name

odbc

Ansible Version

$ ansible --version
ansible [core 2.14.14]
  config file = ~/ansible/ansible.cfg
  configured module search path = ['~/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.9/site-packages/ansible
  ansible collection location = ~/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/bin/ansible
  python version = 3.9.18 (main, Jan 24 2024, 00:00:00) [GCC 11.4.1 20231218 (Red Hat 11.4.1-3)] (/usr/bin/python3)
  jinja version = 3.1.2
  libyaml = True

Community.general Version

$ ansible-galaxy collection list community.general
Collection        Version
----------------- -------
community.general 6.6.2

Configuration

$ ansible-config dump --only-changed
CONFIG_FILE() = ~/ansible/ansible.cfg
DEFAULT_PRIVATE_KEY_FILE(~/ansible/ansible.cfg) =~/xxx.pem
DEFAULT_REMOTE_USER(~/ansible/ansible.cfg) = xxx

OS / Environment

Red Hat Enterprise Linux release 9.4 (Plow)

Steps to Reproduce

  - name: Get some values in the test db
    community.general.odbc:
      dsn: "Driver=/opt/simba/spark/lib/64/libsparkodbc_sb64.so;Host=xxx.azuredatabricks.net;Port=443;HTTPPath=sql/1.0/warehouses/xxx; SSL=1;ThriftTransport=2;AuthMech=3;UID=token;PWD=xxx"
      query: "select name from xxx limit 5"
      commit: false
    changed_when: false

Expected Results

ok: [localhost] => {"changed": false, "description": [{"display_size": null, "internal_size": 255, "name": "name", "nullable": true, "precision": 255, "scale": 0, "type": "str"}], "results": [["xxx"], ["xxx"], ["xxx"], ["xxx"], ["xxx"]], "row_count": -1}

Actual Results

fatal: [localhost]: FAILED! => {"changed": false, "msg": "Failed to connect to DSN: ('HYC00', '[HYC00] [Simba][ODBC] (11470) Transactions are not supported. (11470) (SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT))')"}

Code of Conduct

• [X] I agree to follow the Ansible Code of Conduct