IBM Lotus Forms 3.0.1 integration with IBM DB2 9, part 2

Leveraging the XML capabilities of DB2 9 in a Lotus Forms solution

This article is the second in a series that explores the XML capabilities of IBM® DB2 9 and integrates those capabilities with IBM Lotus® Forms by looking at retrieving the Lotus Form document in its XML native format from the database and embedding it in Java™ Server Pages (JSPs).

Tamer Nassar (tamer@us.ibm.com), Software Engineer, IBM

Tamer Nassar 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 (mural@us.ibm.com), IT Architect, IBM

Murali Vridhachalam photoMurali 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.



29 June 2009 (First published 24 March 2009)

Also available in Chinese Russian

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.

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:

CONNECT TO ORDERSDB;
IMPORT FROM "C:\DBExport\Out.ixf" OF IXF METHOD N (CUSTOMERNUM, CUSTOMERNAME,
ORDERDETAIL, ORDERFORM) MESSAGES "C:\DBExport\Msg.txt" INSERT INTO ACME.ORDERS
(CUSTOMERNUM, CUSTOMERNAME, ORDERDETAIL, ORDERFORM);
CONNECT RESET;

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.

Figure 1. The ACME.ORDERS table
The ACME.ORDERS table

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


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.

Listing 1. The getLotusForm(int intID) method
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.

Listing 2. The Lotus Forms document in XML format
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;
}

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:

request.setAttribute("LotusForm", xmlData);
request.getRequestDispatcher("displayLotusForm.jsp").forward(request, response);

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.

Listing 3. The PrintWriter code snippet
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.

Listing 4. Embedding the Lotus Forms attribute in JSP
<%
	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.

Listing 5. PurchaseOrder.jsp code snippet
<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>

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
Figure 2. The index.html page
The index.html page

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.

Figure 3. PurchaseOrder.xfdl embedded in the JSP page
PurchaseOrder.xfdl embedded in the JSP page

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.


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.

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.


Downloads

DescriptionNameSize
Code sampleDBExport.zip11.4KB
Code sampleReadFormAppPI.zip16.7KB
Code sampleSaveReadFormAppPI.zip.zip34.3KB

Resources

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 IBM collaboration and social software on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Lotus, Information Management
ArticleID=377903
ArticleTitle=IBM Lotus Forms 3.0.1 integration with IBM DB2 9, part 2
publish-date=06292009