InterFormNG2 Manual

The xent file defines a lookup into a database of your choice via SQL. This can be called from the workflow via the Enrich XML from database workflow component.

 

A better alternative to the xent option is the built-in function, ng:databaseLookup, which is more flexible and can be used directly in the designer and in expressions in the workflow.

 

You can use variables, normal XPath functions and even the built in Xpath-functions, when you setup the parameter. The use of a variable is covered here.

 

Prerequisite

A prerequisite for the Xent files to work is, that you have placed a fitting JDBC driver inside a specific directory:

 

On the Windows platform you should place the jdbc driver inside the subdirectories /jre/lib/ext of where InterFormNG2 is installed. That is default:

C:\Program Files\InterFormNG2\jre\lib\ext

 

On other platforms you should place the jdbc driver inside the directory of the JDK (Java Development Kit), that is used for InterFormNG2.

On the IBM i platform it can e.g. be: /QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/lib/ext

 

This section contains these sub-sections:

 

1.Introduction to xent

2.How to refer to a database connection.

3.How to use workflow variables in xent.

4.How to trim a field for comparison

5.How to use e.g. less than or greater than for comparison in the SQL expression.

6.Option to insert extra nodes in the output with xent.

 

 

Introduction

Here is an example of how the contents can look for an Xent file:

<?xml version="1.0" encoding="UTF-8"?>

<enrich>

 <datasource 

         name="default" 

         type="jdbc"

         settingsName='MyIBMi'

 />

 <nodes path="/Root/Document">

<sql expression="select * from APFUDV/TESTFILE where NUMBER=?"

                 datasource="default">

                 <parameter>DocumentNo</parameter>

         </sql>

 </nodes>

</enrich>

 

The settingsName (MyIBMi in the example above) refers to a database connection. The database configuration is optional, but recommended as it can be used in order to be able to encrypt and secure the password used for the connection. It also makes it possible to consolidate all the database connections to other machines. The MyIBMi configuration used is shown here.

 

(The Xent file must be loaded into the transform library)

 

<?xml version="1.0" encoding="UTF-8"?>

<enrich>

 <datasource name="default" type="jdbc"

         driverClassName="com.ibm.as400.access.AS400JDBCDriver"

         url="jdbc:as400:192.168.250.214;naming=system;translate binary=true"

         user="myuser" password="mypassword" />

 

 <nodes path="/Root/Document">

<sql expression="select * from APFUDV/TESTFILE where NUMBER=?"

                 datasource="default">

                 <parameter>DocumentNo</parameter>

         </sql>

         <sql expression="select * from extraDet where source_number=?" datasource="default">

                 <parameter>concat(Source_No,'@{helloworld}')</parameter>

         </sql>

         <xpath>

<field expression="/Root/CompanyInfo/Company_Name">The_company</field>

                 <field expression="Paragraphs/Header/Line">Text</field>

         </xpath>

 </nodes>

</enrich>

 

(The 4 lines in the <xpath> section can be excluded (in italic). They are only needed, if you also want to change the layout of the XML file)

 

The Xent file above can be reduced, if you run InterFormNG2 directly on the AS400 / iSeries /IBMi, then we can change the contents above into this:

 

<?xml version="1.0" encoding="UTF-8"?>

<enrich>

 <datasource name="default" type="jdbc"

         driverClassName="com.ibm.as400.access.AS400JDBCDriver"

         url="jdbc:as400://localhost;naming=system;translate binary=true"

                                                  />

 

 <nodes path="/Root/Document">

                 <sql expression="select * from APFUDV/TESTFILE where NUMBER=?"

                 datasource="default">

                 <parameter>DocumentNo</parameter>

         </sql>

         <sql expression="select * from extraDet where source_number=?" datasource="default">

                 <parameter>concat(Source_No,'@{helloworld}')</parameter>

         </sql>

 </nodes>

</enrich>

 

(In the example above we refer to the local machine and do not specify the user/password, which means, that the SQL is done with the authority of the user profile, IFORMNG2, under which the InterFormNG2 service is running.)

 

The data source part defined the driver to use, the server as well as the user ID and password for validation.

 

The nodes part contains this:

1.The path defined is used for specifying the path for any nodes, that you are selecting in the input XML file to be used as keys for the lookup in the database. It also specifies the path to use when the new data is inserted. If the path selects multiple nodes, then the SQL expression and the xpath node will executed/inserted for each node.

2.An SQL expression to select data from the database. Here you can use a question mark (?) to select a node from the input XML file. The question marks are replaced by the parameters following the SQL expression in the same sequence, so in the example above the SQL expression will be:

 

expression="select * from APFUDV/TESTFILE where NUMBER=<DocumentNo>"

 

In the example above this Table (APFUDV/TESTFILE) can e.g.  used:

 

 

You can have multiple parameter nodes - one for each field. Each parameter is retrieved from the XML file. You can state the path for this in two manners:

 

A relative path (relative to the path stated earlier). If the path is relative, then the value of the parameter should not begin with a ‘/’. This parameter is found in the path stated earlier: <parameter>DocumentNo</parameter>.

You can also refer to a subnode relative to this path like this:

<parameter>SubNode/DocumentNo</parameter>.

 

An absolute path. You just specify the complete path to the node e.g. like so:

<parameter>/Root/DocumentNo</parameter>

(Must start with ‘/’)

 

 

Use of Variables in Xent

You can use workflow variables in your Xent files for the parameters of an SQL expression. You should assign the value before the Xent file is executed e.g. with the workflow component, Set one Workflow variable. As you can see in the example earlier in this section you refer to a variable in the format: @{<variable>}. This expression will be replaced by the value of this workflow variable.

 

 

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.

 

 

 

A few tips of the SQL expression:

The parameter, ? should not be included in '' - even if it is an alphanumeric value.

 

If you want to trim trailing/leading blanks from the parameter found in the input XML file you can consider this:

select * from APFUDV/TESTFILE where NUMBER=trim(concat('',?))

This suggestion may not work on all databases, so you will need to test it.

 

The parameter is a reference to a node in the input xml file - you cannot use xpath functions for this.

 

You cannot directly use these characters in the SQL expression: &,<,> as they are not allowed in an XML file, which the xent file really is.

So if you want to insert either of these characters in the SQL expression, then you need to refer to the table below for the substitutions:

 

 

You e.g. need to use this information if you want to select records with a condition, that tests if a node is larger or less than a value.

 

If you e.g. want to use this SQL expression:

SELECT * from employees where employeeNumber < 100

 

Then you need to replace "<" with "&lt;" and use this expression instead:

SELECT * from employees where employeeNumber &lt; 100

 

The replacements for less than as well as greater are also used if you want to use the comparisons: less than or greater than.

 

If you want to test if a value is less or equal to 100, then you want this expression:

SELECT * from employees where employeeNumber <= 100

But again you need to replace '<' with '&lt;', so you need to write the select like below:

SELECT * from employees where employeeNumber &lt;= 100

 

If you want to test if a value is greater or equal to 100, then you want this expression:

SELECT * from employees where employeeNumber >= 100

But again you need to replace '>' with '&gt;', so you need to write the select like below:

SELECT * from employees where employeeNumber &gt;= 100

 

 

 

 

Additional xpath nodes

Optional xpath_data nodes can be included.

This can be used for copying nodes into new node(s) or even doing advanced xpath calculations and put the result back into the XML file. In the example above a node called ‘The_company’ is added with the value of the node, /Root/CompanyInfo/Company_Name from the xml file. A ‘Text’ node per each found Line node is added in the output XML file. For each xpath node added an occurence attribute is added - numbered from 1 and upwards.

 

With the settings above you will get an XML file with the contents like below: