Creating an XML electronic forms solution with an Adobe PDF form and IBM DB2 pureXML

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 pureXML

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

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

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

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.

Extending the eForm 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.

Development overview

These are the three major steps to create the complete electronic forms solution (refer back to Figure 1 for a diagram):

1. Create the database and table to store the form data in DB2 pureXML using DB2 Command Editor
2. Create and deploy the data web service using Data Studio
3. Create the electronic form using Adobe LiveCycle Designer ES

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.

Create the database and table to store the form data

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.

1. Open a DB2 Command Editor window and type the following command to create a database named eformdb:

2. Use the following command to connect to the database:

3. To create a table, type the following command. For simplicity, this command creates only one column in the table, but notice the column has a data type of xml.

Create and deploy the data web service

This section describes how to create a web service that accepts the XML form data and inserts it into the database. The insert statement that is the foundation of this web service is as follows:

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.

1. Open Data Studio (the images in this article are from Data Studio Developer 2.1).
2. Set up the database connection. In the Data Source Explorer window double-click Database Connections > EFORMDB [DB2 Alias] to bring up the Properties window (see Figure 4). (Note: if the EFORMDB database doesn't show in the list, right-click the Database Connections item and select New to create the connection.) For Drivers, select IBM Data Server Driver for JDBC and SQLJ Default from the drop down menu. Type your DB2 user name and password and select Save Password. You can test the connection by clicking Test Connection. Click OK to finish.

3. Create a new project. Right-click anywhere in the Data Project Explorer window and select New > Data Development Project. In the New Data Development Project window, type eFormProject as the project name. Click Next. Select EFORMDB from the Connections list in the Select Connection dialog box (Figure 5) and click Finish.

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.

4. Create a new script. Click the plus sign (+) in the Data Project Explorer window and expand eFormProject. Right-click eFormProject > SQL Scripts then select New > SQL or XQuery Script to create a new SQL statement. Type insertForm as the name (Figure 6) and click Finish.

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.

5. In the insertForm.sql window, type the following:

This statement will take the data value passed to DB2 as the :document parameter and insert it into the form column of eformtable.

6. Click the X to close the script folder and save insertForm.sql. You will see it listed in the SQL Scripts list for eFormProject in the Data Project Explorer.
7. Create the Web Service. In the Data Project Explorer window, right click eFormProject > Web Services and select New Web Service to create a web service. Type eFormWebService as the name and eForm as the namespace URI (see Figure 7). Once again, be precise with the spelling and capitalization. Click Finish.

8. In the Data Project Explorer window, drag and drop the eFormProject > SQL Scripts > insertForm.sql object onto eFormProject > Web Services > eFormWebService* (see Figure 8). This will create a new operation in the eFormWebService called insertForm.

9. Build and deploy the web service. In the Data Project Explorer window, right click eFormProject > Web Services > eFormWebService* and select Build and Deploy. In the Deploy Web Service window (see Figure 9), select Server and select IBM WASCE v2.1 Server at localhost from the drop down menu. Select Launch Web Services Explorer After Deployment and click Finish. (This will first start WASCE if it is not already started.)

10. Save the WSDL. In the Web Services Explorer window (see Figure 10), click the URL, which begins with http://localhost:..., under WSDL Main in the Navigator pane. Then in the Actions pane scroll down to select Import WSDL To File System. Save the file as eForm.wsdl on your disk drive; you'll use in the next section.

The web service is now complete and deployed.

Create the electronic form

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.

1. Create an XML file with the following contents, using any editor, as a source of the XML data structure for your PDF form (the indentation is for readability; you don't have to be careful about indenting):

2. Save the file as data.xml.
3. Start Adobe LiveCycle Designer ES. Choose File > New and select Use a Blank Form to create a new form (see Figure 11). Click Next and on the following Document Setup screen click Next again.

4. Uncheck Add An Email Button and Add A Print Button (see Figure 12). Click Finish.

5. Create a Data Connection. Right-click in the Data View panel and select New Data Connection (see Figure 13).

6. Type DataInstance for the name and for the Get Data Description From option select Sample XML Data (see Figure 14). Click Next.

7. For the sample XML data file, select the data.xml file created in steps 1 and 2 (see Figure 15). Click Finish.

8. Create the form layout. Drag and drop an XML element node of DataInstance, such as firstname, onto the canvas to create the corresponding label and input field on the form (see Figure 16). This process also binds the form input field to the data node, which is indicated by an arrow symbol next to the element. (You can see, or change, a binding in the Binding tab of the Object window on the lower right, as seen in Figure 20.) Drag and drop the remaining XML element nodes in DataInstance (lastname, userid, and phone) onto the form design canvas.

9. Create another new connection. Right-click in the Data View panel and select New Data Connection. This time, name it WSDLInstance and for the Get Data Description From option select WSDL File (see Figure 17). Click Next.

10. Specify the WSDL file for the web service that will receive the form data. In this example, it is the eForm.wsdl file you created in the Create and deploy the data web service section above (see Figure 18). Click Next.

11. Select the insertForm SOAP operation of the web service (see Figure 19), click Next, and then click Finish.

12. From the Data View window, drag and drop the document node in the WSDLInstance onto the canvas (see Figure 20) to create a field for this web service parameter. With the Document input field selected, in the Binding tab of the Object panel change the Default Binding option to None since this will not be a user input field.

13. Create a send button. In the Data View window, drag and drop the insertFormBtn node of the WSDLInstance onto the canvas (see Figure 21) to create a button for sending the form data to the web service.

14. In the Standard section of the Object Library, select Button and click on the canvas to create a new button (see Figure 22). In the Object panel, change the text of the button to Submit.

15. Choose Window > Script Editor to show the Script Editor (see Figure 23). It appears above the canvas, but may not open very wide. You can drag to enlarge it. With the Submit button selected, in the Script Editor select Click as the Show option and select JavaScript as the Language option. An asterisk (*) will appear when you add the following script in the Script Editor to indicate a change in the click event:

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.

16. Hide the Document input field. With the Document input field selected, in the Field tab of the Object panel, change the Presence option to Invisible (see Figure 24). Do the same to hide the insertFormBtn button.

17. Use the Preview PDF tab above the canvas to preview the form (see Figure 25). Save and close the form.

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.

Viewing the submitted form data in the database

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:

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:

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.

Where to go from here

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.

Acknowledgements

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.

References

• DB2 Express-C with pureXML
• Technical Papers for Using and Querying DB2 pureXML
• IBM Data Studio and Optim Development Studio
• IBM Data Studio and Optim Development Studio downloads
• Adobe LiveCycle Designer ES
• Build an intelligent eForms solution based on DB2 pureXML, Lotus Forms, and Web services
• Data Web Services: Build Web services the new way to access IBM database servers