Traditionally, relational DBMSs stored XML data as a single column-row entity (such as CLOB, BLOB, or VARCHAR) or decomposed ("shred") the XML data into multiple columns. These methods presented problems for many applications, including increased development and query complexity, difficulties in evolving schemas to address new business needs, and poor runtime performance of subdocument queries. By storing the XML data natively in the database, users can now take full advantage of new XML query capabilities, leverage the robust indexing and data storage capabilities of the DBMS, and allow for growth and changes in the XML schema.

IBM Workplace Forms documents are entirely described in XML, and saving these forms in a database capable of managing XML in its native form is a natural match. The overall architecture described in this article is depicted in figure 1. The form includes a submit button that performs an HTTP post to a backend servlet. The servlet uses the IBM Workplace Forms Server APIs to extract data, manipulate the form, or both. In addition, any other business logic can also be performed, such as placing the form on a workflow process. Last, the servlet uses the IBM DB2 JDBC APIs to store the form in IBM DB2 V9.

The remainder of this article is divided into a series of steps that describes how to:

• Set up integration prerequisites
• Add the submission button to the form
• Implement a servlet that saves the form (and its XML-based data instances) in DB2 V9
• Use the DB2 V9 Control Center to verify that the form data was successfully stored

Integration Prerequisites

To run the sample servlet, the Workplace Forms Server APIs must be installed and configured. These APIs are used by the servlet to extract data from the form. While this may be done using basic XML parsers, using the Workplace Forms Server APIs adds many benefits, some of which include less code, handling of digital signatures, and the ability to work with compressed form data.

It is important to note that the JAVA APIs make Java Native Interface (JNI) calls to native libraries (that is, DLLs). To find these DLL files, be sure the "C:\Program Files\ibm\Workplace Forms\Server\2.6\API\redist\msc32" directory is in your system path. In addition, during runtime, the APIs look for a file named PureEdgeAPI.ini, which determines which version of the API is used. Create a file named PureEdgeAPI.ini in the C:\Windows directory, with the following contents:

[API]
* = C:\Program Files\IBM\Workplace Forms\Server\2.6\API\redist\msc32\PureEdge\70

Further details on installing and configuring the Workplace Forms Server APIs can be found in the Workplace Forms Server API Installation and Setup Guide.

DB2 V9 must be installed, and the database that stores the forms must be created. (DB2 Express-C is available for free download.) If you are using the sample provided with this article, run the following commands from a DB2 command window:

1. Create db ordersdb using codeset utf-8 territory us.
2. Connect to ordersdb.
3. Create schema acme.
4. Create table acme.orders [ID int primary key generated always as identity (start with 0 increment by 1), CUSTOMERNUM VARCHAR(64), CUSTOMERNAME VARCHAR(64), ORDERDETAIL XML, ORDERFORM XML].
5. Disconnect ordersdb.

This set of commands creates a database that consists of a single table. Each row of the table contains both traditional relational data and the newer XML-based data.

Last, ito learn how the integration servlet was designed, IBM Rational Application Developer for WebSphere Software is used. In addition, the servlet is run within the IBM WebSphere Application Server V6.0 Test Environment with Rational Application Developer. You can obtain a free trial download of Rational Application Developer.

Back to top

Add the submission button to the form

A mechanism must exist by which a completed form can be submitted for processing and storage. One such method for doing so is a simple XFDL button that submits the entire form to a servlet. (Another method is to use an XForms button, which allows only the data instance to be submitted.) For this example, we are interested in saving the form in its entirety. To demonstrate the submission process, we use a sample Purchase Order form, which you can download at the end of this article. (Note that the process of adding a submission button can also be applied to your own custom form.) Designed with the IBM Workplace Forms Designer, the sample Purchase Order form contains simple text fields for capturing user data (see figure 2).

To update the Purchase Order form, start Workplace Forms Designer. Next, create a new project by clicking File - New - Project. Choose to create a simple project and name it FormsDB2V9. Now that a project has been created, import the sample Purchase Order form by following these steps:

1. Choose File - Import.
2. Select File System and click Next.
3. Browse and select the file named PurchaseOrder.xfdl.
4. Specify FormsDB2V9 as the folder to import the form into.
5. Click Finish.

After importing the form into the project, double-click the form to open it in the Designer Perspective. From here, you can add an XFDL Submit Button to the form as shown in figure 3. After placing the button on the form, right-click it and choose Convert Items - XFDL to ensure the button is of the proper type.

The properties of the button should be set according to the values depicted in table 1:

After you add and configure the submission button, export the form by right-clicking it and choosing Export from the pop-up menu. Select File System as the export destination, and then follow the remaining prompts to save the updated form to a directory of your choice. The newly added submission button should now appear when the form is opened in IBM Workplace Forms Viewer. As we have not yet implemented the servlet, though, the button does not function yet.

Back to top

Servlet implementation

Now that the sample Purchase Order form has been updated to include a submission button, let's focus on the servlet that receives the form submission. You can develop the servlet code in an IDE of your choice, although the steps in this article are specifically for Rational Application Developer. To learn how the servlet operates, import the provided Web application resource (WAR) file into a new project. From Rational Application Developer, switch to the Web Perspective (Windows - Open Perspective) and complete the following steps to import the provided sample:

1. Choose File - Import.
2. Select WAR file and click Next.
3. Specify the WAR file SaveFormApp.war (available at the end of this article).
4. Select WebSphere Application Server V6.0 as the target server.
5. Leave all other values/options as is and click Finish. The project should appear similar to the one shown in figure 4.

The imported Web application contains two class files: SaveForm.java and DBController.java. The SaveForm class is the servlet class, and it performs the functions of receiving the form and using the Workplace Forms Server APIs to extract form data. The DBController class is called by SaveForm, and it uses the DB2 JDBC APIs to store the form data values and the form into DB2. Although the sample is complete, it is a worthwhile exercise to browse through specific sections of the implementation. Open the SaveForm servlet class and find the init() method (listing 1). This method is called when the servlet is first initialized by the container. Notice that the Workplace Forms Server API is initialized here by a call to the DTK.initialize() method.

public void init(ServletConfig config) throws ServletException
{
	try
	{
		// Initialize the API
		DTK.initialize("SaveFormApp", "1.0.0", "7.0.0");
	}
	catch (Exception e)
	{
		System.out.println(e.getMessage());
		e.printStackTrace();
	}
}

Next, observe the doPost() method (listing 2), which is called when a form is submitted to the servlet (that is, when the user clicks the submission button that was previously added to the form). Notice that the saveForm() method is called to save the form to the database. This method contains the logic to pull the necessary instance data from the form, and then have it saved to the database.

protected void doPost(HttpServletRequest req, HttpServletResponse resp) 
	throws ServletException, IOException 
{
	try
	{
		System.out.println("Received request to save form.");
		
		// Call method to save form
		saveForm(req); 
		
		// If no errors thrown, then return success message
		resp.getWriter().println("PO succesfully submitted.");
	}
	catch (Exception e)
	{
		// Error was thrown, so return error message
		resp.getWriter().println("Error: " + e.getMessage());
	}		
}

Although it is possible to store the entire form as a single XML structure within a table, in some circumstances, you might choose to store the primary form values in their own columns alongside the XML-based form. The sample provided with this article does just that: The customer ID, customer name, and order items are stored as values in their own columns. The form is stored in a fourth column. This provides a way to easily access the most commonly used data in a purely relational way, but it gives the ability to query and work with the remaining XML structured data as well.

The main task of extracting data from the form and directing it to be stored in the database is handled in the saveForm() method (listing 3). Note that the form itself is nothing but an XML-based document and can be manipulated with the standard Java-based XML parsers. As previously mentioned, using the Workplace Forms Server APIs to work with the form has certain advantages and is the recommended method when working with Workplace Forms forms. The Workplace Forms Server APIs provide a wrapper around the Java-based XML parsers, thus reducing the amount of code. Manipulating form fields and values, working with digital signatures, and handling binary attachments are just some of the added functions provided by the APIs. In addition, if the form was submitted in compressed format (an option available to you), the Workplace Forms Server APIs transparently handles the decompressing of the form data.

The use of the Workplace Forms APIs to obtain the form from the request object and extract field values from the form is depicted in listing 3. The extractXFormsInstance() method of the FormNodeP object is used a number of times to extract specific data from the form. The first parameter of this method is the ID of the model to extract. Because this example uses the default model, the value null is specified. The second parameter points to the data instance item (that is, custnumb in the data instance named INSTANCE1) that is to be extracted. The third parameter determines if non-relevant instance data is also extracted. The fourth parameter determines if constraint or validation errors are ignored. The fifth parameter of null specifies that the node this method is operating on has inherited the necessary namespaces. The last parameter represents the output stream, which holds or stores the extracted data. It is also important to note the use of the destroy() method. As the APIs use the JNI to access underlying native libraries, the servlet must explicitly use the destroy() method to clean up used resources to avoid memory leaks.

It is important to note that the sample servlet passes the database user ID and password as parameters when the DBController class object is instantiated. In this example, the user ID and password are both set to db2admin. To run the sample servlet successfully, you may need to update these values to match your environment.

private void saveForm(HttpServletRequest req)
{
	FormNodeP theForm = null;
	ByteArrayOutputStream stream = new ByteArrayOutputStream();	
	
	try
	{
		// Obtain the XFDL object from the IFSSingleton static object
		XFDL theXFDL = IFSSingleton.getXFDL();
		
		// Read the form from the HTTP request inputstream
		theForm = theXFDL.readForm(req.getInputStream(), 0);
			
		// Extract the customer num value from the form. getValue()
		// is a private method that strips the < and > signs.
		stream.reset();
		theForm.extractXFormsInstance(null, 
			"instance('INSTANCE1')/customer/custnumb", 	false, false, null, stream);
		String customerNum = getValue( stream.toString() );
		System.out.println("customerNum=" + customerNum);
			
		// Extract the customer name value from the form
		stream.reset();
		theForm.extractXFormsInstance(null, 
			"instance('INSTANCE1')/customer/custname", false, false, null, stream);
		String customerName = getValue( stream.toString() );
		System.out.println("customerName=" + customerName);
			
		// Extract the order details from the form. This is the entire xml
		// structure for data instance INSTANCE2 and corresponds to the
		// items items selected in the order table.
		stream.reset();
		theForm.extractXFormsInstance(null, 
			"instance('INSTANCE2')", false, false, null, stream);
		String orderDetail = stream.toString();	
		System.out.println("orderDetail=\n" + orderDetail);
			
		// Store the entire form as a variable so we can also store it into the database along
		// with the metadata collected above. This also is an xml data structure.
		stream.reset();
		theForm.writeForm(stream, null, 0);
		String form = stream.toString();			
		
		// Use the DBController class to store the customer id, customer name, order details,
		// and form into the database. Note the order details and form are stored in their
		// native XML format into the database.
		DBController dbController = new DBController("db2admin", "db2admin", "ordersdb");
		dbController.createOrder(customerNum, customerName, orderDetail, form);			
	}
	catch (Exception e)
	{
		System.out.println(e.getMessage());
		e.printStackTrace();
	}
	finally
	{
		// The finally block of code is called right before this method exists
		// (whether of not an error is thrown). In here we call the XFDL.destroy()
		// method to clean up resources. IF YOU FORGET TO CALL THIS METHOD THEN
		// A MEMORY LEAK WILL OCCUR BECAUSE UNDERLYING NATIVE LIBRARIES WILL
		// NEVER RELEASE THE MEMORY ALLOCATIONS MADE!!!
		
		if ( theForm != null )
		{
			try 
			{
				theForm.destroy();
			} 
			catch (Exception e) 
			{
				System.out.println(e.getMessage());
				e.printStackTrace();
			}
		}
	}		
}	

The remaining section of code that is of significance in this implementation is DBController.createOrder() method (shown in listing 4). After extracting the form data, the sameForm() method calls the DBController.createOder() method to persist the form to the database. If you've worked with the JDBC API in the past, you are familiar with this snippet of code. Note two important differences: The first is the use of the xmlparse function in the SQL command, which is used to perform explicit parsing of data for XML storage. The second is the preserve whitespace option, which preserves the boundary whitespace characters found in the XML document. Forgetting to include this option would cause the form to be altered (that is, boundary whitespace characters removed) before being stored in the database. In doing so, if the form had been signed, it would no longer be valid.

public void createOrder(String customerNum, String customerName, 
	String orderDetail, String theForm) throws Exception
{		
	Connection conn = null;
	
	try
	{
		conn = getConnection();
							
		PreparedStatement pstmt = conn.prepareStatement("INSERT INTO 
			ACME.ORDERS (CUSTOMERNUM, CUSTOMERNAME, ORDERDETAIL, ORDERFORM) 
			VALUES (?, ?, xmlparse(document cast(? as clob(5k)) preserve whitespace), 
			xmlparse(document cast(? as clob(512k)) preserve whitespace))");
	
		pstmt.setString(1, customerNum );
		pstmt.setString(2, customerName );
		pstmt.setString(3, orderDetail );						
		pstmt.setString(4, theForm );
			
		int rc = pstmt.executeUpdate();
			
		pstmt.close();
	}
	catch (Exception e)
	{			
		System.out.println("Error storing form in DB. Msg=" + e.getMessage());		
		throw new Exception(e.getMessage());
	}	
	finally 
	{
		try 
		{
			if (conn!=null) conn.close();
		}
		catch (Exception e) {}
	}
}

Back to top

Starting the servlet

To test and develop the servlet, use the WebSphere Application Server V6.0 Test Environment. Doing so allows you to quickly change and redeploy the servlet code. In addition, breakpoints can be created in the code to allow a debugger to be used. From within Rational Application Developer, create a server configuration for this project by completing the following steps:

1. Choose File - New - Other, and then choose Server - Server and click Next.
2. Select Websphere v6.0 Server as the server type and click Next.
3. Click Next to accept the default server port and settings.
4. At the "Add and Remove Projects" screen, select the project you imported the WAR file into (SaveFormAppEAR) and click the Add button to move it to the list of configured projects.
5. Click Finish to save the new server configuration.

After creating a server configuration, it must be started. To do this, go to the Servers View (Window - Show View - Servers). Select the new server you created, and then click the Start button as shown in figure 5.

To view the server console, click the tab named Console (Window - Show View - Console). From here, you see the progress of the server start. In addition, any errors/messages that the integration servlet outputs are displayed here.

In order for the servlet to succesfully use the DB2 JDBC APIs, the application server classpath must be updated. Failing to do so results in an exception as the COM.ibm.db2.jdbc.app.DB2Driver class would not be located. After you start the server, perform the following steps to use the WebSphere Application Server Admin Console to update the classpath:

1. Open a browser and go to http://localhost:9060/ibm/console.
2. Log in as admin.
3. Expand Servers - Application Servers and select server1.
4. Expand the section named Java and Process Management.
5. Navigate to Process Definition - Java Virtual Machine.
6. Locate the property field named classpath, and then enter the location to the db2java.zip file (C:/Program Files/ibm/SQLLIB/java/db2java.zip).
7. Click OK. Click the Save link at the top of the screen to save the changes persistently.
8. Restart the server to update the classpath. To do this, go back to the Servers View (Windows - Show View - Servers). From here you can stop and then start the application server.

Back to top

Saving the form

After the servlet is running, the integration is ready to be tested. You do this by opening the PurchaseOrder file you previously updated and exported from Workplace Forms Designer to the file system. After completing the form, click the Submit button. At this point, the viewer performs an HTTP post to the URL specified in the submit action, which in this example is http://localhost:9080/SaveFormApp/SaveForm. Before storing the form in the database, the servlet outputs the user data to the console as shown in figure 6.

If an error similar to the following is thrown, be sure the directory containing the pe_cc.dll file is in your system path:

SRVE0100E: Did not realize init() exception thrown by servlet SaveForm:
java.lang.UnsatisfiedLinkError: Can't find library pe_cc (pe_cc.dll) in
sun.boot.library.path or java.library.path

You can verify that the data was saved into DB2 by using the DB2 Control Center or issuing queries from the DB2 command line. To use the DB2 Control Center, perform the following steps:

1. Start the DB2 Control Center.
2. Navigate to All Databases - ORDERSDB - Tables.
3. Right-click the table named ORDERS and select Open. From here, you see a row of data containing both traditional data and XML-typed data. The two XML-typed columns contain the orders table and the entire form.
4. Click the button labeled "..." in the column named ORDERDETAIL to see the XML data instance stored; see figure 7.

In addition, you can run simple queries from a DB2 Command Window to test how the form data was stored. Table 2 describes some sample queries.

Back to top

Summary

In this article, you learned how IBM Workplace Forms can be integrated with IBM DB2 V9. More specifically, you saved a form into a DB2 V9 database, preserving its XML-based data instance structure. You did this by adding a submit button to the Purchase Order form that, when pressed, submitted the entire form to a servlet. You then set up a development environment in IBM Rational Application Developer to test the sample integration servlet. This servlet used the IBM Workplace Forms Server APIs to extract data from the form. It then used JDBC APIs to save the data and the form into the database. Last, you used the DB2 Control Center to verify that the form was indeed saved into the database and to confirm that the XML hierarchy was conserved.

You can improve the integration servlet described in this article by adding the necessary code to place the submitted form onto a workflow process. In addition, you can use the IBM Workplace Forms API to update the form with a unique ID before storing the form. In doing so, if the form is later retrieved from the database, updated, and resubmitted, the servlet can update the respective row in the database instead of generating a new row.

Back to top

Downloads

Information about download methods

Resources

Learn

• Read the developerWorks article, "Integrating IBM Workplace Forms with WebSphere Portal to create a form-centric application."
  
  
• Read the developerWorks article, "Extending the functionality of IBM Workplace Forms with the Function Call Interface."
  
  
• Read the developerWorks article, "Create a form with IBM Workplace Forms Designer."
  
  
• "Enable C++ applications for Web services using XML-RPC" is a step-by-step guide to exposing C++ methods as services.
  
  

Get products and technologies

• Download a trial version of IBM Workplace Forms V2.6 Viewer and Designer.
  
  
• Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2, Lotus, Rational, Tivoli, and WebSphere.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out John Boyer's blog, Workplace Forms and the Next Generation Web Applications.
  
  

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Integration Prerequisites
• Add Submission Button
• Servlet implementation
• Starting the servlet
• Saving the form
• Summary
• Downloads
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Lotus on developerWorks

Calling all developers

Download no charge Lotus Domino Designer

Special offers