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:

1. datasource
   This section defines the source database file for the lookup. The settingsName refers to a database connection of InterFormNG2, where the connection, user and password is stored.
2. outputPage
   This is the spooled file page, where the additional data should be added. In the example above the data found via SQL is added into page 1 of the spooled file.
3. 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.
4. 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.
5. outputVariable
   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 example above 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.
6. 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.

Note for the IBM i platform: Use a library list to select the IBM i files in the SQL expression

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.

1. 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.

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)) &gt; 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:

1. 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"/>
</enrich>

1. pages
   The pages section is repeat loop, which repeats everything inside for each spooled file page in the input spooled file.
2. lookup
   This defines a section for an SQL lookup.
3. 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.
4. 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.
5. 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.
6. 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.
7. 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.