Sunday, June 28, 2015

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){ 
     
XLog log = new XLog("Update Person" ....); 
     XyzConnectionManager cm = new XyzConnectionManager(); 
     PreparedStatement ps= null;         
     Connection c = null; 

     String returnValue = ""; 

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

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

     try{ 
         
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{ 
           
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(); 
         
if(result > 0){ 
            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()); 
           
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.

Sunday, June 21, 2015

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){ 
    XLog log = new XLog("XYZ Bean", .....); 
    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{ 
          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); 
                
          ps.setString(1, (String) viewScope.get("userFirstName")); 
          ps.setString(2, (Long) viewScope.get("userLastName")); 
          ps.setLong(3, (Long) viewScope.get("userAge")); 
          ps.setNull(4, java.sql.Types.VARCHAR); 
          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 { 
          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.