Creating XML files is easy. You can think of it as a complex report, and as such a XML file can be created by a plain RPG II program with some O record wizardry.
On the other hand, reading an XML file file is complex. There's white space to ignore, completion checks to do, schemas to validate, DTD to test against, naming spaces, private tags, encodings to parse, and more.
On the gripping hand, we can limit our problem to getting the data out of XML and into data entities that are more familiar to System i developers, like database tables.
Fortunately, there are tools that let you map the relationship between data in XML and data in a table. Some are custom made for the System i and geared for RPG developers. I am deliberately going to ignore them in this post. If I have a choice, I prefer to use ubiquitous technology, that is easy to understand by hordes of programmers. Other solutions require deployment of ESB / ETL / EAI or similar infrastructures. While I am a big fan of ESB, sometimes you need a local solution rather than depend on external processes and processing, not to mention the costs of integration, licenses, and servers.
That leaves us little choice but to focus on Java based tools.
The first one is Saxon, to be found here http://www.saxonica.com .
Saxon is among the leading XML transformation tools. In fact, it is embedded into a very large number of commercial XML processing tools and libraries.
Saxon has an extension, called Saxon-SQL, that lets you embed SQL data modification statements into a XSLT transformation stylesheet.
You need Java at version of at least 1.4 , and the Saxon distribution. The free Saxon-B is good enough for our needs, but I recommend that you get Saxon-SA - at £300.00 per server it is a real bargain.
The JT400 package is also required in order to use JDBC connections to the System i database.
I unzipped the entire distribution into a new directory: /QIBM/UserData/saxon
Now let's peek at the XML and database integration task.
Let's suppose that we are to insert into the Work Order application table data from XML.
The XML looks like this:
<?xml version="1.0"?>
<WO_OUT>
<ROW>
<WO>U10002</WO>
<ITEM>80012470</ITEM>
<FACILITY>PL</FACILITY>
<START_DATE>20081126</START_DATE>
<DUE_DATE>20081129</DUE_DATE>
<QUANTITY>654321</QUANTITY>
<TV_TRANSACTION_ID>85246786</TV_TRANSACTION_ID>
<INTERFACE_TRANSFER_DATE>20081015</INTERFACE_TRANSFER_DATE>
<INTERFACE_TRANSFER_TIME>110903</INTERFACE_TRANSFER_TIME>
</ROW>
</WO_OUT>
The mapping to the data table columns is:
ITEM --> TCPROD
FACILITY --> TCFAC
START_DATE --> TCRDTE
DUE_DATE --> TCDDTE
QUANTITY --> TCQORD
TV_TRANSACTION_ID --> TCTRID
INTERFACE_TRANSFER_DATE --> TCDATE
INTERFACE_TRANSFER_TIME --> TCTIME
The following XSLT maps the XML values into the corresponding database columns. It is based on the sample that comes with Saxon.
Notice the following special interest items:
- The stylesheet refers the DB2 JDBC driver. If you provide the correct library at runtime, this code can be executed on the System i server as well as on an external server. We will use the JT400Native.jar library for internal processing
- The system name, user, password and library are resolved at runtime thru external parameters. The user and password are provided as part of the connection string.
- normalize-space is used to take care of extra white space like spaces, tabs, carriage returns
- The INTERFACE_TRANSFER_DATE and INTERFACE_TRANSFER_TIME values are optional and possibly will not exist. Therefore TCDATE and TCTIME must be manipulated to set numeric defaults.
- I recommend to set all database fields to allow null values in case of more missing data.
- Do not use 1 char fields. All char fields must have length of at least 2.
- The database table should be journalled - there is an implicit commit.
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:fn="http://www.w3.org/2005/xpath-functions"
xmlns:sql="java:/net.sf.saxon.sql.SQLElementFactory"
xmlns:saxon="http://saxon.sf.net/"
extension-element-prefixes="saxon sql">
<!-- insert your database details here, or supply them in parameters -->
<xsl:param name="driver" select="'com.ibm.as400.access.AS400JDBCDriver'"/>
<xsl:param name="LIBRARY"/>
<xsl:param name="SYSNAME"/>
<xsl:param name="SYSUSER"/>
<xsl:param name="SYSPASSWORD"/>
<xsl:param name="database"
select="concat('jdbc:as400://', $SYSNAME,
';user=' , $SYSUSER, ';password=',
$SYSPASSWORD, ';naming=system;libraries=', $LIBRARY)"/>
<xsl:param name="user"/>
<xsl:param name="password" />
<!-- This stylesheet writes the book list to a SQL database -->
<xsl:variable name="count" select="0" saxon:assignable="yes"/>
<xsl:output method="xml" indent="yes"/>
<xsl:template match="WO_OUT">
<xsl:if test="not(element-available('sql:connect'))">
<xsl:message>sql:connect is not available</xsl:message>
</xsl:if>
<xsl:message>Connecting to
<xsl:value-of select="$database"/>...</xsl:message>
<xsl:variable name="connection"
as="java:java.sql.Connection" xmlns:java="http://saxon.sf.net/java-type">
<sql:connect driver="{$driver}" database="{$database}"
user="{$user}" password="{$password}">
<xsl:fallback>
<xsl:message terminate="yes">SQL extensions are not installed</xsl:message>
</xsl:fallback>
</sql:connect>
</xsl:variable>
<xsl:message>Connected...</xsl:message>
<xsl:apply-templates select="WO_OUT">
<xsl:with-param name="connection" select="$connection"/>
</xsl:apply-templates>
<xsl:for-each select="ROW">
<sql:insert connection="$connection" table="KFXT">
<sql:column name="TCCMPN" select="normalize-space(WO)"/>
<sql:column name="TCPROD" select="normalize-space(ITEM)"/>
<sql:column name="TCFAC" select="normalize-space(FACILITY)"/>
<sql:column name="TCRDTE" select="normalize-space(START_DATE)"/>
<sql:column name="TCDDTE" select="normalize-space(DUE_DATE)"/>
<sql:column name="TCQORD" select="normalize-space(QUANTITY)"/>
<sql:column name="TCTRID" select="normalize-space(TV_TRANSACTION_ID)"/>
<sql:column name="TCDATE"
select="if (string-length(normalize-space(INTERFACE_TRANSFER_DATE)) != 0)
then normalize-space(INTERFACE_TRANSFER_DATE) else '0.0'"/>
<sql:column name="TCTIME"
select="if (string-length(normalize-space(INTERFACE_TRANSFER_TIME)) != 0)
then normalize-space(INTERFACE_TRANSFER_TIME) else '0.0'"/>
</sql:insert>
<saxon:assign name="count" select="$count+1"/>
</xsl:for-each>
<xsl:message>Inserted <xsl:value-of select="$count"/> records.</xsl:message>
<sql:close connection="$connection"/>
</xsl:template>
</xsl:stylesheet>
To execute this, you will have to run the QSH java command:
- Set the classpath to include saxon9.jar, saxon9-sql.jar and jt400Native.jar.
- We will use localhost and the current user credentials. If you connect from a remote server, you will need the actual user and password.
#!/usr/bin/qsh
CP=/QIBM/UserData/Saxon/saxon9.jar
CP=$CP:/QIBM/UserData/Saxon/saxon9-sql.jar
CP=$CP:/QIBM/ProdData/OS400/jt400/lib//jt400Native.jar
java -Djava.version=1.5 -cp $CP net.sf.saxon.Transform
input.xml insert-table.xslt LIBRARY=MYLIB SYSNAME=localhost
SYSUSER=*current SYSPASSWORD=*current
and here it is in a CL program
PGM
DCL &CP *CHAR 1024
dcl &XQ *char 1024
dcl &PATH2LIBS *char 1024 '/QIBM/UserData/Saxon'
dcl &PATH2JDBC *char 1024 '/QIBM/ProdData/OS400/jt400/lib'
CD 'myDirectory'
chgvar &XQ ('net.sf.saxon.Transform')
CHGVAR VAR(&CP ) VALUE( +
&PATH2LIBS *tcat '/saxon9.jar' *tcat ':' *tcat +
&PATH2LIBS *tcat '/saxon9-sql.jar' *tcat ':' *tcat +
&PATH2JDBC *tcat '/jt400Native.jar' *tcat ':' *tcat +
'.' +
)
JAVA CLASS(&XQ) +
CLASSPATH(&CP) +
parm( +
'input.xml' +
'insert-table.xslt' +
'LIBRARY=MYLIB' +
'SYSNAME=localhost' +
'SYSUSER=*current' +
'SYSPASSWORD=*current' +
) +
PROP((java.version 1.5)) +
OUTPUT(* )
RETURN
ENDPGM
Posts
No comments:
Post a Comment