Read SQL Result
Reads data provided by a query to the variables defined by the mapping.
If the query returns no result set or the result set contains no rows, then the task fails. Otherwise, the first row of the returned result set is mapped to the variables using defined mappings.
Therefore we recommend specifying the query so that it returns only one specific row or using an ORDER BY clause in order to retrieve rows in the intended order.
Properties
| Name | Type | Description | Expression support | ||
|---|---|---|---|---|---|
Query |
mandatory |
Query to be executed to get the data; supports variable mappings.
|
semi-expression |
||
Connection Name |
mandatory |
Name of the database connection. |
semi-expression |
||
Mapping |
mandatory |
Set of mappings describing the mapping between the columns in the result set and the variables. Individual result rows are mapped to the defined mappings in the order they are defined. |
none |
Mapping properties
| Mapping properties must be defined exclusively: either Output Parameters or Result Set can be defined. |
| Name | Type | Description | Expression support |
|---|---|---|---|
Output Parameters |
optional |
Defines a set of the output parameters to be read from the query. Applicable when calling stored procedures. |
none |
Result Set |
optional |
Defines a set of the output parameters to be read from the query’s result set. Applicable when running SQL queries. |
none |
Output parameters properties
| Name | Type | Description | Expression support |
|---|---|---|---|
Name |
mandatory |
Defines the name of the mapping. The value read from the mapping is available under this name in the condition and saved in the task variables. For more information about using variables in workflows, see Workflow Variables. |
none |
Type |
mandatory |
Defines how to read the value from the statement. Currently supported types are: STRING, INTEGER, LONG, DAY, DATETIME, BOOLEAN. |
none |
Result set properties
| The value is read from the first row of the first available result set. |
| Name | Type | Description | Expression support |
|---|---|---|---|
Name |
mandatory |
Defines the name of the mapping. The value read from the mapping is available under this name in the condition and saved in the task variables. For more information about using variables in workflows, see Workflow Variables. |
none |
Index |
mandatory |
Defines the index of the value in the result set.
Indexed from |
none |
Predefined variables
-
ERROR_CODE - The variable is set if the task processing fails. The value of the variable represents the following errors:
-
-1: Defined query returns no result set.
-
-2: Returned result contains no data.
-
-3: Inconsistent mapping, there are more mappings defined than the columns in the result set.
-
-4: SQL error occurred during processing.
-
Example
<!-- calling a stored procedure -->
<executable class="com.ataccama.adt.task.exec.EwfReadSqlResultTask">
<query>call PROC3($+{op1})</query>
<connectionName>derbyDb</connectionName>
<mapping>
<outputParameters>
<parameter name="op1" type="STRING"/>
</outputParameters>
</mapping>
</executable>
<!-- running SQL query -->
<executable class="com.ataccama.adt.task.exec.EwfReadSqlResultTask">
<query>select * from $#{tableName} where $#{condColName} = ${condColValue}</query>
<connectionName>derbyDb</connectionName>
<mapping>
<resultSet>
<value name="col1" index="1"/>
<value name="col3" index="3"/>
</resultSet>
</mapping>
</executable>For details about variable mapping, see Workflow Variables.
Was this page useful?