17 December 2007
This sample file contains TransformQuery.1.0.xml, which is an exported process and customer.sql, which is a MySQL backup.
Adobe LiveCycle Workbench ES provides a service for accessing relational databases. The JDBC service, found within the Foundation service category, contains multiple query and execution operations for relational data accessible via JDBC. For orchestrations that require access to multiple rows from a table, the JDBC service delivers the operation called Query for Multiple Rows as XML.
The Query for Multiple Rows as XML operation returns an XML instance where each node has an attribute "type" with a value representing the database schema data type. This article demonstrates how to remove the "type" attribute from the XML instance using the XSLTService Transform operation.
The use case
Let's assume that an orchestration requires multiple rows of data from a MySQL table called "customer" (see Figure 1).
Based upon the database schema, by default, the "Query for Multiple Rows as XML" service operation returns the following XML instance:
<root> <element> <id type="INTEGER UNSIGNED">1</id> <firstName type="VARCHAR">Fred</firstName> <lastName type="VARCHAR">Flinstone</lastName> <address type="VARCHAR">301 CobbleStone Way</address> <city type="VARCHAR">Bedrock</city> <state type="VARCHAR">CA</state> <zipCode type="INTEGER UNSIGNED">95110</zipCode> </element> <element> … </root>
However, the use case may require the XML instance to be formatted differently. For illustration purposes, the data extracted from the "customer" table will be written to the file system as XML and the XML instance will be formatted as follow:
<customers> <customer> <id>1</id> <firstName>Fred</firstName> <lastName>Flinstone</lastName> <address>301 CobbleStone Way</address> <city>Bedrock</city> <state>CA</state> <zipCode>95110</zipCode> </customer> </customers>
Draw the orchestration
The process map will contain the following service operations (see Figure 2).
Use the following steps to create the process map by adding and linking instances of the following service operations:
Add process Variables
Now define two process variables (see Figure 3).
Configure the JDBC operation
Follow the steps to configure the JDBC operation:
The datasource name is a JNDI name defined in a datasource file. You can add data sources to the datasource file deployed by LiveCycle ES (..\deploy\adobe-ds.xml), or preferably, create "application" datasource files. Separating datasource files from "adobe-ds.xml" eliminates the introduction of errors into the "system" datasources and optimizes portability, particularly for migration through the application development lifecycle.
The XML instance will be used in the Transform operation for testing the XSL transformation. On Windows platforms, avoid using Notepad as a text editor for XML. Notepad adds a Byte-Order-Mark (BOM) which can cause XSL transformations to fail.
Configure the XSLT operation
Follow the steps below to configure the XSLT operation:
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/ | node()"> <xsl:copy> <xsl:apply-templates select="node()"/> </xsl:copy> </xsl:template> </xsl:stylesheet>
The template <xsl:template match="/ | node()"> performs matches on two patterns. The pattern / matches the root node and node() matches any node other than an attribute node and the root node. The copy is then performed on all child nodes excluding attribute nodes.
Configure the file utilities operation
Do the following steps to configure the file utilities operation:
Deploy and test the orchestration
Follow the steps below to deploy and test the orchestration:
—Open a browser and enter the URL to call the process via the default SOAP endpoint. For example, if the process name is TransformQuery, the following URL will synchronously invoke a process instance (see Figure 8):
The WS-Security implementation in Axis requires user authentication on the SOAP request. Use administrator/password for user name/password.
The SOAP envelope contains the node invokeResponse indicating the process executed successfully.
The invocation procedure defined above is a simple method to test an orchestration that has no input parameters. More typically, an orchestration deployed in LiveCycle ES will be invoked by a client application using one of the out-of-the-box mechanisms including a Java API, web services, e-mail, and watched folders. Using the LiveCycle Administration Console, a service can be configured to be exposed by one or more of these mechanisms. For more on invoking LiveCycle ES, see the LiveCycle ES SDK Help.
Go to the pathname specified in the Write String service to validate the creation of the file and the XML content.
Adobe LiveCycle ES can extend your enterprise with new applications that span data capture, process orchestration, and document generation. This article demonstrated the assembly of services into a process without a user interface. Imagine…
For more on LiveCycle ES, visit the Adobe LiveCycle ES documentation page.
LiveCycle Workbench ES Help provides instructions for importing process versions.