Executes a defined SQL command/procedure call over the input data and writes its output values. This step performs some write-like SQL commands for each input row. It provides attributes to affect batch size and commit size.

Usage notes

• This step is suitable for write-like operations only (must NOT return a result set). When there is a result set as the result of query execution, an error is thrown. This is considered a configuration problem that should be be fixed. For read-like operations use the SQL Select step.
• Because it is not possible now to analyze a SQL command without being run, some problems are reported after the first result set is obtained (validation of the result of the SQL query). Note that when an error is thrown at this moment, then the state is as follows: 1) possible beforeScript is executed and commited 2) statement for the first row is commited only if autoCommit is true 3) afterScript is not executed.
• The step requires one input and has an optional output. If no output is specified the SQL command is performed without writing data to the output.

Parameter processing

The SQL command uses a special form for parameter definition. The general syntax is:

$[direction]{columnName}

where direction is one of following:

• empty string - defines the INPUT parameter
• '+' - defines the OUTPUT parameter
• '*' - defines the INOUT parameter (of the procedure)

The name of an input column must be given as columnName, otherwise the step will report an error.

Since '$' has special meaning here, care must be taken when using this character. If it is required to use '$' in its original meaning, it must be "escaped" by doubling its value (putting $$ into the SQL command). Note that this doubling must be done in the whole SQL query (in strings, comments etc.), otherwise the SQL query compilation will fail due to missing or invalid parameter definitions, etc.

Note: It is not recommended to put comments and other unnecessary parts in the SQL command since it may become unparsable for JDBC drivers (they may not support these items).Parameter types