New Blog Series: Using a DB2 Datasource in XPages

This post is the introduction of a blog series on using a DB2 backend datasource in XPages.  I will discuss how we set up the connections and give examples of different types of queries. There should be very little here specific to DB2, but for ease of writing, I will write 'DB2' throughout this series. Feel free to substitute any relational database, such as Oracle, MySQL, SQL Server, etc.

Background

Why did we choose to use a DB2 backend?  It is complicated... but the real reason we chose to use DB2 is that someone at my company believed that Notes was not secure enough for storing sensitive data.  Most everyone who reads this knows that this is far from the truth.  Notes is extremely secure and its security model is looked at as one of it biggest strengths.

When presented with this assertion, I did my best to present arguments against using DB2. I knew the security premise was wrong, and I knew it would dramatically increase project completion time. Lastly, I knew Notes was well suited for the project at hand.

Even though I fought hard for what I felt was right, I was not very disappointed when I lost the battle. The reason being that I knew that using a relational backend would be a fun learning experience and good for the career. In my last job, I had experience retrieving Oracle data through a java agent, but I never bound an XPage to relational data real time.

Around this time, I went to the MWLUG conference in Grand Rapids and heard an excellent presentation by Toby Samples on using Hibernate in Xpages. When I came back, I did my best to sell the use of Hibernate because I felt it would be a benefit, and I now had the information to make it work. Despite this, the decision was made to go with JDBC, mainly because we had already had another database with the same functionality that we could copy, and the developers who wrote it were now on the same project.

Series Contents

With that background, I am going to discuss the following topics in this blog series:

• Setting up your database to connect to DB2.  
• Using the Extension Library Relational Controls
• Using Connection Pooling
• Storing the DB2 Credentials in a managed bean
• Coding a simple SELECT statement
• Selecting information from two tables using a JOIN
• Coding an INSERT
• Coding an UPDATE
• Populating a combobox dropdown with values from a DB2 query
• Retrieving a collection of data in JSON format for using in a data table

Note: Due to a very heavy load at work, it might take me up to two months or more to finish this blog series.  

Assumptions

Throughout I am going to make the assumption that you know the basics of SQL. For me, SQL was the one thing I learned in college that I actually still use today.  Another assumption is that you know something about java, because all the coding is done using it. You can use SSJS to access DB2, but early on, we made the decision to have as much of the business logic as possible in java.

Credits

I was busy with another project while the groundwork was setup for connecting to DB2. Credit for doing this work goes to my coworker Dwain Weurfel. Dwain, thanks for helping me document and explain the setup, and  for helping me to proof these posts, so that I get it right! I should also give credit to former coworker Eric Dyer who had a large role in creating the groundwork while working on the first DB2 backend project at my company.

Using Javascript RegExp Object to replace a XPages Contraint Validator

I had an occasion yesterday where I needed change a partial refresh to process data without validation in order to fix another problem. Despite this I still needed to validate the data the user enters based on a regular expression.  The previous developer was was using a <xp:validateContraint> to check the data, but this check would no longer trigger since I am skipping validation. The constraint validator uses a regular expression to determine whether the validation passes or not.

Even though the regular expression in this case was simplistic, I did not want to mirror what it does using string manipulation to validate it. I remembered from one of my Javascript books (Professional JavaScript for Web Developers) that Regular Expressions are built into the language.  It was very easy to duplicate the constraint functionality by just copying the RegExp string that already worked and then creating a RegExp javascript object.   

The code here lives in a button in the SSJS onclick event: 
        
var thisID = getComponent("inputID").getValue(); 
var pattern = /^([A-Za-z]{2}[A-Za-z0-9]{0,2})$/; 
        
if(!pattern.test(thisID)){ 
     return viewScope.infoMessage = "No Can Do";
} 

The built-in RegExp object can be created in one of two ways, you can either use the slash as I do in the above code or you can create it using new RegExp("....").  Either way that you choose, it works the same. The object has a method called test() which will return a true/false depending if the passed in string matches.  Of course, you can use this in any javascript, client or serverside.

Using a Hidden Input to Validate if a Button was pushed

In one of my current projects, I came across an issue where I needed to validate a form using the XPages validators.  This is a mature application that only uses SSJS everywhere, and I have to work with the boundaries already in place. 

The Problem

This particular problem is hard to describe but I will try.  I wanted to prevent a page from advancing to the next step.  The "Next >" link uses validators to control whether it works or not. Because of this established pattern, I needed to continue to use validators.  My problem was that there were no editable fields left on the page.  Validators only work on editable input fields, not buttons, links or computed fields. 

I needed to validate whether a button was pushed and still allow the code behind the button to work and itself trigger validation. 

The Next button allows the user to continue only when all validation is passed.

The Solution

I am going to show how to use a hidden input to validate non-input clickable elements, in this case a button. Despite validition, the onclick event of the button will still work.  Normally, the validator will trip, and the onclick event will not work until validation is passed.

Since you can only use a validator with input controls, I decided to use a Hidden Input. This control shows up on the page, but is not displayed. The validation messages of a hidden input are displayed using the standard display error control. 

<xp:inputHidden id="inputHidden1" value="#requestScope.validateYN}" required="true"> 
      <xp:this.validators> 
           <xp:validateRequired 
                  message="Please use Validate with WU button before continuing"> 
            </xp:validateRequired> 
        </xp:this.validators> 
</xp:inputHidden> 

The key is to bind the control to a scoped variable with no initial value; this will cause the validator to trigger when the page is submitted. To make the button still work normal, I used the onfocus to set the scoped variable so that it passes validation. In that onfocus event, I must do a partial refresh on the hidden input, and 'The Process Data Without Validation' box must be checked.

This shows the onfocus event of the button you want to validate

Final Thoughts

There might be other ways to accomplish what I show here, but I spent a lot of time trying other ways before I got this one working. I do know this is a rather obscure use case, but google has nothing on it, and I really didn't want to forget this in case I need to use it again.

Note: If you are very experienced with XPages, you might ask why I didn't set the button's "disabledValidators" property to "true".  For some reason, this did not work; the main code in the onclick event did not execute regardless of the property being set.