community.general.odbc – Execute SQL via ODBC

Note

This plugin is part of the community.general collection (version 4.2.0).

You might already have this collection installed if you are using the ansible package. It is not included in ansible-core. To check whether it is installed, run ansible-galaxy collection list.

To install it, use: ansible-galaxy collection install community.general.

To use it in a playbook, specify: community.general.odbc.

New in version 1.0.0: of community.general

Synopsis

  • Read/Write info via ODBC drivers.

Requirements

The below requirements are needed on the host that executes this module.

  • python >= 2.6

  • pyodbc

Parameters

Parameter Choices/Defaults Comments
commit
boolean
added in 1.3.0 of community.general
    Choices:
  • no
  • yes ←
Perform a commit after the execution of the SQL query.
Some databases allow a commit after a select whereas others raise an exception.
Default is true to support legacy module behavior.
dsn
string / required
The connection string passed into ODBC.
params
list / elements=string
Parameters to pass to the SQL query.
query
string / required
The SQL query to perform.

Notes

Note

  • Like the command module, this module always returns changed = yes whether or not the query would change the database.

  • To alter this behavior you can use changed_when: [yes or no].

  • For details about return values (description and row_count) see https://github.com/mkleehammer/pyodbc/wiki/Cursor.

Examples

- name: Set some values in the test db
  community.general.odbc:
    dsn: "DRIVER={ODBC Driver 13 for SQL Server};Server=db.ansible.com;Database=my_db;UID=admin;PWD=password;"
    query: "Select * from table_a where column1 = ?"
    params:
      - "value1"
    commit: false
  changed_when: no

Return Values

Common return values are documented here, the following are the fields unique to this module:

Key Returned Description
description
list / elements=dictionary
success
List of dicts about the columns selected from the cursors, likely empty for DDL statements. See notes.

results
list / elements=list
success
List of lists of strings containing selected rows, likely empty for DDL statements.

row_count
string
success
The number of rows selected or modified according to the cursor defaults to -1. See notes.



Authors

  • John Westcott IV (@john-westcott-iv)