Using DB2 in XPages Part 10: The MWLUG 2015 Presentation Slides, and Thoughts

MWLUG is now a wrap. I was honored to be chosen to speak along with Dwain Wuerfel on Integrating DB2 and XPages. I am a somewhat bummed that technical troubles limited the demo I wanted to show. Thankfully as I have been told, my backup plan did get my point across. To that end, I have created additional slides to incorporate what I showed in the demo.

I have embedded the demo below, but it might be hard to read, so click here to view directly on slideshare.

The Slides

Real World Experience: Integrating DB2 with XPages from Steve_Zavocki

Youtube Video Link

You can watch the session on youtube here: https://www.youtube.com/watch?v=GVgfWzBomoM

A big thanks to David Navarre for recording the session!

Conference Review

The conference this year was as great for content and making and renewing friendships. If you have never attended an MWLUG conference I can't recommend it enough. Both times I have attended I have paid my own way, and it was money well spent. Next year the conference is going to be in Austin, Texas and they are hoping for 250 attendees. Kudos to Richard Moy and team who are wonderful organizers!

Final Thoughts

Writing this post, it occurred to me that August 23 is exactly three years since I received my first *real world* (ie: paid) XPages experience. On this day in 2012 was when I started working at Harmons Grocery in Salt Lake City, Utah. Harmons was a great experience, and since then I have been further challenged working at Navy Federal Credit Union in Florida. I have very much enjoyed the transition from Notes Developer to a Web Application Developer. I am excited to see what the next three years brings, whether that is XPages, Java, or the latest Javascript frameworks; ideally all of the above and more.

Using DB2 in XPages Part 9: Access DB2 from a Java Agent

Unless I think of something else worthy of writing about, this will be my final post in this blog series on DB2 and XPages. I am grateful and honored by the compliments that have come my way with regards to this series. One of the main reasons I blog is so that I can remember the work that I have done, and so that I will be able to do it quicker and more effective in future. The other main reason is that I also enjoy sharing information with others, especially since I have greatly benefited from other community bloggers.

I also wanted to give another plug for the MWLUG conference this August 19-21, 2015 in Atlanta. There some amazing sessions being presents by the top names in our community. I am humbled and honored to be included on that list. Along with my co-worker Dwain Weurfel, I will be presenting a session titled "Real World Experience: Integrating XPages and DB2". Come hear us present on topics that are included in this blog series and much more. 

In this post, my topic is accessing DB2 data using a Java agent.

How are Java Agents Different

Java agents are different and those differences can be very frustrating. I remember seeing this tweet and it reminded me that I needed to get busy and write this blog post.

@ptcalhoun Same here this week - Java agents suck. DO NOT USE them

— Oliver Busse ✫ (@zeromancer1972) July 21, 2015

In our case, we had to use them for scheduled processes that needed to run on the server. It was use either Java or LotusScript.

To access DB2, it is necessary to access the drivers provided by IBM or your DBA. To have your agent code reference the drivers and any other jars is is necessary to understand the restrictions that apply to java agents. The list below is not meant to be exhaustive:

Java Agents Can:

1. Access Jar files stored in the servers jvm\lib\ext folder
2. Access Script Libraries containing supporting classes

Java Agents Cannot:  

1. Access Jars stored in the NSF's Build Path
2. Access any Jar using the new 'Jar' design element
3. Access any java code stored in the Code\Java section

Using External Jars and Script Libraries

Java agents can access the jars on the server but you MUST also have a copy of the jars on your local jvm\lib\ext folder in order to compile your code.

To use a supporting script library in your java agent, you first create a new library of type 'Java'. You then put whatever supporting classes inside that you need. Once created, you then can import the library into your java agent using the 'Import' button. In this case in the graphic, there are only two classes; I could have easily just imported those two classes individually into my agent.

Script Library

Use Import to use the Script Library in your agent

Finally, the last thing you need to do in order to use the classes is to add an import statement in your agent.  

import org.sample.javaLib.XLog; 
import org.sample.javaLib.AgentUtils;

Code Usage

The code in this example runs on a scheduled basis to update a table column based on a condition.

Code Example using footnotes

package org.sample.agent; 

import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.text.DateFormat; 
import java.text.SimpleDateFormat; 

import lotus.domino.AgentBase; 
import lotus.domino.Database; 
import lotus.domino.Document; 
import lotus.domino.Session; 
import lotus.domino.View; 

1 import org.sample.javaLib.XLog; 
import org.sample.javaLib.AgentUtils; 

public class JavaAgent extends AgentBase { 

@Override 
public void NotesMain() { 
   Database db_cred = null; 
   Connection conn = null; 
   PreparedStatement ps1 = null, ps2 = null; 
   ResultSet rs = null; 
   XLog log = null; 
   Session session = getSession(); 

   try { 
     //get values from keyword documents 
     2 String schema = session.getCurrentDatabase().getView("Keywords").getDocumentByKey("SCHEMA").getItemValue("KeywordValues").elementAt(0).toString().trim();

    ...Rest of code to pull keywords removed...                    

     //Initialize log 
     log = new XLog("My App", agentDebugServer, agentLogDB, agentDebugLevel); 
     log.info("Agent processPendingSettlementAmount: Starting execution of agent"); 

     db_cred = session.getDatabase(agentDebugServer, dbpath); 

     if (3 db_cred.isOpen()) { 

        View v = db_pass.getView(dbview); 
        Document doc = v.getDocumentByKey(dbkey, true); 
        String url = doc.getItemValueString("PI_JDBCURL").trim(); 
        String user = doc.getItemValueString("PI_Username").trim(); 
        String password = doc.getItemValueString("PI_Password").trim(); 
        4 doc.recycle(); 

        v.recycle(); 
     }
     Class.forName(driver); 
     5 conn = DriverManager.getConnection(url, user, password); 

     if (conn != null) { 
        conn.setAutoCommit(false); 
        java.util.Date currentDate = new java.util.Date(); 
        6 java.sql.Date emptyDate = java.sql.Date.valueOf("9999-12-31"); 

     // Format Posting Date 
     DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
     currentDate = formatter.parse(formatter.format(currentDate));                                                              

     java.sql.Date currentDateSQL = new

     java.sql.Date(currentDate.getTime()); // convert to SQL date 

     7 String sqlSelect = "SELECT USER_ID, USER_TAX_PAID, USER_TAX_DUE_AMT, " + 

"USER_TAX_DUE_AMT FROM " + schema + ".USER_DETAIL" + 

"WHERE USER_TAX_DUE_AMT > 0 AND USER_TAX_DUE_DT <= ?"; 

    String sqlUpdate = "UPDATE USER_DETAIL SET USER_TAX_PAID = ?, USER_TAX_DUE_AMT = ?, USER_TAX_DUE_DT = ? WHERE USER_ID = ?"; 

    ps1 = conn.prepareStatement(sqlSelect); 
    ps1.setDate(1, currentDateSQL); 
    rs = ps1.executeQuery(); 

    while (rs.next()) { 
       ps2 = conn.prepareStatement(sqlUpdate); 
       8 ps2.setString(1, rs.getString("USER_TAX_DUE_AMT").trim()); 

       ps2.setInt(2, 0); 
       ps2.setDate(3, emptyDate); 
       ps2.setString(4, rs.getString("USER_ID").trim()); 
       ps2.executeUpdate();

       9 conn.commit();

     }
                                                     

      log.info("Agent processTax: Process completed successfully"); 
     } else { 
        log.error("Agent processTax: Connection to DB2 could not be obtained"); 
     } 
    } else { 
     log.error("Agent processTax: Unable to open DBCRED document"); 
    } 

   } 

   catch (Exception e) { 

      //log errors 
   } 
   finally { 
      //clean up connections                 
   } 
}

Code Explanation using footnotes

1  - Code imported from Script Library that we previously imported into the agent.

2  - In previous post, the example code pulled from a KeywordBean in applicationScope.  In a java agent, this bean is unavailable therefore each agent must load the keywords that are needed.

3 - Since the Credentials class (discussed in previous post) is unavailable in an agent, the agent manually retrieves the values from the Credential Notes database.

4 -  Of course, all Notes objects must be recycled

5 - Gets the connection from the standard java.sql.DriverManager class, and passes the credentials it had retrieved from the Credentials database. This agent runs on the server so credentials are never passed to or from the server, therefore this is considered secure by company standards.

6 -  Early on in the project, the DBA set all columns to "Not Null" so we had to put a dummy date of "9999-12-31" in a column to signify that it was 'empty'.  Bottom line here: Don't let your DBA get away with doing that unless you really need it. Know what you need the table to look like prior to starting coding.

7 - There are two distinct queries that need to be run within this agent

8 - Shows how the second query is build with values pulled from the first query. The first being a SELECT that returns a ResultSet object.

9 - After each iteration in the while statement, we must manually commit the changes to DB2

Note: The agent code above was heavily modified and stripped down from the original. The purpose was also changed to something more understandable. I wouldn't be surprised if all my curly braces don't line up properly.

Using DB2 in XPages Part 8: Updating an Existing Record into DB2

As this blog series is winding down, in this second to last post, I will give an example for performing an UPDATE to an existing DB2 record.

Example Overview

The UPDATE SQL is very straightforward. In my opinion, the UPDATE statement makes much more sense that the INSERT statement. 

For updates, you only need to update the columns that you want changed. You do not need to include columns that you don't want to be updated. Of course, you need to have the correct DB2 permissions to perform updates. For updating a specific record, the WHERE is required.  

Code Usage

This code is intended to be called upon an XPages event such as the onClick of a button, or from another java method,  You would keep this in your controller managed bean and call it like this:

var updateResult = appBean.updatePerson(viewScope.personID, keywordBean.getString("SCHEMA"));

Code Example with footnotes

public String updatePerson(String personID, String schema){ 
     1 XLog log = new XLog("Update Person" ....); 
     2 XyzConnectionManager cm = new XyzConnectionManager(); 
     PreparedStatement ps= null;         
     Connection c = null; 

     String returnValue = ""; 

     3 FacesContext facesContext = FacesContext.getCurrentInstance(); 
     Map<String, Object> viewScope = facesContext.getViewRoot().getViewMap(); 

     4 SimpleDateFormat formatDateDB2 = new SimpleDateFormat("yyyy-MM-dd"); //to DB2 format 

     try{ 
         5 c = cm.getConnection(); 

         //SQL for Person Table UPDATE 
         String sql = "UPDATE " + schema + ".PERSON_TABLE SET PERSON_ID = ?, " + 
         PERSON_AGE = ?, PERSON_NICKNAME = ?, PERSON_FAV_COLOR = ?, " +
         PERSON_DOB = ? WHERE PERSON_ID = ?";
                
         try{ 
           6 java.util.Date DOB_Date = formatDateDB2.parse((String)viewScope.get("DOB_UI")); 
         } catch (ParseException e){ 
            //maybe log parse error here
         } 
                                        
         ps = c.prepareStatement(sql);  //prepared statement for detail table 
         ps.setString(1, (String) viewScope.get("personID")); 
         ps.setLong(2, (Long) viewScope.get(personAge")); 
         ps.setString(3, (String) viewScope.get("personNickname")); 
         ps.setString(4, (String) viewScope.get("personFavoriteColor")); 
         ps.setDate(5, DOB_Date); 

         int result = ps.executeUpdate(); 
         7 if(result > 0){ 
            8 c.commit(); 
            returnValue = "Success"; 
         }else{ 
            returnValue = "Failure"; 
         } 
    }

    catch(Exception e) { 
    log.error("EXCEPTION in updatePerson: " + e.toString());  
    } 
    finally { 
        try { // close resources 
        c.close(); 
        } catch (SQLException e) { 
           log.error("EXCEPTION in closing out resources in updatePerson: " + e.toString()); 
           9 throw new RuntimeException(e); 
          } 
        } 
     10 return returnValue; 
} 

Code Explanation using footnotes

1  - Creates new instance of our logging utility. We had problems creating a single instance, so we resorted to creating a new instance each time.

2  - Creates new instance of our connection manager class, see Part 3 for a detailed explanation of this class

3 -  Gets a handle to the viewScope in which the UI elements are bound.

4 -  Converts the date to a DB2 friendly date. Later I found out that you don't need to do this, as the setDate() method of the PreparedStatement class will do this for you. Early on in the project, the DBA set all columns to "Not Null" so we had to put a dummy date of "9999-12-31" in column.  We used this date converter which we thought we needed.  Bottom line here: Don't let your DBA get away with doing that unless you really need it. Know what you need the table to look like prior to starting coding.

5 - Creates a new connection or grabs one from the pool. This method is explained in Part 3 of this series.

6 - Parse and format the date.  This step is not necessary, but I left this to make the point I made in Footnote #4.  Come to our MWLUG session in Atlanta to hear more about lessons such as this one that we learned while working on this project.

7 - An Integer with the number of rows updated is returned. To check for success, we check whether the returned integer is greater than one.

8 - Our table (DB2 view actually) is set so that we have to manually commit on updates. Commit means to make the update persistent.

9 - The finally statement always runs, and is the recommended place to close out resources. If that operation fails then we throw the exception. There is some debate on whether it is best to throw an exception at this point.  

10 - Lastly we return back a String of success or failure. Error Handling is done in the calling method.

Using DB2 in XPages Part 7: Inserting a New Record into DB2

My apologies for the long break in my blog series. The project that this is the basis for this blog series is wrapping up which is causing work to be extremely busy. 

Before I get started I am excited to report that I will be co-presenting a session at the MWLUG conference in August titled Real World Experience: Integrating DB2 and XPages. The session will cover some of the material in this blog series as well a much more. I will be presenting with my coworker Dwain Wuerfel who has worked on two separate XPages projects using a DB2 backend. Come see us in Atlanta this August 19-21.

Now onto this post in which I will cover how to Insert a new record into DB2.

Example Overview

The code to perform an INSERT is fairly straightforward. However I personally find The INSERT statement in SQL to be somewhat awkward because of the way it must contain a VALUES statement that pairs with the column values that precede it. I find it easy to get values mixed up especially when you have a multitude of columns.

After you execute an INSERT query, it does not return a boolean success or failure like the SELECT. It simply returns an integer of how many rows were updated. A return value of zero would indicate that something went wrong. 

Code Usage

This code is intended to be called upon an XPages event like the onClick of a button,  You would keep this in your controller managed bean and call it like this:

var insertResult = appBean.insertExample(keywordBean.getString("SCHEMA"));

Code Example with footnotes

public String insertExample(String schema){ 
    1 XLog log = new XLog("XYZ Bean", .....); 

    2 XYZConnectionManager cm = new XYZConnectionManager(); 

    PreparedStatement ps = null; 
        
    Connection c = null; 
    String returnValue = ""; 
                
    3 FacesContext facesContext = FacesContext.getCurrentInstance(); 

    Map<String, Object> viewScope = facesContext.getViewRoot().getViewMap(); 

    try{ 
          4 c = cm.getConnection(); 

          String sql = "INSERT INTO " + schema + ".PERSON_TABLE " + 
          "(FIRST_NAME, LAST_NAME, AGE, BIG_BAG_OF_NOTHING, BIRTHDAY)" + //1,2,3,4,5 
          "VALUES (?, ?, ?, ?, ?)"; 

          ps = c.prepareStatement(sql); 
                
          5 ps.setString(1, (String) viewScope.get("userFirstName")); 

          ps.setString(2, (Long) viewScope.get("userLastName")); 
          6 ps.setLong(3, (Long) viewScope.get("userAge")); 

          7 ps.setNull(4, java.sql.Types.VARCHAR); 

          8 ps.setDate(5, (Date) viewScope.get("userDOB")); 

                        
          int result = ps.executeUpdate(); 

          if(result > 0){ 
                returnValue = "Success"; 
          }else { 
                returnValue = "Failure"; 
          } 
        
      } catch(Exception e) { 
          log.error("EXCEPTION in insertATMInfo: " + e.toString());

      } finally { 
          9 try { // close resources 

              ps.close();                                 
              c.close(); 
          } catch (SQLException e) { 
              log.error("FATAL EXCEPTION in closing out resources " + e.toString()); 
             throw new RuntimeException(e); 
          } 
       } 
   10 return returnValue;         

} 

Code Explanation using footnotes

1  - Creates new instance of our logging utility. We had problems creating a single instance, so we resorted to creating a new instance each time.

2  - Creates new instance of our connection manager class, see Part 3 for a detailed explanation of this class

3 -  Gets a handle to the viewScope in which the UI elements are bound.

4 -  Creates a new connection or grabs one from the pool. This method is explained in Part 3 of this series.

5 - The setString method of the Prepared Statement accepts a String and writes a VARCHAR or a LONGVARCHAR depending on the size of the String

6 - The setLong method of the Prepared Statement accepts a long  and writes a BIGINT into DB2.

7 - The setNull method of the Prepared Statement is useful for writing null to a column that is set to accept a null.  You have to include the type of value so that the method knows which type of null to write.

8 - The setDate method of the Prepared Statement accepts any kinds of Date (util or sql) and writes it to a Sql DATE type.

9 - The finally statement always runs, and is the recommended place to close out resources. If that operation fails then we throw the exception. There is some debate on whether it is best to throw an exception at this point.  

10 - Lastly we return back a String of success or failure. Error Handling is done in the calling method.