Add support for Transactions with multiple SQL statements in JDBC plugins (by adding a new Queries task)

[paulgrainger85]

Feature description

Only tested on MySQL/MariaDB but may apply to other JDBC plugins.

It is not possible to execute a set of statements in the Query tasks.

e.g. with MySQL/Maria DB the following fails

  - id: sql
    type: io.kestra.plugin.jdbc.mysql.Query
    url: jdbc:mysql://docker.for.mac.localhost:3306/mydb
    username: root
    password: my-secret-pw
    sql: |
      insert into test(col1, col2) values (20, 'abc');
      insert into test(col1, col2) values (3, 'def');

As a workaround we can do the following, but would be better if either

• there was a separate task to do this in JDBC
• or the Query task was supplement to support multiple statements

  - id: sql_bulk
    type: io.kestra.plugin.docker.Run
    containerImage: mariadb:latest
    inputFiles:
      maria.sql: "{{inputs.sql}}"
    entryPoint:
      - /bin/bash
      - -c
    commands:
      - "mariadb --host=docker.for.mac.localhost --user=root --database=mydb --password=my-secret-pw --port=3306 < {{workingDir}}/maria.sql"

[anna-geller]

Your intuition is correct; it is not tied to MySQL. All JDBC plugins support only a single SQL statement in the sql parameter (TTBOMK).

We'll work on a new task (e.g. Queries) to support multiple SQL statements

[anna-geller]

might be a good opportunity to also address https://github.com/kestra-io/plugin-jdbc/issues/100

[anna-geller]

will be addressed in https://github.com/kestra-io/plugin-jdbc/issues/368