It is possible to change the contents of a spooled file (as known on the IBM i platform) and to add data from an external database with the advanced special workflow component: Enrich spooled file from database.
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:
<?xml version="1.0" encoding="UTF-8"?>
<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):
<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:
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.
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.
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.
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
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.
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.
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.
