Editor's note: Know a lot about this topic? Want to share your expertise? Participate in the IBM Lotus software wiki program today.

Introduction

The first article in this series, "IBM Workplace Forms V2.6 integration with IBM DB2 V9," shows you how to add the submission button to a form, implement a servlet that saves the form (and its XML-based data instances) in DB2 9, and use the DB2 9 control center to verify that the form data was successfully stored. In this article, you learn how to retrieve these saved forms from the database and embed them in your JSPs to maintain the same page style in your Web application.

Also, you can download the project for part 2 and the integrated project for part 1 and part 2 so that you can read and write Lotus Form files from the database in their native format. The files available in the Downloads section of this article include these archives:

• DBExport.zip includes the DB2 information that you need to populate the table with Lotus Forms documents.
• ReadFormAppPI.zip includes the project exchange for the article, which you need so that you can read Lotus Forms documents in DB2 and display them embedded in JSP pages.
• SaveReadFormAppPI.zip includes the project exchange for this article, part 2 of the series, integrated with part 1 of the series. This file allows you to write and read the Lotus Forms documents in DB2 and display them embedded in JSP pages.

Back to top

Prerequisites

This article assumes that you are familiar with XML, DB2, Lotus Forms, and Web applications in general. It also assumes that you have completed all the integration prerequisites for part 1.

If you don't have any orders in the acme.orders table, you can do the following:

1. Extract the DBExport.zip file (available in the Downloads section of this article).
2. Put the "DBExport folder in the C:\ drive.
3. Run the import command to populate your table with a couple of orders.

To import the file, you can run the following commands from a DB2 command window:

After you run these commands, you should be able to see orders in your ACME.ORDERS table as shown in figure 1. If the data imported correctly, you can remove C:\DBExport folder.

This project was created using IBM WebSphere® Integration Developer 6.1, Lotus Forms 3.0.1, and DB2 9.1.

Back to top

Retrieving the Lotus Form document

In the DataHandler class, notice the getLotusForm(int intID) method, shown in listing 1, which passes the ordered/formid that the user wants to retrieve.

public String getLotusForm(int intID) throws Exception {

	// build the SQL query to get all the orders from the database
	String sqlQuery = "SELECT ORDERFORM FROM ACME.ORDERS where  ID = " + intID;

	System.out.println("sqlQuery: " + sqlQuery);
	FormDAO formDAO = new FormDAO();
	String strLotusForm = formDAO.getLotusFormFromDB2(sqlQuery);
	return strLotusForm;
}
	

Also, in the FormDAO class, using getLotusFormFromDB2() method, you can establish the connection to the database and retrieve the entire stored Lotus Forms document from the ORDERFORM column in the ACME.ORDERS table.

After that, you can hold the results in a string that presents the Lotus Forms document in XML format and passes it back to the servlet.

Also, you need to make sure that your XML form is formatted correctly and encoded UTF-8.

In the sample code shown in listing 2, we check and clean the XML in case the encoding is not UTF-8.

public String getLotusFormFromDB2(String sqlQuery) throws ServletException, SQLException {
	 
	Statement stmt;
	ResultSet rs = null;
	Connection conn = null;

	String result = "";

	try {
		// open the connection
		DBController dbCon = new DBController();
		conn = dbCon.getConnection();

		// execute query
		stmt = conn.createStatement(); // Create a Statement object
		rs = stmt.executeQuery(sqlQuery);

		// read result as string
		result = "";
		try {
			rs.next();
			result = rs.getString(1); // Retrieve
			rs.close(); // Close the ResultSet
			stmt.close();
		} catch (SQLException e2) {
			System.out.println("No results: " + sqlQuery);
			result = "";
		}
	} catch (SQLException e) {
		e.printStackTrace();
		result = e.toString();

		// Re-throw Exception as ServletException.
		throw new ServletException("Error in getLotusFormFromDB2 method. Error
                                              message: " + e.getMessage());
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} finally {
		// always disconnect
		conn.close();
	}
	return result;
}
	

Back to top

Embedding the Lotus Forms document in JSP

You can pass the Lotus Forms document to the JSP as an attribute to be displayed in the JSP page:

Another option, which we didn't use in our example, lets you use PrintWriter in the servlet to print the form embedded in the page. See listing 3.

response.setCharacterEncoding("utf-8");
PrintWriter out = response.getWriter();
String htmlHeader = "<HTML><BODY><OBJECT id=\"Object1\" 
height=\"900\" width=\"900\"   classid=\"CLSID:354913B2-7190-49C0-944B-1507C9125367\">
<PARAM NAME=\"XFDLID\" VALUE=\"XFDLData\"><PARAM NAME=\"detach_id\" 
VALUE=\"2507088000\"><PARAM NAME=\"retain_Viewer\" VALUE=\"on\">
<PARAM NAME=\"refresh_url\" VALUE=\"envAware.html\"><PARAM NAME=\"TTL\" 
VALUE=\"17\"></OBJECT> <SCRIPT language=\"XFDL\" id=\"XFDLData\" 
type=\"application/vnd.xfdl; wrapped=comment\"> <!-- ";
String htmlFooter = " --> </SCRIPT><P>Return to 
<A HREF=\"simpleHTML.html\">Form</A></P></BODY></HTML>";
htmlData = htmlHeader +xmlData+htmlFooter ;
System.out.println(htmlData);
out.println(htmlData);
out.close();

In displayLotusForm.jsp, you have to read the Lotus Forms attribute and embed it in your JSP as shown in the code in listing 4. Also, you can modify the object size based on your form size.

<%
	String LotusForm = (String) request.getAttribute("LotusForm");
	 
%>

<CENTER>
<TABLE border="0" cellpadding="2" width="100%">
  <TBODY>
  <TR>
  <TD><SCRIPT language="XFDL" id="XFDLData" 
  type="application/vnd.xfdl; wrapped=comment"> 
                       
	<!-- 
	<%=LotusForm %>
	--> 

	</SCRIPT> 
		 <OBJECT id="IBMLotusForm" height="700" width="85%" border="0"
		vspace="10" hspace="10" style="font-family:Helvetica"
		classid="CLSID:354913B2-7190-49C0-944B-1507C9125367">
		<PARAM NAME="XFDLID" VALUE="XFDLData">
		    <!--[if !IE]>--> 
            <OBJECT id="IBMLotusForm" height="700" width="85%"
			border="0" vspace="10" hspace="10" style="font-family:Helvetica"
			type="application/vnd.xfdl">
			<PARAM NAME="XFDLID" VALUE="XFDLData">
			</OBJECT> 
                <!--<![endif]--> 
                </OBJECT>
             </TD>

		</TR>
	</TBODY>
</TABLE>

You can include the XFDL file itself in the JSP page to maintain your navigation buttons, header, and footer. You need to add it as an include, as shown in the code snippet from PurchaseOrder.jsp in listing 5.

<TD>
<SCRIPT language="XFDL" id="XFDLData" type="application/vnd.xfdl; wrapped=comment"> 
<!--
<jsp:include page='PurchaseOrder.xfdl'/>
-->
	</SCRIPT> 
	<OBJECT id="IBMLotusForm" height="700" width="85%" border="0"
		vspace="10" hspace="10" style="font-family:Helvetica"
		classid="CLSID:354913B2-7190-49C0-944B-1507C9125367">
		<PARAM NAME="XFDLID" VALUE="XFDLData">
		<!--[if !IE]>--> 
	       <OBJECT id="IBMLotusForm" height="700" width="85%"
		border="0" vspace="10" hspace="10" style="font-family:Helvetica"
				type="application/vnd.xfdl">
				<PARAM NAME="XFDLID" VALUE="XFDLData">
		   </OBJECT> 
		<!--<![endif]--> 
	</OBJECT>
</TD>

Back to top

Running on the server

To run the ReadFormApp project which is contained in this part of the article series only, follow these steps:

1. Right-click index.html in the SaveReadFormApp folder.
2. Select Run As - Run on Server. In the index.html page that displays, you see one link, Go to orders list.

To run the integrated SaveReadFormApp project (discussed in part 1 and part 2 of this article series), do the following:

1. Right-click index.html.
2. Select Run on Server. In the index.html page that displays, you can see the three links shown in figure 2:
   
   • Create new order - lotus form embedded in jsp page
   • Create new order
   • Go to orders list

When you click Create new order - lotus form embedded in jsp page, PurchaseOrder.jsp displays the Lotus Forms document PurchaseOrder.xfdl embedded within the JSP page as shown in figure 3.

When you run the PurchaseOrder.xfdl document on the server, you get the advantage of making the URL the same as the servlet name. This equivalency means that you can install the project on the server without changing the server name or the port number on the Lotus Forms URL.

Back to top

Tips

Keep these tips in mind as you work to integrate Lotus Forms with DB2 9:

• If the XFDL file opens as XML and you have the Lotus Forms viewer installed, go to the administrative console by selecting Environment - Virtual Hosts - default host - MIME-Types. Make sure that you have selected MIME-Type application/vnd.xfdl for extensions xfdl and xfd. If not, add these values and restart the server.
• If the XFDL file doesn't open in Mozilla Firefox, then make sure that when you embed the XFDL, you define the right object.
• If you right-click the file and you don’t see the option Run on Server, select Windows - Preferences. Then select General - Capabilities - Development. Select Advanced, and then select the option Miscellaneous Eclipse Functionality.

Back to top

Conclusion

In this article, you learned how to retrieve a Lotus Forms document in its XML-based data instance structure from a DB2 database; you did that by retrieving the data from the XML field. Also, you learned how to embed the retrieved Lotus Forms document into a JSP page to be able to maintain the same look for your Web application. Finally, you learned how to open the XFDL file in a JSP page and make the form submission independent of the server name and port number. By being able to write and read the Lotus Forms document to the database as XML, you are able to design your logic to be generic and to save any Lotus Forms document to DB2 9.

Back to top

Downloads

Information about download methods

Resources

• Participate in the discussion forum.
  
  
• Read the developerWorks article, "IBM Workplace Forms V2.6 integration with IBM DB2 V9."
  
  
• Read the developerWorks® article, "Software as a service: Build a Web-delivered SaaS framework for forms and workflow-driven applications."
  
  
• Read the developerWorks article, "Create a form with IBM Workplace Forms Designer."
  
  
• IBM Lotus Forms eForms provides eForms software to speed automation of forms-based business processes and helps integrate data with existing IT systems.
  
  
• Explore DB2 9 for Linux UNIX and Windows.
  
  
• Refer to the IBM Lotus Forms support page.
  
  

About the authors

Tamer Nassar is a software engineer in the office of the IBM CIO, and has been with IBM since 2000. He has been involved in different projects, with a variety of technologies, designing, implementing, and testing many end-to-end enterprise solutions. His areas of interest and expertise include SOA, IT architecture and methodology, WebSphere Application Server, WebSphere Process Server, WebSphere MQ, and WebSphere Message Broker.

Murali Vridhachalam, an Open Group certified IT architect, has been involved with XBRL since early 2005. He was the lead architect for IBM's first ever submission of financial reports using XBRL, as part of the SEC's XBRL voluntary filing program. His current interests include SOA and Software as a Service offerings built using the IBM enterprise software portfolio.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Prerequisites
• Retrieving the Lotus Form document
• Embedding the Lotus Forms document in JSP
• Running on the server
• Tips
• Conclusion
• Downloads
• Resources
• About the authors
• 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

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers