This section is an addition to illustrate the extra functionality of the Enrich spooled file from database workflow component.
Optionally an XPath expression can be used as a condition for the lookup. The lookup will then only be performed when the condition evaluates to "true". The condition must return a boolean value (see example below).
In order to refer to a database a database driver must be setup.
It is also possible to iterate across all the pages of the spooled file and change something on each page. Note that when iterating over the pages, the outputVariable and outputPage attributes are rarely meaningful. Instead the outputPosition attribute should be used to make changes to the current page (outputPage will default to current page if not specified).
An iteration across pages can be done like this:
For a spooled file loaded as version 2 (which is default and recommended):
<?xml version="1.0" encoding="UTF-8"?>
<enrich>
<datasource
name="default"
type="jdbc"
settingsName='Default'
/>
<pages>
<lookup outputPosition="7, 61, 4, 4" condition="number(substring(./line[13], 57, 4)) > 1002">
<sql expression="SELECT Text1 FROM APFUDV/TESTFILE where NUMBER = ?" datasource="default">
<parameter>substring(./line[13], 57, 4)</parameter>
</sql>
</lookup>
<lookup outputPosition="57, 60, 12, 12">
<sql expression="SELECT Text1 FROM APFUDV/TESTFILE where NUMBER = ?" datasource="default">
<parameter>substring(./line[13], 57, 4)</parameter>
</sql>
</lookup>
</pages>
</enrich>
For a spooled file loaded as a version 1 spooled file the loopup sections looks like this:
<lookup outputPosition="7, 61, 4, 4" condition="number(ng:spoolMap(./text(), 57, 60, 13, 13)) > 1002">
<sql expression="SELECT Text1 FROM APFUDV/TESTFILE where NUMBER = ?" datasource="default">
<parameter>ng:spoolMap(./text(), 57, 60, 13, 13)</parameter>
</sql>
</lookup>
<lookup outputPosition="57, 60, 12, 12">
<sql expression="SELECT Text1 FROM APFUDV/TESTFILE where NUMBER = ?" datasource="default">
<parameter>ng:spoolMap(./text(), 57, 60, 13, 13)</parameter>
</sql>
(The example above runs on an AS400 and it looks up in a local file, TESTFILE in the library, APFUDV).
In the example above these functions are executed:
For all pages in the spooled file these actions are done:
If the data found in line 13 position 57-60 is numeric larger than 1002, then an SQL select is done, that selects the email field from the testemails file (or table). The email address, that is returned is for the record, where the field, customerId is the same as the text found in line 13 position 57-60. This email is stored in line 4 position 7-61.
The second lookup does this:
Select the value of the discount field in the dicount file (or table), where the customerId is the same as the data found in line 13 position 57-60 of the current page. The found discount is to be stored i line 12 position 57-60.
The individual sections are described below:
datasource
The datasource refers to an existing database connection, where the source database, user and password are stored. Alternatively you can specify that in the xent file as indicated below, but that is not recommended as this includes the password in clear text:
<enrich>
<datasource name="default" type="jdbc"
driverClassName="com.ibm.as400.access.AS400JDBCDriver"
url="jdbc:as400:127.0.0.1;naming=system;translate binary=true"
user="myuser" password="mypassword"/>
pages
The pages section is repeat loop, which repeats everything inside for each spooled file page in the input spooled file.
lookup
This defines a section for an SQL lookup.
outputPosition
The positions and line, where the result of the SQL should be added. In the example above the data is inserted in positions 7 to 61 in line 4 for the first section and position 57 to 60 in line 12 for the section section.
condition
This is an optional part of the lookup. If inserted, then you can use this condition to decide if the SQL is to be executed for the current spooled file page.
outputVariable
Instead of (or in addition to) adding data into the spooled file, you can also create/update a workflow variable with the data found via SQL. In the other example the retrieved data is also put into a workflow variable called customerEmail. This variable can then e.g. be used in the Create PDF email workflow component as the To email address.
sql expression
This is the dynamic SQL expression, that is to be executed. In the expression above we retrieve the email field from the record in the testemails table/file, where the customerId is the same as the value found in the input spooled file. The dynamic values, that are found in the input spooled file are written as a question mark (?). If you use multiple question marks, then the values found in the parameter list below are substituted in the order in which they are defined i.e. the first question mark is substituted with the first parameter, the second question mark is substituted with the second parameter etc.
parameter
You can define none or multiple parameters. The parameters will substitute the question marks of the SQL expression above in the same sequence. In the example above only a single parameter is defined and the value is retrieved from page 1, positions 57 to 60 in line 13 of the input spooled file. This value is used for the comparison with the customerId field in the SQL.
