SQL Operation

RdbmsAction32x32

Description

The SQL Operation action provides several functions:

  • Executes standalone DDL or DML SQL statements.

  • Executes SELECT statements, and sends the result through a bind link.

  • Executes INSERT, UPDATE or DELETE statements from results sent through a bind link.

  • Wait for a query to return a number of records.

When executing statements, the SQL code must be entered in the action’s Expression Editor.

Parameters

Name Mandatory Default Description

SQL Action Type

Yes

DDL_DML

Type of SQL Command. Possible types are:

  • DDL_DML for standalone SQL statements

  • SELECT to use as the source of a bind link

  • INSERT, UPDATE, DELETE to use as the target of a bind link

SQL Connection

No

SQL connection used for the command. If you link a metadata object to this action, the action inherits the connection information.

SQL Transaction Type

No

AUTOCOMMIT

Indicates the type of transaction. Possible types are:

AUTOCOMMIT

An autocommit connection is picked up from the connection pool.

COMMIT

A commit is issued after the SQL command.

NOCOMMIT

No commit is issued after the SQL command.

SQL Transaction Name

No

T1

A name given to the SQL transaction. Giving multiple actions the same SQL transaction name groups them in the same transaction.

SQL Transaction Commit Nb

No

–1

Number of lines after which a commit must be issued. This parameter works for INSERT, UPDATE and DELETE commands.

–1 stands for no commit.

SQL Transaction Isolation

No

Transaction isolation type. Possible types are:

  • TRANSACTION_NONE

  • TRANSACTION_READ_COMMITTED

  • TRANSACTION_READ_UNCOMMITTED

  • TRANSACTION_REPEATABLE_READ

  • TRANSACTION_SERIALIZABLE.

An empty value defaults to the database’s internal configuration.

SQL Action Type Stat

No

Name suffix for the action’s statistics variable.

The action publishes a statistics variable that returns the number of lines handled by the command. Filling out this field gives the variable a name prefixed with SQL_STAT_, and ending with the field contents. For example, if you enter INSERTVAR in this field, the action publishes the SQL_STAT_INSERTVAR variable.

SQL Fetch Size

No

1000

Number of lines to fetch for SELECT commands. By default, the action uses the runtime engine’s setting.

SQL Batch Size

No

1000

Number of lines to process in a batch for INSERT, UPDATE and DELETE commands. By default, the action uses the runtime engine’s setting.

SQL Wait Data

No

false

Moves the command into data wait mode.

The action waits until the query specified in the action code or in SQL Wait Count Select returns at least the value from SQL Wait Nb Rows, or until the value from SQL Wait Timeout is reached.

SQL Wait Poll interval

No

1000

Polling interval when SQL Wait Data is set to true.

SQL Wait Timeout

No

Wait timeout when SQL Wait Data is set to true.

SQL Wait Nb Rows

No

Number of rows to wait for when SQL Wait Data is set to true.

SQL Wait Count Select

No

Query that returns a number of rows to wait for, when SQL Wait Data is set to true.

SQL Multi Queries

No

false

Activates multi-query mode.

In multi-query mode, your SQL code can contain several SQL statements separated by the SQL Multi Queries Separator.

SQL Multi Queries Separator

No

;

Character that separates SQL statements when multi-query mode is on.

SQL Multi Queries Exclusion Patterns

No

Regular expression that defines which lines to exclude from the list of statements when multi-query mode is on. These lines are typically SQL comments.

SQL Disable Nb Rows Statistic

No

false

Disables the calculation of statistics when in multi-query mode. Possible values are:

  • true: disables statistics for all queries.

  • false: does not disable any statistics.

  • a specific query list to not calculate, with the query numbers separated by commas or semicolons.

An AUTONOMOUS SQL transaction type was available in older versions of Semarchy xDI. It was removed in version 2024.1.0 LTS.

If you import earlier projects that are still configured to use the AUTONOMOUS transaction type, xDI interprets those transactions as AUTOCOMMIT instead.

Parameter guides

Using the SQL Action Type parameter

You can use the SQL Operation process action in different ways. For example, you can execute standalone queries, or use it to pass data through bind links.

This article explains more about how the SQL Operation action’s different action types in the SQL Action Type parameter.

DDL_DML

The DDL_DML action type executes a standalone query. It does not pass the result to another action, so use this action type if you do not plan on using a bind link.

DDL DML

SELECT

The SELECT option turns the action into a bind source. It executes a SELECT query (set in the action’s Expression Editor) and passes the result to another action over a bind link.

In the following example, the SELECT action is the bind source. The result goes to a DELETE action, which deletes the rows returned by SELECT.

selectBind

UPDATE, INSERT, DELETE

The UPDATE, INSERT and DELETE options turn the action into a bind target. SQL actions with this action type use the result of a bind link in the action query. For instance, you can use a SELECT action to pass a result, and act on that result.

The syntax to reference the source in the Expression Editor is :{column}:.

The following example shows the setup from the previous example. The expression uses the CUS_ID variable returned by the SELECT query, by referencing :{CUS_ID}:.

deleteBind
UPDATE, INSERT and DELETE actions execute for all input rows they take. Make sure your SELECT statement only returns the necessary rows to not lose data.

Using transactions

A database transaction is a single unit of work, treated in a coherent and reliable way independent of other transactions. They are useful to implement ACID, and can also be useful to roll back changes in case of errors.

To use transactions with an SQL database, configure specific parameters of the SQL Operation action. The most important parameters are:

Name Default Description

SQL Transaction Type

AUTOCOMMIT

Indicates the type of transaction. Available types are:

AUTOCOMMIT

An autocommit connection is picked up from the connection pool.

COMMIT

A commit is issued after the SQL command.

NOCOMMIT

No commit is issued after the SQL command.

SQL Transaction Name

T1

A name given to the SQL transaction. Giving multiple actions the same SQL transaction name lets them participate in the same transaction.

To perform multiple operations in the same transaction, give each operation the same SQL Transaction Name.

You can also set the SQL Transaction Commit Nb to set the number of lines after which a commit must be issued, and SQL Transaction Isolation to set transaction isolation.

Example

This example demonstrates an integration flow which performs some operations, rolls back the transation in case of errors, and otherwise commits changes.

Overview

All actions use the same transaction name T1. The first three actions use the NOCOMMIT transaction type, while the last one uses the COMMIT type. This layout executes all actions with the same JDBC connection, but does not commit changes except at the end.

  • If everything works during the process, the last action executes, and changes are committed.

  • If an error occurs, the process ends without reaching the COMMIT action, and nothing is committed. The runtime automatically performs a rollback. As all other actions have a NOCOMMIT type, the rollback affects all actions.

Here is an execution example:

error

In this screenshot, one of the actions failed. The runtime executes a rollback, and the operations from the two successful actions are discarded.