Test Driven Development for PeopleSoft

The PeopleTools blogging team just released an unsupported tool for test driven PeopleCode development: PSUnit. The blog post contains the PSUnit source code and a document that describes how to use PSUnit to test drive PeopleCode development. If you are familiar with one of the xUnit frameworks (jUnit, nUnit, utPLSQL, Test::Unit, etc) and eXtreme Programming, then you know the value of test driven development. If you are new to Test Driven Development (TDD), then take a look at Kent Beck's book Test Driven Development by Example.

As a fellow PeopleCode programmer, I believe we can create better solutions by applying proven disciplines and methodologies to PeopleCode development.

Serve JSON from PeopleSoft

Last month a reader asked me for an example of serving JSON from PeopleSoft. The following IScript demonstrates how to serve JSON by printing user and role information in JSON format:

Function IScript_GetJSON
   Local SQL &usersCursor = CreateSQL("SELECT OPRID, OPRDEFNDESC, EMAILID FROM PSOPRDEFN WHERE ROWNUM < 6");
   Local SQL &rolesCursor;
   Local string &oprid;
   Local string &oprdefndesc;
   Local string &emailid;
   Local string &rolename;
   
   Local boolean &isFirstUser = True;
   Local boolean &isFirstRole = True;
   
   %Response.Write("[");
   While &usersCursor.Fetch(&oprid, &oprdefndesc, &emailid)
      REM ** comma logic;
      If (&isFirstUser) Then
         &isFirstUser = False;
      Else
         %Response.Write(", ");
      End-If;

      %Response.Write("{""OPRID"": """ | EscapeJavascriptString(&oprid) | """, ""OPRDEFNDESC"": """ | EscapeJavascriptString(&oprdefndesc) | """, ""EMAILID"": """ | EscapeJavascriptString(&emailid) | """, ""ROLES"": [");

      &rolesCursor = CreateSQL("SELECT ROLENAME FROM PSROLEUSER WHERE ROLEUSER = :1 AND ROWNUM < 6", &oprid);
      &isFirstRole = True;
      
      While &rolesCursor.Fetch(&rolename);
         REM ** comma logic;
         If (&isFirstRole) Then
            &isFirstRole = False;
         Else
            %Response.Write(", ");
         End-If;

         %Response.Write("""" | EscapeJavascriptString(&rolename) | """");
      End-While;
      
      &rolesCursor.Close();
      %Response.Write("]}");
   End-While;
   
   %Response.Write("]");
   &usersCursor.Close();
   
End-Function;

The code above uses embedded SQL. In production, be sure to use App Designer SQL definitions. This code listing also embeds JSON formatting strings. As an alternative, I recommend HTML definitions and HTML bind variables. In this manner, HTML definitions serve as templates for structured JSON data.

Formatted, the output from my demo database looks like:

[
    {
        "OPRID": "ADRIESSEN",
        "OPRDEFNDESC": "Anton Driessen",
        "EMAILID": "ADRIESSEN@server.com",
        "ROLES": [
            "All Processes",
            "All Query Access Groups",
            "EPM Scorecard Viewer",
            "Portal User",
            "Query Access - All FSCM"
        ]
    },
    {
        "OPRID": "ADUPOND",
        "OPRDEFNDESC": "Alain Dupond",
        "EMAILID": "ADUPOND@server.com",
        "ROLES": [
            "All Processes",
            "All Query Access Groups",
            "EPM Scorecard Viewer",
            "Portal User",
            "Query Access - All FSCM"
        ]
    },
    {
        "OPRID": "AEGLI",
        "OPRDEFNDESC": "Anna Egli",
        "EMAILID": "AEGLI@server.com",
        "ROLES": [
            "All Processes",
            "All Query Access Groups",
            "EPM Scorecard Viewer",
            "Employee Global Payroll",
            "Portal User"
        ]
    },
    {
        "OPRID": "AERICKSON",
        "OPRDEFNDESC": "Arthur Erickson",
        "EMAILID": "AERICKSON@server.com",
        "ROLES": [
            "Accounts Payable Manager",
            "All Processes",
            "All Query Access Groups",
            "Application Homepages",
            "EP General Options"
        ]
    },
    {
        "OPRID": "AFAIRCHILD",
        "OPRDEFNDESC": "Alison Fairchild",
        "EMAILID": "AFAIRCHILD@server.com",
        "ROLES": [
            "Applicant",
            "All Processes",
            "All Query Access Groups",
            "EPM Scorecard Viewer",
            "Employee ELM"
        ]
    }
]

IScripts provide a secure free-form mechanism for serving data. This makes them perfect for serving JSON in response to a logged in user's AJAX request. If you want to serve PeopleSoft data in JSON format for consumption in a page outside PeopleSoft, then try using an Integration Broker synchronous message handler.

If you need help prototyping your JSON, take a look at the JSON homepage and JSONLint, an online JSON validator. I rely heavily on JSONLint when prototyping JSON.

Using Oracle JDBC from PeopleCode

PSST0101's post Writing to Access Databases demonstrates how to call standard JDBC boiler plate code from PeopleCode. I tried it with the Oracle JDBC driver, but couldn't get the driver to load. First, class.forName kept throwing java.lang.ClassNotFoundException: oracle/jdbc/OracleDriver. Since the intent of class.forName is to register the driver with the JDBC DriverManager, I thought I would see if I could register it manually:

Local JavaObject &driverManager = GetJavaClass("java.sql.DriverManager");
&driverManager.registerDriver(CreateJavaObject("oracle.jdbc.OracleDriver"));

This time the JVM found the class, but, when I executed DriverManager.getConnection, the JVM threw java.sql.SQLException: No suitable driver.

The point of boiler plate JDBC code is to abstract the persistence layer from the code tier, allowing for configurable data repositories. Since PeopleCode requires Java class names as strings, PeopleCode affords us this configurable feature without the JDBC abstraction. Therefore, we can skip the JDBC niceties in favor of direct driver usage:

Local JavaObject &driver = CreateJavaObject("oracle.jdbc.OracleDriver");
Local JavaObject &info = CreateJavaObject("java.util.Properties");

&info.put("user", "john_doe");
&info.put("password", "secret");

Local JavaObject &conn = &driver.connect("jdbc:oracle:thin:@servername:1521:SID", &info);
Local JavaObject &stmt = &conn.createStatement();
Local JavaObject &rs = &stmt.executeQuery("SELECT ROLENAME FROM PSROLEDEFN WHERE ROWNUM < 11");
Local number &rowIdx;

While &rs.next()
   &rowIdx = &rowIdx + 1;
   MessageBox(0, "", 0, 0, "Row " | &rowIdx | " column 1: " | &rs.getObject(1).toString());
End-While;

REM ** Close JDBC resources per Cheryl's comment below;
&rs.close();
&stmt.close();
&conn.close();

Even though I find this example thought provoking, I have not used it in production. When integrating with other applications, I first consider Integration Broker's asynchronous services. Integration Broker provides services such as queuing that aren't available with direct access. If I can't find a delivered target connector and can't create a custom target connector to suit my needs, then my next consideration is Oracle database links.

PeopleSoft's flexible architecture provides developers with a vast array of options. It is important for us as developers to know how and when to use these options.

Update (March 24, 2009)

Nicolas pointed out that users should not hard code user names and passwords. I absolutely agree. The code above is provided as an example. If you implement a solution like this, I strongly encourage you to Encrypt the password and store it somewhere else. PeopleCode has Encrypt and Decrypt functions for this purpose. You can either store the user name and password encrypted in the database or store it in a protected file off line. Furthermore, don't use a privileged user. Use a database user that only has the database access required to accomplish the coded task.

Besides a hard coded password, the code above contains a hard coded SQL string literal. I strongly discourage this practice as well. Use App Designer SQL definitions to store SQL statements. Unlike string literals, PeopleSoft change management tools can search, compare, and maintain managed objects referenced in SQL definitions.