community.general.hana_query module – Execute SQL on HANA

Note

This module is part of the community.general collection (version 4.8.3).

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.hana_query.

New in version 3.2.0: of community.general

Synopsis

  • This module executes SQL statements on HANA with hdbsql.

Parameters

Parameter

Comments

autocommit

boolean

Autocommit the statement.

Choices:

  • no

  • yes ← (default)

database

string

Define the database on which to connect.

encrypted

boolean

Use encrypted connection. Defaults to false.

Choices:

  • no ← (default)

  • yes

filepath

list / elements=path

One or more files each containing one SQL query to run.

Must be a string or list containing strings.

host

string

The Host IP address. The port can be defined as well.

instance

string / required

The instance number.

password

string

The password to connect to the database.

Note: Since the passwords have to be passed as command line arguments, userstore=true should be used whenever possible, as command line arguments can be seen by other users on the same machine.

query

list / elements=string

SQL query to run.

Must be a string or list containing strings. Please note that if you supply a string, it will be split by commas (,) to a list. It is better to supply a one-element list instead to avoid mangled input.

sid

string / required

The system ID.

user

string

A dedicated username. The user could be also in hdbuserstore. Defaults to SYSTEM.

Default: “SYSTEM”

userstore

boolean

added in 3.5.0 of community.general

If true the user must be in hdbuserstore.

Choices:

  • no ← (default)

  • yes

Notes

Note

  • Does not support check_mode.

Examples

- name: Simple select query
  community.general.hana_query:
    sid: "hdb"
    instance: "01"
    password: "Test123"
    query: "select user_name from users"

- name: Run several queries
  community.general.hana_query:
    sid: "hdb"
    instance: "01"
    password: "Test123"
    query:
    - "select user_name from users;"
    - select * from SYSTEM;
    host: "localhost"
    autocommit: False

- name: Run several queries from file
  community.general.hana_query:
    sid: "hdb"
    instance: "01"
    password: "Test123"
    filepath:
    - /tmp/HANA_CPU_UtilizationPerCore_2.00.020+.txt
    - /tmp/HANA.txt
    host: "localhost"

- name: Run several queries from user store
  community.general.hana_query:
    sid: "hdb"
    instance: "01"
    user: hdbstoreuser
    userstore: true
    query:
    - "select user_name from users;"
    - select * from users;
    autocommit: False

Return Values

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

Key

Description

query_result

list / elements=list

List containing results of all queries executed (one sublist for every query).

Returned: on success

Sample: [[{“Column”: “Value1”}, {“Column”: “Value2”}], [{“Column”: “Value1”}, {“Column”: “Value2”}]]

Authors

  • Rainer Leber (@rainerleber)