You can also add data with the function, ng:databaseLookup directly in the spooled file with a transformation or just use the ng:databaseLookup in either the designer or in the workflow to put the sql data into a variable.
Related components: Enrich XML from database, Database SQL select and Database SQL update.
A related workflow input is: From database with custom SQL, which starts a workflow with an SQL select.
In order to refer to a database a database driver must be setup.
The enrichment is defined in an xent file with the file extension .xent.
The parameter of the Enrich spooled file from database component is:
After running the Enrich spooled file from database workflow component the spooled file is actually not really changed, but the payload of the workflow is changed, so that it now contains the new, changed spooled file and subsequent workflow elements can 'see' the added data.
Xent file
This is an xent file (with extension .xent), in which you can setup SQLs to add more data into the payload XML file and/or to copy/calculate nodes via XPath expressions.
The .xent file must be loaded in the transforms library (or a sub folder inside of this).
The Xent file format is described below in details.
The link for the Xent format is for XML files only, but the setup is very similar for spooled files.
Here is an example of how you can setup an xent file for a spooled file:
If the spooled file is loaded as version 2 (which is default and also recommended), then you can use this as a way to lookup data from a database file:
<enrich>
<datasource name="default" type="jdbc" settingsName="Default"/>
<pages>
<lookup outputPage="1" outputPosition="7, 61, 4, 4" outputVariable="customerEmail" condition="number(substring(//page[1]/line[13], 57, 4)) != 1009">
<sql expression="SELECT Text1 FROM APFUDV/TESTFILE where NUMBER = ?" datasource="default">
<parameter>substring(//page[1]/line[13], 57, 4)</parameter>
</sql>
</lookup>
</pages>
</enrich>
If the spooled file is loaded as version 1, then you can use these lines for the lookup (keep the header):
<?xml version="1.0"?>
<lookup outputPage="1" outputPosition="7, 61, 4, 4" outputVariable="customerEmail">
<sql expression="SELECT email FROM testemails where customerId = ?" datasource="ifdb">
<parameter>ng:spoolMap(//page[1], 57, 60, 13, 13)</parameter>
</sql>
</lookup>
Another example can be found in the section, Enrich all spooled file pages. This illustrates how you can add SQL data on all pages of the spooled file or only on pages, where a condition is true.
From the top you can use these options:
It is possible to dynamically select the files on the IBM i platform via a library list. You just need to exclude the library in the reference to the file. The setup of the library list is covered in the section, How to select files in SQL via a library list on IBM i on the database connection.
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):
<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>
</lookup>
(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:
<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"/>
</enrich>