Script Examples

The following chapter shows examples how to implement some general use cases in ANT, JavaScript or Groovy code. These examples can be used in XDM on each place where scripts are used.

Saving a property for later usage

Suppose you want to use a property foo with the value bar later in the workflow.

ANT

<property name="properties.foo" value="bar"/>

JavaScript and Groovy

properties.put("foo", "bar");

Getting specific task variables

To access the value of a task parameter, such as logLevel, the following command can be used:

ANT

<?xml version="1.0"?>
<project name="Hook" default="run">
...
<!-- print the task property-->
<echo message="Log level: ${logLevel}"/>
...
</project>

JavaScript and Groovy

properties.get('logLevel');

Executing an SQL query

Executing a SQL query and access the values of the columns is possible in ANT, JavaScript in a task stage hook.

Executing SQL using JavaScript

In Task Stage hooks the typical script methods to use JDBC for querying data can be used as described in common script examples for executing SQL.

Executing SQL using ANT

The task stage hook parameter with data type Connection returns the following three properties to use in an Ant SQL Task:

<parameter-name>.url: The url that was specified in the connection
<parameter-name>.user: The user of the corresponding credential of the connection
<parameter-name>.password: The password of the corresponding credential of the connection

The following example describes how to use an Ant SQL task. This Ant SQL task establishes a DB2 connection with the parameter connection and prints the output of the query.

<?xml version="1.0"?>
<project name="Hook" default="run">
    <import file="${inst.dir}/config/taskdef.xml" />
    <target name="run">
                  <sql driver="com.ibm.db2.jcc.DB2Driver"
                         url="${connection.url}"
                         userId="${connection.user}"
                         password="${connection.password}"
                         print="yes"
                         output="/xdm/data/output.out"
                         showWarnings="true">
            <!-- sql query-->
                         <![CDATA[
                                  SELECT ID,"Description" FROM "SCHEMA"."TABLE_1";
                          ]]>
          </sql>

      <!-- load the generated output file from the sql task and print it-->
          <loadfile property="content" srcFile="/xdm/data/output.out" />
          <echo message="${content}"/>
    </target>
</project>

For sql driver the specific driver for the DBMS has to be entered. This example works for DB2 Z/OS and DB2 LUW.

Populating contents into a mapping table

XDM is able to modify data using mapping tables containing key value pairs for specific scenarios, such as a name mapping. For having the information ready to use in a modification method, the most common use case is to create a mapping table before the modification is applied. This can be done using a task stage hook, that is being executed right before data should be processed as part of an XDM task.

It can also be executed within a workflow template and hereby being scheduled e.g. for having a mapping table filled with data on the daily updated basis.

For putting data into a mapping table (that might exist already or will be created if not), the MappingTableUtils API is used. A detailed overview of available methods for the API can be found in the corresponding object reference.

Further examples on how to use the above-mentioned API are displayed in the script examples.

A specific example on how to populate contents into a mapping table is shown below. The mapping table will be stored inside a mapping table container called firstNames. The container has to be created beforehand. The mapping table itself has the name first_names, which must be specified when calling merge(…) using the API.

Groovy
JavaScript

import de.ubs.xdm3.batch.modification.MappingTableUtils
def utils = new MappingTableUtils(firstNames)

taskStageHook.sourceConnection.jdbcConnection.createStatement().with { stmt ->
    stmt.executeQuery("SELECT DISTINCT first_name FROM production.employees").with { rs ->
        taskStageHook.sourceConnection.jdbcConnection.createStatement().with { stmt2 ->
            stmt2.executeQuery("SELECT * FROM (SELECT DISTINCT first_name FROM production.employees) s ORDER BY RANDOM()").with { rs2 ->
                while(rs.next() && rs2.next()) {
                    def firstName1 = rs.getString(1)
                    def firstName2 = rs2.getString(1)

                    // populating contents into the mapping table "first_names"
                    utils.merge("first_names", [firstName1] as Object[], [firstName2] as Object[])
                    println("Mapped ${firstName1} to ${firstName2}")
                }
            }
        }
    }
}
utils.close()

importPackage(Packages.de.ubs.xdm3.batch.modification);
var utils = new MappingTableUtils(customFirstName);

var stmt  = taskStageHook.sourceConnection.jdbcConnection.createStatement();
var rs    = stmt.executeQuery("SELECT DISTINCT first_name FROM production.employees");
var stmt2 = taskStageHook.sourceConnection.jdbcConnection.createStatement();
var rs2   = stmt2.executeQuery("SELECT * FROM (SELECT DISTINCT first_name FROM production.employees) s ORDER BY RANDOM()");

while (rs.next() && rs2.next()) {
    var firstName1 = rs.getString(1);
    var firstName2 = rs2.getString(1);

    // populating contents into the mapping table "first_names"
    utils.merge("first_names", [firstName1], [firstName2]);
    print("Mapped " + firstName1 + " to " + firstName2);
}

utils.close();

Calling a REST API from a hook

It is possible to call an external service from task stage hooks via a REST API.

In a task stage hook, the examples from the Common examples for calling a REST API can be directly used in a task stage hook.

Select Value from DB to Parameter

This stage hook retrieves a value from the database and makes it available for further processing. To read the value, it requires an SQL statement, which can be freely defined. The returned value of the statement can be used in the task as a custom parameter by using the key SQLResult. For the execution a connection parameter has to be defined.

Groovy
JavaScript

def query = 'SELECT <your value> AS "Value" FROM <your schema>.<your table>'
def connection = taskStageHook.connection.jdbcConnection
def pStmt = connection.prepareStatement(query)

def rs = pStmt.executeQuery()

if (rs.next()) {
    def result = rs.getString("Value")
    println("Your value: " + result)
    properties.put('SQLResult', result)
}

rs.close()
pStmt.close()

importPackage(java.sql);

var query = 'SELECT <your value> AS "Value" FROM <your schema>.<your table>';
var pStmt = taskStageHook.connection.jdbcConnection.prepareStatement(query);
var rs = pStmt.executeQuery();

if (rs.next()) {
    result = rs.getString("Value");
    print("Your value: " + result);
    properties.put('SQLResult', result);
}

rs.close;
pStmt.close();

Property Print Hook

To print all properties being set in a task can be print to the log with this task stage hook being added to the task. This task stage hook does not work within a workflow.

Groovy
JavaScript

// 'properties' is provided by XDM and contains all task-specific properties for this execution.
def keys = properties.keySet().toList().sort()


def maxLen = keys.collect {k ->
  def keyStr = k.toString()
  return (1 + keyStr.length() + 1) }.max()

keys.each { k ->
  def keyStr = k.toString()
  def keyBlock = ">" + keyStr + "<"
  def padding = " " * (maxLen - keyBlock.length() + 2)

  def v = properties[k]?.toString()
    println "${keyBlock}${padding}= >${v}<"

}

println "_______________________________________________________________________"

// 'properties' is provided by XDM and contains all task-specific properties for this execution.
var keys = Java.from(properties.keySet());
keys.sort();

var maxLen = 0;
for (var i = 0; i < keys.length; i++) {
    var keyStr = String(keys[i]);
    var len = keyStr.length + 2; // '>' + key + '<'
    if (len > maxLen) maxLen = len;
}


for (var j = 0; j < keys.length; j++) {
    var keyStr  = String(keys[j]);
    var keyBlock = ">" + keyStr + "<";

    var paddingSize = maxLen - keyBlock.length + 2;
    if (paddingSize < 0) paddingSize = 0;

    var padding = new Array(paddingSize + 1).join(" ");

    var v    = properties.get(keys[j]);
    var vStr = (v != null) ? String(v) : "";

    print(keyBlock + padding + "= >" + vStr + "<");
}

print("_______________________________________________________________________");