ONE Desktop ONE DQG ONE MDM ONE RDM ONE Runtime Server
latest (15.1.0) 14.5.x 13.9.x 13.8.x 13.7.x 13.6.x 13.5.0 13.4.x 13.3.x 13.2.x
User Community Service Desk Downloads
If you can't find the product or version you're looking for, visit support.ataccama.com/downloads

Wait For SQL Value

Wait for SQL Value icon

Waits (at the maximum given amount of time) until the specified condition is evaluated to true.

If the query passed to the task does not return any result (that is, it does not return any result set containing rows and it does not contain any output parameters), the task fails immediately. If the query returns a result set, only the first row is taken into account: 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

Polling Interval (ms)

mandatory

The interval to wait between two checks of the condition. A value higher than zero is required.

expression evaluating to integer

Timeout (ms)

mandatory

The maximum amount of time the task waits for the condition to be true. After this time, the task fails (that is, it does not wait anymore even if the condition is only being checked). A value higher than zero is required.

expression evaluating to integer

Query

mandatory

Defines the SQL query to execute. The query uses the following syntax for passing parameters:

  • ${name} - Variable mapping, the value of the variable name is passed to the query.

  • $+{name} - Output parameter mapping, the output variable name is registered to the SQL statement at this position. The mapping property must contain Output Parameters element with this name (it specifies the data type of the output parameter).

  • $#{name} - String replace, the value of the variable name is placed in the SQL query at the given position.

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

semi-expression

Mapping

mandatory

Defines how to map the Output Parameters and values read from the Result Set to the variables. See the example for syntax.

Each Mapping must have a name which is used either in the Condition or later for setting the value to the task variable.

none

Condition

mandatory

Condition determining whether the task can quit. It must evaluate to a Boolean value: if the result is true, the task quits and the mapped output values are stored to the task variables. If the condition’s result is false, the task continues to wait or check the conditions.

All parameter names used in Condition must refer to the names defined in the Mapping property.

expression

Connection Name

mandatory

Name of the database connection.

semi-expression

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 will be available under this name in the condition and saved in the task variables.

none

Type

mandatory

Defines how to read the value from the statement. Supported types are STRING, INTEGER, LONG, DAY, DATETIME, BOOLEAN.

none

Result set properties

Name Type Description Expression support

Name

mandatory

Defines the name of the mapping. The value read from the mapping will be available under this name in the condition and saved in the task variables.

none

Index

mandatory

Defines the index of the value in the result set. Indexed from 1 (the index of the first available column is 1).

none
The value is read from the first row of the first available result set.

Take note of the following:

  • Values read from the query are not stored to the task variables until the condition is evaluated as true (however, they are still available in the condition).

  • All objects read from mappings are converted to strings before storing on context (evaluation in conditions, storing to task variables) because the workflow does not support data types. When using mapped values in a condition, re-typing to the desired type is required (for example, toInteger). When deserializing DATE values, use the default US Date format yyyy-MM-dd HH:mm:ss.

  • Since values from the mappings are mapped as global variables in the Condition property, the mapping with the same name as a global variable overrides this global variable’s value in the Condition evaluation.

For details about variable mapping, see Workflow Variables.

Sample
<!-- calling a stored procedure -->
<executable class="com.ataccama.adt.task.exec.EwfWaitForSqlValueTask">
   <pollingInterval>200</pollingInterval>
   <timeout>60000</timeout>
   <connectionName>derbyDb</connectionName>
   <query>call PROC2($+{out1},$+{out2})</query>
   <mapping>
       <outputParameters>
           <value name="out1" type="INTEGER" export="false"/>
           <value name="out2" type="INTEGER"/>
       </outputParameters>
   </mapping>
   <condition>toInteger(out1) > 0 or  toInteger(out2) > 0</condition>
</executable>

<!-- running SQL query -->
<executable class="com.ataccama.adt.task.exec.EwfWaitForSqlValueTask">
   <pollingInterval>200</pollingInterval>
   <timeout>60000</timeout>
   <connectionName>derbyDb</connectionName>
   <query>select col1, col2 from $#{tableName}</query>
   <mapping>
       <resultSet>
           <value name="rsOut1" index="1"/>      <!-- indexed from 1, i.e. col1 will be stored into rsOut1 -->
       </resultSet>
   </mapping>
   <condition>toInteger(rsOut1) > 0</condition>
</executable>

Was this page useful?