User Community Service Desk Downloads
If you can't find the product or version you're looking for, visit support.ataccama.com/downloads

Read SQL Result

Read SQL Result icon

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.

We do not recommend putting comments and other unnecessary parts in the SQL query since it can become unparsable for JDBC drivers (they might not support these items).

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 1 (that is, the index of the first available column is 1).

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

Sample
<!-- 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?