Try SpotterExecute SQL scripts on a MSSQL database
| "added in version" 4.0.0 of community.general"
Authors: Kris Budde (@kbudde)
Install with ansible-galaxy collection install community.general:==8.5.0
collections:
- name: community.general
version: 8.5.0Execute SQL scripts on a MSSQL database.
- name: Check DB connection
community.general.mssql_script:
login_user: "{{ mssql_login_user }}"
login_password: "{{ mssql_login_password }}"
login_host: "{{ mssql_host }}"
login_port: "{{ mssql_port }}"
db: master
script: "SELECT 1"
- name: Query with parameter
community.general.mssql_script:
login_user: "{{ mssql_login_user }}"
login_password: "{{ mssql_login_password }}"
login_host: "{{ mssql_host }}"
login_port: "{{ mssql_port }}"
script: |
SELECT name, state_desc FROM sys.databases WHERE name = %(dbname)s
params:
dbname: msdb
register: result_params
- assert:
that:
- result_params.query_results[0][0][0][0] == 'msdb'
- result_params.query_results[0][0][0][1] == 'ONLINE'
- name: Query within a transaction
community.general.mssql_script:
login_user: "{{ mssql_login_user }}"
login_password: "{{ mssql_login_password }}"
login_host: "{{ mssql_host }}"
login_port: "{{ mssql_port }}"
script: |
UPDATE sys.SomeTable SET desc = 'some_table_desc' WHERE name = %(dbname)s
UPDATE sys.AnotherTable SET desc = 'another_table_desc' WHERE name = %(dbname)s
transaction: true
params:
dbname: msdb
- name: two batches with default output
community.general.mssql_script:
login_user: "{{ mssql_login_user }}"
login_password: "{{ mssql_login_password }}"
login_host: "{{ mssql_host }}"
login_port: "{{ mssql_port }}"
script: |
SELECT 'Batch 0 - Select 0'
SELECT 'Batch 0 - Select 1'
GO
SELECT 'Batch 1 - Select 0'
register: result_batches
- assert:
that:
- result_batches.query_results | length == 2 # two batch results
- result_batches.query_results[0] | length == 2 # two selects in first batch
- result_batches.query_results[0][0] | length == 1 # one row in first select
- result_batches.query_results[0][0][0] | length == 1 # one column in first row
- result_batches.query_results[0][0][0][0] == 'Batch 0 - Select 0' # each row contains a list of values.
- name: two batches with dict output
community.general.mssql_script:
login_user: "{{ mssql_login_user }}"
login_password: "{{ mssql_login_password }}"
login_host: "{{ mssql_host }}"
login_port: "{{ mssql_port }}"
output: dict
script: |
SELECT 'Batch 0 - Select 0' as b0s0
SELECT 'Batch 0 - Select 1' as b0s1
GO
SELECT 'Batch 1 - Select 0' as b1s0
register: result_batches_dict
- assert:
that:
- result_batches_dict.query_results_dict | length == 2 # two batch results
- result_batches_dict.query_results_dict[0] | length == 2 # two selects in first batch
- result_batches_dict.query_results_dict[0][0] | length == 1 # one row in first select
- result_batches_dict.query_results_dict[0][0][0]['b0s0'] == 'Batch 0 - Select 0' # column 'b0s0' of first row
name:
aliases:
- db
default: ''
description: Database to run script against.
type: str
output:
choices:
- dict
- default
default: default
description:
- With V(default) each row will be returned as a list of values. See RV(query_results).
- Output format V(dict) will return dictionary with the column names as keys. See
RV(query_results_dict).
- V(dict) requires named columns to be returned by each query otherwise an error is
thrown.
type: str
params:
description: 'Parameters passed to the script as SQL parameters.
(Query V(''SELECT %(name\)s"'') with V(example: ''{"name": "John Doe"}).)''
'
type: dict
script:
description:
- The SQL script to be executed.
- Script can contain multiple SQL statements. Multiple Batches can be separated by
V(GO) command.
- Each batch must return at least one result set.
required: true
type: str
login_host:
description: Host running the database.
required: true
type: str
login_port:
default: 1433
description: Port of the MSSQL server. Requires O(login_host) be defined as well.
type: int
login_user:
description: The username used to authenticate with.
type: str
transaction:
default: false
description:
- If transactional mode is requested, start a transaction and commit the change only
if the script succeed. Otherwise, rollback the transaction.
- If transactional mode is not requested (default), automatically commit the change.
type: bool
version_added: 8.4.0
version_added_collection: community.general
login_password:
description: The password used to authenticate with.
type: str
query_results:
contains:
queries:
contains:
rows:
contains:
column_value:
description:
- List of column values.
- Any non-standard JSON type is converted to string.
example:
- Batch 0 - Select 0
returned: success, if output is default
type: list
description: List of rows returned by query.
elements: list
type: list
description:
- List of result sets of each query.
- If a query returns no results, the results of this and all the following queries
will not be included in the output.
- Use the V(GO) keyword in O(script) to separate queries.
elements: list
type: list
description: List of batches (queries separated by V(GO) keyword).
elements: list
returned: success and O(output=default)
sample:
- - - - Batch 0 - Select 0
- - - Batch 0 - Select 1
- - - - Batch 1 - Select 0
type: list
query_results_dict:
contains:
queries:
contains:
rows:
contains:
column_dict:
description:
- Dictionary of column names and values.
- Any non-standard JSON type is converted to string.
example:
col_name: Batch 0 - Select 0
returned: success, if output is dict
type: dict
description: List of rows returned by query.
elements: list
type: list
description:
- List of result sets of each query.
- If a query returns no results, the results of this and all the following queries
will not be included in the output. Use 'GO' keyword to separate queries.
elements: list
type: list
description: List of batches (queries separated by V(GO) keyword).
elements: list
returned: success and O(output=dict)
sample:
- - - - Batch 0 - Select 0
- - - Batch 0 - Select 1
- - - - Batch 1 - Select 0
type: list