Build an intelligent eForms solution based on DB2 pureXML, Lotus Forms, and Web services

Create an electronic forms application in three steps

Form processing is one of the major daily tasks in many public and private businesses. Processes based on paper forms are resource intensive, time consuming, and often result in inaccurate or incomplete data. This article describes how you can build an efficient electronic solution for form data capture, submission, and management. The article guides you through three steps to build an efficient XML electronic forms data collection solution with Lotus® Forms for data capture, DB2® pureXML® for data storage and query, and a connection between the two via a simple Web service that you can create with Data Studio Developer. In three steps you can have an operational electronic forms application that can be the basis for efficient, quick, and accurate data collection and management.

Share:

Bryan Patterson (bryanp@us.ibm.com), DB2 Data Server Solution Architect, IBM

Author Photo: Bryan PattersonBryan Patterson is a senior solutions architect at IBM's Silicon Valley Laboratory who specializes in database management. He has more than 20 years of software industry experience including management positions in development, quality assurance, and product planning. His email address is bryanp@us.ibm.com.



Dexiong Terry Zhang (dtzhang@us.ibm.com), Software Engineer (Co-op), IBM

Author Photo: Dexiong Terry ZhangDexiong Terry Zhang is a software engineer Co-op at IBM's Silicon Valley Lab. He graduated from San Jose State University with a degree in Computer Science. He joined the DB2 pureXML Enablement team in 2008 and is working on solution integration of pureXML in various projects.



30 April 2009

Also available in Vietnamese Spanish

Introduction

The daily operations of today's businesses rely heavily on forms for data collection, including such activities as account opening, claims, requests for service, and even tax filing. Many data collection processes still rely on paper forms to gather information, but both public and private businesses are moving from paper forms to online electronic forms (eForms). Compared to paper-based systems, an eForms solution is faster, more efficient, easier to use, and results in better data. The World Wide Web Consortium (W3C) recommendation for eForms is eXtensible Markup Language (XML) based XForms technology.

XML is considered by many to be the most suitable technology for online data transfer and has been widely used in many industries for online business. The self-describing, flexible, and extensible aspects of XML also make it a perfect choice as the data model for electronic forms data capture and submission. XForms, powered by XML, can present and capture electronic form data in an efficient manner. XForms supports form data validation against an XML schema that can include industry standard vertical vocabularies such as ACORD, HL7, and NIEM. XForms also supports direct XML data submission to a server. Direct XML data submission eliminates the process for data format conversion before transmission. XForms is platform independent, which means one single form development can be deployed to multiple client devices. IBM® Lotus Forms provides an eForms solution based on XForms with additional presentation and security capabilities that can greatly improve the data capture process.

After data is captured by a form, an efficient method to manage the XML form data is needed. Traditionally, in a relational database, XML data has to either be treated as a large object or decomposed into relational columns. When XML data is treated as a large object in a database, any query against the data requires a full XML document retrieval instead of only retrieving the needed data, and the content of the data cannot be used to determine which documents to retrieve. On the other hand, decomposition of XML data into relational columns is a complex and costly process, and it removes the self-descriptive semantic information within the XML. The decomposition process also eliminates the flexibility and extensibility of XML. If the structure of the original XML document is changed, which is likely to happen periodically to form applications, the original mapping to relational structures will no longer be valid and will require updates to incorporate the change.

IBM addressed the need for efficiently managing XML data in relational databases by introducing the pureXML feature in IBM DB2. With DB2 pureXML, you can store XML data into, and retrieve the data from, the database in its original hierarchical structure without further processing or formatting. This enables efficient management for XML form data.

Combining DB2 pureXML and Lotus Forms provides a foundation for a simple yet effective electronic form solution.

Solution overview

The IBM Intelligent eForms solution is built on DB2 pureXML and Lotus Forms. Lotus Forms captures data on the front-end in an XML structure and DB2 pureXML manages the form XML data in the back-end. The front-end and back-end are connected with a Web service created with IBM Data Studio Developer and hosted on an application server such as IBM WebSphere® Application Server. The solution offers end-to-end XML data capture, submission, and management capability with reliable and scalable IBM software products. The solution architecture in Figure 1 shows the tools used at both development time and run time.

Figure 1: IBM Intelligent eForms solution architecture
Development time: Lotus Forms Designer, Data Studio Developer, DB2 Command Editor. Run time: Lotus Forms, Web service on WebSphere Application Server, DB2 pureXML

The following sections briefly describe the capabilities of the primary products that make up the solution. Refer to the Resources section for links to more detailed information about these products.

DB2 pureXML

The pureXML feature was first introduced in IBM DB2 Version 9. It provides the ability to natively store XML data in its hierarchical format within a relational database system. This means the XML document is stored with full database awareness of the document's internal hierarchical structure and its individual elements and attributes. With pureXML, you can efficiently store, control, 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 along with the relational data. Figure 2 depicts how with pureXML, DB2 has become a hybrid database that can access and manage relational data and XML data in the same efficient manner.

Figure 2: DB2 architecture with built-in support for relational and XML data
SQL/XML and XQuery can retrieve both relational and XML data from DB2 a database table

With the native XML storage of pureXML, you can take advantage of the flexibility of XML structures and reduce application overhead in your eForms solution. With pureXML, you can insert the complete form XML data into a single DB2 XML column while still maintaining efficient access to individual data elements. You can return whole documents or subsets of documents depending on what is needed. The XML form data stored in DB2 can be directly transmitted over an intranet or the Internet for additional processing, analysis, or information sharing without further manipulation.

Note: pureXML is available starting with Version 9 of DB2 for Linux®, UNIX®, and Windows® and DB2 for z/OS. The solution in this article uses DB2 Express-C edition Version 9.5 for Linux, UNIX, and Windows, which is free to download and use. Refer to the Resources section for a link to the download page.

Data Studio Developer

IBM Data Studio is an Eclipse-based portfolio of tools that includes a comprehensive set of features to design, develop, and manage data, databases, and data-driven applications. It provides a development and testing environment you can use to build database objects and applications. Data Studio Developer allows you to utilize SQL, SQL/XML, XQuery and Java to create and execute database queries, stored procedures, Web services, and Java data access layers. You can generate Data Web Services directly from SQL statements and stored procedures without writing a single line of application code. The Web services support both SOAP and REST bindings and you can immediately deploy them to supported application servers such as WebSphere Application Server.

This article describes how to use Data Studio Developer to create a simple Data Web Service to receive XML data from an eForm and store it directly into DB2.

WebSphere Application Server

IBM WebSphere Application Server is a Java, J2EE, and Web service based application server. It 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. The solution in this article uses WebSphere Application Server Community Edition for Web services deployment. WebSphere Application Server Community Edition is free of charge. Refer to the Resources section for a link to the download page.

Lotus Forms

IBM Lotus Forms helps you automate forms-based business processes and facilitates data integration with existing IT systems. The foundation technologies of Lotus Forms are the W3C open standard XForms and Extensible Forms Description Language (XFDL). At a high level, XForms provides an XML data model with separation of form data and appearance, and XFDL controls the form presentation layer and business logic. With Lotus Forms, you can create eForms with a paper form look and feel or with a guided question and answer style. Lotus Forms has capabilities to validate data entry based on defined criteria and to apply business logic based on data that is entered. Lotus Forms also provides digital signature support to ensure security. Lotus Forms can be deployed as client desktop software or as Web server based forms that you can access via a Web browser.

This article describes how to use Lotus Forms Designer and Lotus Forms Viewer. Lotus Forms Designer is the development tool for building XForms based electronic forms. It is built on an Eclipse platform and provides a WYSIWYG environment with advanced features for fast eForms development. Lotus Forms Viewer is the rich desktop runtime client that allows you to view, complete, and submit eForms.

Building the Solution

This section guides you through the steps to create an Electronic Forms solution. 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 - use DB2 Command Editor

  2. Create and deploy the Data Web Service - use Data Studio Developer

  3. Create the electronic form - use Lotus Forms Designer

This whole process can be completed quite quickly once you have the necessary software installed. Then all you need to do is fill in the form and submit the data using Lotus Forms Viewer. The article guides you through this as well.

Finally, the article shows you how to query against the XML form data that gets stored in DB2.

Prerequisites

Before proceeding to building the solution, you need to install and configure the following software on your system:

  • DB2 9, or later, including pureXML

  • Data Studio Developer V2.1, available as a free trial download

  • WebSphere Application Server Community Edition V2.1

  • Lotus Forms Designer V3.5

  • Lotus Forms Viewer V3.5

Refer to the Resources section for the download links and detailed installation and configuration guidelines of each product.

The article guides you through the steps to build the electronic forms solution. If you plan to create the solution on your system it would be helpful to have a basic knowledge of database systems, DB2, and SQL statements. It would also be beneficial to have a basic understanding of XML technology and Web services concepts.

Create the Database and Table to Store the Form Data

Follow the steps below to create the database and table for the eForms solution in DB2.

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

    create database eformdb using codeset utf-8 territory us
  2. Enter the following command to connect to the database:

    connect to eformdb
  3. Enter the following command to create a table. For simplicity, the table for this solution has only one column, but notice that the column has a data type of XML.

    create table eformtable (form xml)

Create and deploy the Data Web Service

Follow the steps below to create a Data Web Service with Data Studio Developer. The Data Web Service receives the XML form data and inserts it into the database. This solution only requires a simple Web service, though more sophisticated services are certainly possible.

  1. Launch Data Studio Developer.

  2. In the Data Source Explorer window, double click Database Connections > EFORMDB [DB2 Alias] to bring up its properties window (Figure 3). If you do not see the EFORMDB database in the list, right click on the Database Connections item and select New... to create the connection.

  3. In the properties window, Select IBM Data Server Driver for JDBC and SQLJ Default from the Drivers drop down menu. Enter your DB2 user name and password and check the Save password box. You can test the connection by clicking the Test Connection button. Click OK to finish.

    Figure 3: Properties for EFORMDB database connection
    Screen shot highlights: Drivers drop down, User name, Password, Save password fields and Test Connection button
  4. Right click anywhere in the Data Project Explorer window and select New > Data Development Project to bring up the New Data Development Project window. Enter eFormProject as the project name. Click Next, then select EFORMDB from the Connections list in the Select Connection screen (Figure 4), and click Finish.

    Figure 4: New Data Development Project window
    Screen shot highlights the EFORMDB connection

    Note: Information such as the project name is used in the WSDL created by Data Studio Developer for the Web service, so be aware that it is case sensitive.

  5. In the Data Project Explorer window, click the plus sign (+) to expand the eFormProject, right click eFormProject > SQL Scripts, and select New > SQL or XQuery Script to create a new SQL statement. Enter insertForm as the name (Figure 5) and click Finish.

    Figure 5: New SQL or XQuery Script window
    Screen shot highlights Name field with insertForm value entered

    Note: This solution uses the SQL and XQuery editor (default option) to create the SQL statement. You could also build the statement from the SQL Query Builder using a wizard approach.

  6. In the insertForm.sql window, enter the following script:

    insert into eformtable (form)
    values (:document)

    Close and save insertForm.sql. You should now be able to see the script listed in the SQL Scripts under eFormProject in the Data Project Explorer.

  7. In the Data Project Explorer window, right click eFormProject > Web Services and select New Web Service... to create a Web service. Enter eFormWebService as the name and eForm as the namespace URI (Figure 6). Once again, remember to be careful to use the spelling and capitalization for the values you enter. Click Finish.

    Figure 6: New Web Service window
    Screen shot with Project, Name, and Namespace URI fields filled in
  8. In the Data Project Explorer window, drag and drop the eFormProject > SQL Scripts > insertForm.sql onto eFormProject > Web Services > eFormWebService*. This creates a new operation in the eFormWebService called insertForm.

    Figure 7: Drag and drop to create operation for Web service
    Screen shot with arrow depicting insertForm.sql being dragged and dropped onto eFormProject
  9. In the Data Project Explorer window, right click eFormProject > Web Services > eFormWebService* and select Build and Deploy... to bring up the Deploy Web Service window (Figure 8). Select the Server radio button and select IBM WASCE v2.1 Server at localhost from the drop down menu. Check the box for Launch Web Services Explorer after deployment and click Finish. (This starts WASCE if it is not already started.)

    Figure 8: Deploy Web Service window
    Screen shot highlights selection for Server and selected check box for Launch Web Services Explorer after deployment
  10. In the Web Services Explorer window (Figure 9), click on the URL under WSDL Main in the Navigator pane and then scroll down in the Actions pane to select Import WSDL To File System. Save the file as eForm.wsdl on your hard drive.

    Figure 9: Web Service Explorer window
    Screen shot highlights selection of URL under WSDL Main and the Import WSDL to File System action

You have now finished creating and deploying the Web service.

Create the electronic form

Follow the steps below to create a simple electronic form.

Note that the Download section of this article contains a link to a sample form. The sample form works with the Web service you created in the previous section as long as you followed the same naming conventions described in the steps. You can use the attached sample form as an alternative to creating your own, or you can create your own and use both to see examples of a simple and complex form. The same Web service and database table work for both.

  1. Launch Lotus Forms Designer and select File > New Lotus Form... to bring up the New Lotus Form Wizard. Select A blank form (default option) and click Finish. (If a window pops up and asks if you want to switch perspective, choose No.) Select Window > Open perspective > Other..., choose Advanced Lotus Forms Designer from the list, and then click OK.

  2. In the Enclosures window (Figure 10), right click WSDL > WebServices and select Enclose WSDL File. Choose the eForm.wsdl you saved earlier on your hard drive to be attached to the form.

    Figure 10: Insert WSDL to the form
    Screen shot highlighting Enclose WSDL File selection
  3. In the Instance window (Figure 11), click on the Creates a new Instance from a WSDL message button.

    Note: The Instance window shows an instance with the name Instance: Generated. However, for this solution you create your own instance from the enclosed WSDL file so that you can submit the form to the Web service.

    Figure 11: Create instance from enclosed WSDL in the Instance window
    Screen shot highlighting the Creates a new Instance from a WSDL message button
  4. In the WSDL message window (Figure 12), check the insertFormSoapInput box and click OK. A new instance called INSTANCE is created.

    Figure 12: Select the needed operation in the WSDL Message window
    Screen shot showing selection of insertFormSoapInput
  5. Switch to Source view by clicking the Source tab at the bottom of the canvas (Figure 13). Find the xforms:instance id="INSTANCE" tag that was created from the WSDL message. As shown in Figure 13, change the child elements of the <document> element to include the following:

    <document>
        <data>
           <name>
              <firstname></firstname>
              <lastname></lastname>
           </name>
        </data>
    </document>
    Figure 13: Change the INSTANCE in Source view
    Screen shot highlighting selection of Source tab and changes to child elements of the document element
  6. Switch back to Design view by clicking the Design tab at the bottom of the canvas. In the Instance window (Figure 14), expand the Instance: INSTANCE item and then drag and drop the data element into the canvas. This automatically creates the corresponding label and entry fields for the data element and its descendants. The highlighted Refs column next to each element indicates the element is bound to an entry field on the form and the number shows how many times the element is referred to.

    Figure 14: Drag and drop element to create labels, entry fields and binding
    Screen shot indicating the data element being dragged to the canvas
  7. In the XForms window (Figure 15), right click XForms > Model: Default > instance: INSTANCE and select Create Submission for Instance. This creates a new submission called SUBMISSION, which is the procedure to submit the form data to the Web service.

    Figure 15: Create submission functionality for the form
    Screen shot highlighting selection of Create Submission for Instance
  8. Switch to Source view. Find and change the xforms:submission tag so that it looks like the following:

    <xforms:submission id="SUBMISSION" replace="none" method="post"
      action="http://localhost:8080/eFormProjecteFormWebService/Services/eFormWebService"
      mediatype="application/soap+xml action=eForm/insertForm">
    </xforms:submission>

    The value of the action attribute above is the URL of the SOAP binding for the Web service. The URL can be found in the soap:address tag within the enclosed WSDL message as shown in Figure 16.

    Figure 16: Find the soap:address in the enclosed WSDL
    Screen shot highlighting the URL of the SOAP binding for the Web service

    The action within the mediatype attribute is the name of the SOAP operation. This can be found in the soap:operation tag within the enclosed WSDL message as shown in Figure 17. Make sure that the value of action in the mediatype attribute is not surrounded by quotation marks.

    Figure 17: Find the soap:operation in the enclosed WSDL
    Screen shot highlighting soapAction value of eForm/insertForm

    Note: For a complex Web service, there may be multiple soap:operation items. Find the soap:operation that you used to create the instance. In this case, since you picked insertFormSoapInput in Figure 12 when creating the instance, you choose the eForm/insertForm operation in Figure 17.

  9. Switch back to the Design view. In the Palette window under Advanced Library (Figure 18), select Button (Submit) and click in the canvas to create a submit button.

    Figure 18: Use Palette to create a submit button
    Screen shot indicating that Button (Submit) was selected and canvas was clicked on

    You may encounter a pop up Properties dialog (Figure 19) that allows you to set the properties of the object you just created. Change the properties as you wish and click OK.

    Figure 19: Properties dialog may pop up when creating new objects
    Properties dialog for describing the submit button created in this step
  10. With the submit button selected on the canvas, expand the XForms (Submit) item in the Properties window (Figure 20). Use the drop down menu to point the submission option to the submission you created in the step above, which is SUBMISSION.

    Figure 20: Point the submit button to the submission we created
    Screen shot of Properties window highlighting the selection of the submission named SUBMISSION
  11. In the Enclosures window (Figure 21), right click WSDL > WebServices > eForm.wsdl then select Delete to delete the WSDL message from the form.

    Figure 21: Enclosed WSDL need to be deleted
    Screen shot highlighting selection of Delete for the eForm.wsdl
  12. Save the form as eForm.xfdl and close Lotus Forms Designer.

Submit eForm Data

You are now ready to use the eForm to capture information and submit data to the database via the Web service. Follow the steps below:

  1. Open the eForm.xfdl with Lotus Forms Viewer. Input some sample data and click the Submit button to send the form to the database via the Web service.

  2. Fill out and submit a few more sample forms. The query examples in the next section are based off of the sample data shown in Table 1.

  3. When you are done submitting samples, simply close Lotus Forms Viewer.

Table 1: Sample data
First NameLast NameUser IDPhone
JohnSmith6932(483)483-2838
TedWilson4823(593)583-4824
AdamSmith9583(284)099-8488
CharlesBrown4388(483)238-2273
JeffGordon7447(558)387-3112

Query eForm XML Data from the Database

After form data has been submitted, you can query the XML form data that was inserted into DB2. DB2 Version 9 supports both SQL/XML and XQuery for querying XML. This example uses a SQL/XML query. For a detailed explanation of SQL/XML and XQuery statements, see the Resources section.

Open the DB2 Command Editor window or Command Line Processor (or use Data Studio, creating a new SQL Script for this query). Input and execute the SQL/XML statements below.

  1. The following query finds the userid from all submitted forms and returns them as varchar:

    select xmlcast(xmlquery('$f/data/userid/text()'passing form as
           "f") as varchar(15)) as ID
    from eformtable

    Given the sample data from Table 1, the query result from the above statement executed in the DB2 Command Editor window is shown in Figure 22.

    Figure 22: Query result for sample query 1
    Screen shot of query result showing the IDs from Table 1
  2. The following query finds the information of all the users with a last name of Smith, and returns the data in relational format:

    select t.fname as "First Name",
           t.lname as "Last Name", 
           t.id as "User ID",
           t.phone as "Phone"
    from eformtable, xmltable('$d/data' passing form as "d" 
         columns fname varchar(20) path 'name/firstname', 
                 lname varchar(20) path 'name/lastname',
                 id varchar(20) path 'userid',
                 phone varchar(20) path 'phone') as t 
    where xmlexists('$d/data/name[lastname="Smith"]' 
          passing form as "d")

    Given the sample data from Table 1, the query result from the above statements executed in the DB2 Command Editor window is shown in Figure 23. If you did not use this same sample data, remove or change the xmlexists where predicate.

    Figure 23: Query result for sample query 2
    Screen shot of query result showing first name, last name, user id, and phone number for each entry in Table 1

Note: IBM also provides advanced business intelligence tools such as IBM DataQuant and Cognos for generating comprehensive reports with XML data in DB2. Refer to the DB2 pureXML wiki link in the Resources section for more information.

Summary

DB2 pureXML and Lotus Forms, connected by Web services, can be combined to address the need for efficient electronic forms based data collection and management. This article illustrates how an electronic forms solution can be created with minimal effort by using the three industry leading development tools from IBM: DB2 pureXML, Data Studio Developer, and Lotus Forms. The solution provides state-of-the-art technologies for eForms design and submission (Lotus Forms), Web services development and deployment (Data Studio Developer), and form XML data storage, control, and access (DB2 pureXML). The simplicity of this eForms solution is due to the fact that is based on end-to-end XML data flow: data is captured in Lotus Forms as XML, submitted to a Web service in that same XML format, and stored directly into DB2 pureXML without any intermediate conversions or manipulation. The end-to-end XML flow simplifies not only the solution development process but also the execution flow. This solution can be implemented quite simply but can greatly improve the effectiveness of information capture, management, and analysis for public and private enterprises.

Acknowledgements

The authors would like to thank those who provided input to or reviewed this article, in particular Eric Dunn who provided valuable guidance on Lotus Forms.


Download

DescriptionNameSize
A sample form using the database and Web service.HouseholdCensusSurvey.zip376KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Lotus, XML, SOA and web services
ArticleID=386254
ArticleTitle=Build an intelligent eForms solution based on DB2 pureXML, Lotus Forms, and Web services
publish-date=04302009