SQL Task

Prerequisites

Before you begin, you must complete the following tasks:

Create a database profile with capability specified as RELATIONAL. See Create a Database Profile.
Decide if you want the SQL Task to participate in a global XA transaction within MicroTx Workflows. In such scenarios, MicroTx Workflows starts the XA transaction. SQL Tasks automatically join the active global transaction when it runs and executes its SQL without committing locally. Within an XA transaction, MicroTx Workflows commits multiple SQL operations and other XA actions in case of success. In case of any failure, MicroTx Workflows rolls back all the actions. This ensures that SQL operations remain consistent with other tasks in a workflow, such as microservice calls or other database updates and succeed or fail together as a single, atomic unit.
- To use a SQL task in an XA Transaction you need to use it between a transaction task with action set to BEGIN and another transaction task with action set to COMMIT. The Transaction task with action set to BEGIN starts the global transaction. See Transaction Task. When used between BEGIN and COMMIT Transaction tasks, the SQL task participates in workflow-defined transaction boundaries instead of controlling its own transactions.
- Enter the URL of the participant application in the conductor.tasks.microtx.participantCallbackUrl property in the Helm chart. This is the URL that the MicroTx Distributed Transaction coordinator uses to communicate with MicroTx Workflows.

Supported Data Sources for SQL Task

You can execute SQL stored procedures that are saved in any Oracle Database and PostgreSQL database to which you can connect using a JDBC connection string.

If you want the SQL Task to participate in a global XA transaction, this is supported only when using an Oracle Database.

Supported SQL Data Types

Variables defined in SQL stored procedures are exposed as parameters in the SQL tasks that you create or run.

The following SQL data types are supported for input and output parameters when you use stored procedures in SQL tasks:

VARCHAR
NUMERIC
BOOLEAN

To add a SQL Task

Navigate to the Task tab in a workflow and view all the tasks that you can add using the Workflow Builder. See Access the Task Tab in Workflow Builder.
In the More Tasks dialog box, click SQL Task to add it to the workflow.
Click the task that you have added in the left pane. The Task tab in the right pane displays details about the task, such as its name and parameters. Next, let's provide details for the task.
In the Task Details group, enter the following information.
- Task Name: Mandatory. Enter a unique name for the task. The name must be between 1 to 128 alphanumeric characters in length and cannot contain spaces or any special characters. Optionally, you can use underscore (_) and hyphen (-).
- Task Reference: Mandatory. Enter a value to refer to the task within a workflow definition. This value must be unique within a workflow. The task reference value must be between 1 to 128 alphanumeric characters in length and cannot contain spaces or any special characters. Optionally, you can use underscore (_) and hyphen (-).
In the SQL Task Parameters group, provide the following information.
- Database Profile: Mandatory. Select the relational database profile which contains the database connection parameters.
- SQL Type: Mandatory. Select one of the following type of SQL statements that you want to run.
  - SELECT: Retrieves data from the database.
  - UPDATE: Adds new data and modifies existing data in the database.
  - DELETE: Removes existing data from the database.
  - PLSQL: Helps you create and perform SQL operations from PL/SQL in the SQL task.
- SQL Statement: Enter the SQL statements that you want to execute every time this task runs. Refer to the Usage Notes section for more details.
- SQL Parameters or PLSQL Parameters: Optional. Specify the parameters that will replace any wildcards in the SQL statement that you provide.
- Enlist in transaction: Optional. You must select this option only if you want to enlist the SQL task as a participant service in a global XA transaction. This is disabled by default.
- Enable Idempotency: Optional. Enable this option when you want the SQL task to avoid duplicate execution for the same workflow idempotency key and task idempotency key. If you enable idempotency, you may also provide values for the following fields:
  - Idempotency Table Name: Optional. Enter the table name used to store task idempotency lock information. If you do not provide a value, MicroTx Workflows uses fenced_task_idempotency_lock.
  - Idempotency Timeout: Optional. Specifies how long MicroTx Workflows holds the task-level idempotency lock for this task. The lock prevents duplicate concurrent execution during retries or replays. The default is 600000 milliseconds, or 10 minutes.
Click Save to save the changes to the workflow.

MicroTx Workflows displays the changes in JSON code.
Review all the changes, and then click Confirm Save to save the changes.

If you do not want to save the changes, click Cancel, and then click Reset to undo all the changes that you have made since the workflow was last saved.

Usage Notes

Consider the following points when you enter the SQL statements that you want to execute every time the SQL task runs.

The SQL statements must be idempotent.
Multiple SQL statements can be delimited by ;
If you want to enter a SELECT statement, then you can enter only a single statement.
You can use the wild card ? in the query.

Example

When you enter information in the Task tab, the corresponding code of the task is updated in the JSON tab. The following example displays the JSON code for a SQL task with sample values.

{
    "name": "oracleTask",
    "taskReferenceName": "oracle_refer",
    "inputParameters": {
        "databaseProfile": "MyDatabaseProfile",
        "sqlStatement": "insert into accounts (account_id,name,amount) values (?,?,?)",
        "type": "UPDATE",
        "parameters": [
            "${workflow.input.oracle.accountName}",
            "${workflow.input.oracle.accountName}",
            "${workflow.input.oracle.accountBalance}"
        ]
    },
    "type": "SQL"
}

3.2.11 SQL Task