26 October 2009
This article guides you through creating a simple yet powerful eForms solution based on Adobe LiveCycle Designer ES and IBM® DB2® pureXML® that you can use to automate the collection, transmission, and storage of user data.
Forms are used in many different industries and businesses to gather information from customers or employees and initiate internal processes. Paper based forms processes are slow, resource intensive and imprecise and often require a conversion stage to input the collected data into computer systems. Switching from paper to electronic forms (eForms) systems can improve turnaround time, reduce resource consumption, and increase accuracy.
The eForms solution described in this article involves three components: an electronic form, a web service, and a database. Because all three speak XML, there is no need for complex data mapping or conversion steps between components. The XML data format used in this example implementation is a very simple structure but you can easily expand the format to meet specific needs or even base the format on one of the many XML-based industry standards for data exchange such as NIEM for government, ACORD for insurance, or FIXML for financial markets. This solution places the collected data into the XML business format at collection time and keeps that format throughout submission and storage for a streamlined process that culminates in the business format being stored in a database where it is available for downstream processes or business analysis. You can follow the steps in this article to build and run the three components needed for this efficient XML electronic forms data collection solution.
The electronic forms solution described in this article uses Adobe LiveCycle ES for data capture and DB2 pureXML for data storage and query, connected by a simple web service that you can create with IBM Data Studio (see Figure 1). With these three pieces you will have an operational electronic forms application that can be the basis for efficient, quick, and accurate data collection and management.
As you see in Figure 1, the solution uses specific software tools to develop the three components and some other software that is part of the runtime execution environment. The following sections provide a brief overview of each of the products in this solution in the order they will be used during solution development, starting with the database.
IBM DB2 is a high performance, scalable relational database system. The pureXML feature was first introduced in IBM DB2 version 9, on both distributed (Linux, UNIX, Windows) and z/OS platforms. This free feature provides the ability to natively store XML data in its hierarchical format within the DB2 relational database system. An XML document is stored with full database awareness of the document's internal hierarchical structure and its individual elements and attributes. With pureXML, customers can efficiently store, index, query, and update XML data alongside relational data stored in the same table or database.
XML data in DB2 pureXML is presented as a column in a table like other relational data, with a data type of XML. DB2 supports queries written in industry-standard SQL/XML and XQuery languages to access and manipulate the XML data. DB2 services such as optimization, indexing, concurrency control, and utilities are extended to support XML data. With pureXML, DB2 has become a hybrid database that can efficiently access and manage both relational data and XML data (see Figure 2).
XML data collected from forms may be inserted into a DB2 XML column, providing data security while maintaining efficient access to individual data elements. Queries can return entire documents or subsets of documents depending on what is needed.
Note: In this paper, I use DB2 9.5 for Linux, UNIX, and Windows on Windows, specifically DB2 Express-C edition, which is free to download and use. The included DB2 Command Editor is used to execute commands to DB2.
IBM Data Studio is a free Eclipse-based data development and testing environment for building database objects and applications. IBM Optim Development Studio (formerly called Data Studio Developer) includes the Data Studio capabilities with additional features such as support for Java application optimization called pureQuery. Data Studio and Optim Development Studio enable you to develop and execute database queries and stored procedures using SQL, SQL/XML, and XQuery. Using Data Studio, you can easily generate and deploy data web services directly from SQL statements using visual drag-and-drop techniques. The web services generated support both SOAP and REST bindings and can be immediately deployed to supported application servers.
In this article, I use Data Studio to create and deploy a simple data web service to receive XML data from an eForm and store it directly into DB2.
IBM WebSphere Application Server (WAS) is a Java, J2EE, and web service based application server. WAS offers a rich application deployment environment with a complete set of application services, including capabilities for transaction management, security, clustering, performance, availability, connectivity, and scalability. In this article, I use WebSphere Application Server Community Edition (WASCE), integrated into Data Studio, for web services deployment. WASCE is free of charge and can be downloaded from the IBM website.
Adobe LiveCycle Designer ES software enables the creation and deployment of interactive XML-based forms for use with Adobe Reader or Flash Player software or web browsers. With Adobe LiveCycle ES, customers can accurately capture data from completed forms as XML and transfer it directly to processing and storage engines, improving data accuracy, streamlining form-driven business processes, and reducing cycle times. In this article, I'll show you how to use Adobe LiveCycle Designer ES to design the form and use either Adobe Acrobat 9 Pro or Adobe Reader with Adobe LiveCycle Reader Extensions ES to open the form and run the eForms solution.
The concepts for this article are derived from a similar integration involving IBM Lotus Forms, an XML-based forms product, integrating with DB2 pureXML via a web service. Since the eForms process is built on an XML data flow, it is possible to use the same web service and database for both the Lotus and Adobe solutions. Any front-end software that produces XML data from the data capture activity can be similarly integrated. (In fact, you can even structure the XML differently.) It is possible to extend the solution even further by having multiple XML-producing data sources integrated into the same web service and database backend. These could include both electronic forms that produce XML data and a paper form scan process that converts the scanned data into XML (see Figure 3).
In this section I will guide you through the steps to create the electronic forms solution with LiveCycle Designer ES and DB2 pureXML.
These are the three major steps to create the complete electronic forms solution (refer back to Figure 1 for a diagram):
This whole process can be completed quite quickly once you have the necessary software installed. The majority of the effort is creating the specific text, fields, and layout for the electronic form. After that, all you need to do is fill in the form and submit the data using Adobe Acrobat 9 Pro or Adobe Reader with LiveCycle Reader Extensions. See Running the eForms solution with the Adobe PDF form for detail on that process as well as instruction on how to query the XML form data that gets stored in DB2.
For more on the architecture and implementation details of the foundational electronic forms solution based on an XML data flow initiated by Lotus Forms, please see Build an intelligent eForms solution based on DB2 pureXML, Lotus Forms, and Web services. The steps to create the database, table, and web service are identical in the Lotus Forms-based solution and the Adobe LiveCycle ES solution described here.
This section describes how to create a database and table (named eformtable) that consists of a single column (named form), of type XML to store the XML data from the form.
create database eformdb using codeset utf-8 territory us
connect to eformdb
create table eformtable (form xml)
insert into eformtable (form) values (:document)
This solution requires only a simple web service, though more sophisticated services are certainly possible. Use the following steps to create a data web service with Data Studio.
Note: Information such as the project name will be used in the WSDL created by Data Studio Developer for the web service, so be aware that it is case sensitive.
Note: I chose to use the default SQL and XQuery editor to create the SQL statement. You could also build the statement using the SQL Query Builder, which uses a wizard approach.
insert into eformtable (form) values (:document)
This statement will take the data value passed to DB2 as the
:document parameter and insert it into the form column of eformtable.
The web service is now complete and deployed.
To create an XML-based PDF Form with web service support, you will need Adobe LiveCycle Designer ES, which is bundled in Adobe Acrobat 9 Pro on Windows.
<data> <name> <firstname></firstname> <lastname></lastname> </name> <userid></userid> <phone></phone> </data>
document.rawValue = xfa.data.data.saveXML(); insertFormBtn.execEvent("click");
This script first assigns the entire DataInstance to the Document input field so that the form data that is entered in the form will be saved as the value of the
document parameter. Then a click event of the insertFormBtn button is executed to send the form to the web service.
You can now open the form using Adobe Acrobat Pro or Adobe Reader with LiveCycle Reader Extensions ES. Fill out the form and click Submit to submit the form data for insertion into the database If you have created the web service and the pureXML database following the steps in the referenced paper, and WASCE is running, then you can check the database to verify the submission was successful and review the form data.
If you want to view the form data that you just submitted, return to the DB2 Command Editor. You can view all the data you submitted with this very simple SQL query:
SELECT form FROM eformtable
This will return all the XML documents from each form submitted. Of course you can restrict which documents you retrieve by adding a WHERE clause. To restrict based on the XML values you can issue a query such as the following:
SELECT XMLCAST(XMLQUERY('$FORM//phone/text()') as varchar(15)) FROM eformtable WHERE XMLEXISTS('$FORM/data/name[lastname='Patterson']')
This query will return the phone number from all forms with the last name of Patterson. You can experiment with other queries against the form data. See this article's references for papers describing SQL/XML and XQuery.
This article has shown how to create Adobe PDF form that produces XML instance data and how to integrate that form into DB2 pureXML to create an XML-based Eeectronic forms solution. If you followed along, you created the form and submission process in Adobe LiveCycle Designer ES, and built the web service to accept the form data and insert it into the XML column of a DB2 pureXML database. Because the data does not need to go through conversion processes to transmit or to store in the database, the XML-based electronic forms solution is easy to create and deploy.
The same web service and database table with an XML column can be used to receive the XML form data from a PDF form and a Lotus Form (which is based on XForms, an XML standard). This concept can be expanded to more generally state that any data capture tool or application that can produce XML data can integrate directly with the web service and DB2 pureXML database of this solution.
You can further expand the solution by enhancing the web service to perform a select (instead of an insert) to retrieve data and display it in a form for review and/or update.
The author would like to thank those who provided input to or reviewed this article. In particular he would like to thank Dexiong Terry Zhang who provided the research on developing an Adobe PDF XML form during his tenure as an intern at IBM.