HOWTO: Generate GUID from PeopleCode

A few months ago I demonstrated a handful of ways to Base64 encode strings — none of them were delivered. That post generated some outstanding feedback. Customers, consultants, and Oracle employees pointed me at several other alternatives, including the delivered Pluggable Encryption technique documented in PeopleBooks. I hope this post generates the same amount of discussion.

First, why would a PeopleSoft developer generate a GUID? My motivation is a database cache. I have a transaction that inserts information into a cache Record and an IScript that reads values from that cache. Rather than pass transaction keys to the IScript, I just pass a GUID that identifies the transaction's row in the cache. Besides decoupling the IScript from a transaction, it provides a bit of security through obfuscation (see OWASP Top 10 2007-Insecure Direct Object Reference)

Now, how to generate a GUID from PeopleCode... PeopleBooks does not list any GUID generation functions, so the next place to look for this functionality is in related technologies accessible to PeopleCode. For example, the Oracle database provides the SYS_GUID function for generating GUID's in the RAW. Here is the SYS_GUID function in action:

Local string &guid;
SQLExec("SELECT RAWTOHEX(SYS_GUID()) FROM PS_INSTALLATION", &guid);
MessageBox(0, "", 0, 0, "GUID from DB: " | &guid);

If you are just looking for a random string, then read no further. If you want a formatted GUID, then you will need to add the dashes yourself. Microsoft SQL Server has a similar function that actually returns a fully formatted plain text GUID.

The problem with these SQL alternatives is that they are database specific. I'm not going to complain about a user taking full advantage of the database's features. But, if there is an alternative that may reduce future maintenance costs (like the cost of swtiching from one database to another), then I'll consider the low cost alternative.

Since all PeopleSoft application servers run Java, we can use the JRE's GUID methods to generate a GUID. If you are on PeopleTools 8.49 with Java 1.5, then this short PeopleCode snippet will give you a fully formatted GUID:

GetJavaClass("java.util.UUID").randomUUID().toString();

On my laptop, the code above generated d65ca460-fc93-4420-a889-8b36311ee4a0.

What if you are using an older version of PeopleTools (prior to 8.49)? The Apache commons id project has a UUID Java class that is similar to the delivered Java 1.5 UUID class. For more information about Java GUID generation, see this java.util.UUID mini-FAQ.

Who knows, if you dig through your application's PeopleCode, you might find an undocumented method for generating GUID's ;)

New PeopleTools Book

Pre order copies of my new book PeopleSoft PeopleTools Tips & Techniques. The publication date is set for July, 2010, about one year from now.

Updated Oct 29, 2009: Unfortunately, you cannot pre order yet. McGraw Hill removed the placeholder page from their site. McGraw Hill will repost and begin taking pre orders as we get closer to the release date. I will post an update at that time.

Ajax Experience '08 JavaScript Presentation

I was researching some JavaScript techniques and ran across the video Advanced JavaScript: Closures, Prototypes, Demystified. If you read this blog and like the JavaScript customization ideas that you see, but aren't very comfortable with JavaScript, then watch this presentation. It does a great job of explaining some of the features of the JavaScript language.

Base64 Encoding with Pluggable Encryption

I dedicate this post to Chris Heller since he is the person that pointed me at PeopleSoft's pluggable encryption in his comments to my last post on Base64 Encoding for PeopleSoft. I had not considered PeopleSoft's pluggable encryption for base64 encoding. Thanks Chris!

Before we can base64 encode strings with PeopleSoft's Pluggable Encryption, we need to create an Algorithm Chain and an Encryption Profile. PeopleBooks does an excellent job of explaining each of these terms as well as telling how to create each of these components. Since PeopleSoft delivers the base64 encryption algorithm with the PSPETSSL encryption library, we can move right into defining the Algorithm Chain. To define the chain, navigate to PeopleTools > Security > Encryption > Algorithm Chain. Add the new value BASE64_ENCODE and add the following Algorithms in order:

1. PSUnicodeToAscii
2. base64_encode
3. PSAsciiToUnicode

Be sure to set the sequence number for each row (1-3). Save and navigate to PeopleTools > Security > Encryption > Encryption Profile. Add the new value BASE64_ENCODE. Specify the algorithm chain BASE64_ENCODE and save. We can now test this pluggable encryption profile with a little PeopleCode:

Local object &crypto = CreateObject("Crypt");
&crypto.Open("BASE64_ENCODE");
&crypto.UpdateData("Hello World");
MessageBox(0, "", 0, 0, "Encrypted: " | &crypto.Result);

And, the result should be: SGVsbG8gV29ybGQ=. I am sure you will all agree that this solution is much simpler than the ugly Java Reflection I previously demonstrated. Furthermore, this method is well documented in PeopleBooks and supported by PeopleSoft.

If I am reading PeopleBooks right, Pluggable Encryption only works with ASCII text. That is why we have to use the PSUnicodeToAscii and PSAsciiToUnicode algorithms in our algorithm chain. What this means is that we still need to use an alternative for base64 encoding binary data, a requirement when embedding binary data in XML documents.

Base64 Encoding for PeopleSoft

This week on the ittoolbox peopeltools-I forum, I ran across a question on base64 encoding binary data using PeopleCode. There are a couple of ways to base64 encode data, none of which are delivered. Before deciding which method to use, we first have to answer two questions:

1. Where is the data (file, database, text string)?
2. Is the data in binary or plain text format?

If the data resides in the database, then we can select it out using the following SQL:

SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('Hello World'))) FROM DUAL;

In fact, rewrite that as:

SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(%TextIn(:1)))) FROM DUAL;

And, if you are an Oracle database user, then you have a generic SQL statement you can use to base64 encode any plain text. If you name the SQL definition BASE_64_ENCODE, then you can call that SQL definition as follows:

Local string &source = "Hello World";
Local string &encoded;
Local number &i;

SQLExec(SQL.BASE_64_ENCODE, &source, &encoded);
MessageBox(0, "", 0, 0, "base64: " | &encoded);

The PeopleCode and SQL above will work for any text string as long as you are using an Oracle database. Now, what if you want to base64 encode binary data? If the binary data is in the database, then modify the SQL accordingly.

Did you notice the %TextIn Meta-SQL I sneaked into the SQL above? Use it whenever you are sending large amounts of text to the database. For a string as short as "Hello World," you don't need it, but if you are trying to base64 encode an entire XML document, then you might.

What if you are not using Oracle database? Perhaps T-SQL has a base64 encoding routine? If so, great. If not, then the following code provides a Java/PeopleCode solution. Unfortunately, the 2 common Java base64 encoding algorithms use method and constructor overloads in a manner that requires some ugly Java reflection PeopleCode.

REM ** create an instance of the Java base64 encoder;
Local JavaObject &encoder = CreateJavaObject("sun.misc.BASE64Encoder");

REM ** get a reference to a Java class instance for the primitive byte array;
Local JavaObject &arrayClass = GetJavaClass("java.lang.reflect.Array");
Local JavaObject &bytArrClass = &arrayClass.newInstance(GetJavaClass("java.lang.Byte").TYPE, 0);

REM ** use reflection to get a reference to the method we want to call;
Local JavaObject &encodeArgTypes = CreateJavaObject("java.lang.Class[]", &bytArrClass.getClass());
Local JavaObject &encodeMethod = &encoder.getClass().getMethod("encode", &encodeArgTypes);

REM ** call the method;
Local JavaObject &bytes = CreateJavaObject("java.lang.String", "Hello World").getBytes();
Local JavaObject &result = &encodeMethod.invoke(&encoder, CreateJavaObject("java.lang.Object[]", &bytes));

REM ** print the result;
MessageBox(0, "", 0, 0, "Result: " | &result.toString());

If you know enough about Java to compile a wrapper class and you have access to your app server's class directory, then you can eliminate the Java reflection code in favor of a wrapper:

package com.blogspot.jjmpsj;

import java.io.IOException;

import sun.misc.BASE64Encoder;

public class Base64Coder {
    public static String encodeString(String data) {
        BASE64Encoder encoder = new BASE64Encoder();
        return encoder.encode(data.getBytes());
    }

    public static String encodeBytes(byte[] data) {
        BASE64Encoder encoder = new BASE64Encoder();
        return encoder.encode(data);
    }
}

... and call it like so:

Local JavaObject &encoder = GetJavaClass("com.blogspot.jjmpsj.Base64Coder");
Local string &result = &encoder.encodeString("Hello World");

MessageBox(0, "", 0, 0, &result);

As you can see from this final example, when working with overloaded constructors and methods, Java wrappers dramatically simplify PeopleCode.

Each of the examples above uses "Hello World" as the text string. What is "Hello World" in base64? SGVsbG8gV29ybGQ=. You can find an online base64 encoder/decoder here.

I will have to leave the question of base64 encoding binary files for another day. I am on the East coast attending Collaborate '09 and I need to get some rest so I can be right as rain for the conference tomorrow morning.

Update May 4th, 2009 at 4:09 AM: In the comments to this post, devwfb suggests that developers use org.apache.commons.codec.binary.Base64
rather than sun.misc.BASE64Encoder because classes in the sun package are undocumented and unsupported. devwfb is right and I should have mentioned this fact in my original post. I chose to use sun's base64 encoder because it is delivered and doesn't require customers to add more jars to the $PS_HOME/class directory. I will post the commons-codec example and link to it from here. See Sun's FAQ for more information.

Productivity gains through Meta-SQL

I hope the designer of PeopleSoft's Meta-SQL is extremely wealthy. I believe Meta-SQL usage is one of the most under utilized PeopleTools development practices. Do you want to improve your productivity as a PeopleSoft developer? Learn Meta-SQL. Consider the %InsertSelect Meta-SQL statement... A PeopleSoft SQL insert into/select from statement may contain 100 or more fields. Maintaining the mappings between the insert clause and the select clause can be quite daunting. %InsertSelect eliminates this mapping nightmare through convention: source fields with the same name are automatically mapped to destinations with the same name. If your scenario deviates from this convention, then a minor configuration in the Meta-SQL statement allows you to override the default behavior.

Likewise, using Meta-SQL, it is possible to write generic SQL that works regardless of the target table. Consider the following PeopleCode:

Local Record &rec = CreateRecord(Record.xxx);
Local string &exists;

REM set &rec key field values, copy from component buffer, etc;

SQLExec("SELECT 'X' FROM %Table(:1) WHERE %KeyEqual(:1)", &rec, &exists);

No matter what table I specify, this same SQL statement will tell me if a matching row exists in that table. PeopleSoft includes many of these Meta-SQL shortcuts. By combining a couple of Meta-SQL statements into a FUNCLIB, I can create a generic PeopleCode compliment to the Oracle merge statement (some call it UPSERT):

Function Merge(&rec As Record)
   Local string &exists;
   
   SQLExec("SELECT 'X' FROM %Table(:1) WHERE %KeyEqual(:1)", &rec, &exists);
   
   If (All(&exists)) Then
      SQLExec("%Update(:1)", &rec);
   Else
      SQLExec("%Insert(:1)", &rec);
   End-If;
End-Function;

Where would I use a merge function like this? Let's say you need to clone data in the current buffer, but change a key field (EFFDT perhaps?). Using the Copy methods of stand-alone Rowsets and Records, I can copy the component buffer into stand-alone rowsets and records, change the key fields, and then, with a generic loop, iterate over the rows and records, inserting or updating database values using the Merge function above.

There are several Meta-SQL routines. Some exist to provide database independence, but many exist to provide meta-data driven shortcuts for repetitive tasks. I encourage you to take some time to review the PeopleBooks Meta-SQL documentation. I trust you will be pleasantly surprised with the productivity gains you can achieve through Meta-SQL. Now, if I could just get someone to create an open source port of PeopleSoft's Meta-SQL that I can use with JDBC...

"Introducing PSUnit" now in Wiki

I just moved the "Introducing PSUnit" document to Oracle's Wiki: Introducing PSUnit. Please feel free to update, discuss, and contribute using the tools provided by Oracle's Wiki.

Collaborate '09

Collaborate '09 is less than a month away. I will presenting session 61450 - PeopleTools Tips and Techniques on Tuesday from 4:30 pm to 5:30 pm. See you there!

Using JDBC to Execute Stored Procedures with Output Parameters

PeopleSoft allows developers to execute database stored procedures using PeopleCode that resembles SQLExec("EXEC PACKAGE.PROC_NAME(:1, :2)", &bind1, &bind2);. Even though stored procedures can have input and output parameters, SQLExec discards output parameters. Several years ago I found an interesting post that described how to use DBMS_PIPE with Oracle database to return output parameters, but, it appears the author removed that post. What made the DBMS_PIPE solution so compelling was that it shared the PeopleSoft database connection. The alternative presented below, unfortunately, requires you to maintain a user name and password, preferably encrypted and stored in a secure location. Because of the class loading issues mentioned in my post Using Oracle JDBC from PeopleCode, this post uses the Oracle specific data access classes. If you use a different database, the classes will differ, but the concept is the same. Furthermore, if you use a different database and JDBC driver, you may be able to use the standard, generic JDBC classes as described in the PSST0101 post Writing to Access Databases

Before demonstrating how to call a stored procedure from PeopleCode, we need a stored procedure to call. The following PL/SQL describes a stored procedure that has two parameters: one in and one in/out. The implementation of the procedure hard codes the output value for simplicity.

CREATE OR REPLACE PACKAGE JJM_IN_OUT AS

  PROCEDURE P(
    IN1    IN     VARCHAR2,
    INOUT1 IN OUT VARCHAR2);
END JJM_IN_OUT;
/

CREATE OR REPLACE PACKAGE BODY JJM_IN_OUT AS
  PROCEDURE P(
    IN1    IN     VARCHAR2,
    INOUT1 IN OUT VARCHAR2) IS
  BEGIN
    INOUT1 := 'Hello World';
  END P;
END JJM_IN_OUT;
/
show errors

To build this package, copy the PL/SQL above into a text editor and then run it from SQLPlus. The procedure test follows:

SQL> var out1 varchar2(100)
SQL> exec JJM_IN_OUT.P('x', :out1);

PL/SQL procedure successfully completed.

SQL> print out1

OUT1
-------------------------------------------------------
Hello World

SQL>

The following IScript demonstrates calling a procedure with in/out parameters from PeopleCode:

Function IScript_OutParms()
   Local JavaObject &driver = CreateJavaObject("oracle.jdbc.OracleDriver");;
   Local JavaObject &info = CreateJavaObject("java.util.Properties");
   
   &info.put("user", "dbuser");
   &info.put("password", "secret");
   
   Local JavaObject &conn = &driver.connect("jdbc:oracle:thin:@server:1521:SID", &info);
   Local JavaObject &stmt = &conn.prepareCall("{call JJM_IN_OUT.P (?,?)}");
   Local JavaObject &types = GetJavaClass("java.sql.Types");
   
   REM ** set input parameter values;
   &stmt.setString(1, "aa");
   &stmt.setString(2, "bb");
   
   REM ** register the second parameter as a output parameter;
   &stmt.registerOutParameter(2, &types.VARCHAR);
   
   REM ** execute the SQL statement;
   &stmt.execute();
   
   %Response.SetContentType("text/plain");
   
   REM ** Display the value of the second parameter;
   %Response.WriteLine("JJM_IN_OUT.P output parameter value: " | &stmt.getString(2));
   
   &conn.close();
End-Function;

After making the connection, the code prepares an SQL statement: {call JJM_IN_OUT.P (?,?)}. JDBC procedure calls use the syntax {call proc_name (?,?)} (the question marks represent the procedure's parameters).

The code above is for demonstration purposes only. Be sure to store the database user name and password in a secure manner. When executing SQL using a second connection, be sure to consider deadlock, race conditions, etc.

JSON In (and out) of Oracle - JSON Data Type

Lewis Cunningham just posted this link in a comment to one of my JSON posts: JSON In (and out) of Oracle - JSON Data Type. Nice work Lewis!

GZip Compress Static Files

My PeopleSoft web server contains several JavaScript and CSS files that I embed in PeopleSoft pages using the various techniques described in this blog. Unlike files served by the PeopleSoft application, the web server does not GZip compress these static text files. Until recently, I used the GZip ServletFilter described by Jayson Falkner in his post Two Servlet Filters Every Web Application Should Have. But GZipping every request for the same static file seemed like an unnecessary waste of CPU cycles. I got to thinking...

Could I eliminate the CPU cycles expended by GZipping those static files on every request? Is there a way store static GZipped content and serve the GZipped version to browsers that accept GZip encoding while still making the plain text version available to other browsers?

Here is the solution I cooked up: using the following rules with the tuckey.org UrlRewriteFilter ServletFilter, I can serve a static GZip file to browsers that accept GZip compression while still serving the plain text version to browsers that don't.

The URL Rewrite rules:

<!-- Browsers that support GZip -->
<rule>
    <condition type="header" name="Accept-Encoding">.*gzip.*</condition>
    <from>^/scripts/([^&lt;&gt;:"/\|?*]+\.js)$</from>
    <to type="forward">/compressed/bin/scripts/$1.gz</to>
    <set type="response-header" name="Content-Encoding">gzip</set>
</rule>

<rule>
    <condition type="header" name="Accept-Encoding">.*gzip.*</condition>
    <from>^/css/([^&lt;&gt;:"/\|?*]+\.css)$</from>
    <to type="forward">/compressed/bin/css/$1.gz</to>
    <set type="response-header" name="Content-Encoding">gzip</set>
</rule>

<!-- Browsers that do NOT support GZip -->
<rule>
    <condition type="header" name="Accept-Encoding" operator="notequal">.*gzip.*</condition>
    <from>^/scripts/([^&lt;&gt;:"/\|?*]+\.js)$</from>
    <to type="forward">/compressed/minified/scripts/$1</to>
</rule>

<rule>
    <condition type="header" name="Accept-Encoding" operator="notequal">.*gzip.*</condition>
    <from>^/css/([^&lt;&gt;:"/\|?*]+\.css)$</from>
    <to type="forward">/compressed/minified/css/$1</to>
</rule>

From this rules file, you can see that I store GZip compressed versions of my JavaScript files in /compressed/bin/scripts. If a requesting browser has the value gzip in the Accept-Encoding header and the request is for a file in the /scripts/ directory, then the first rule tells the UrlRewriteFilter to add the response header Content-Encoding: gzip and serve the version located at /compressed/bin/scripts/. Rule #2 is similar, but for CSS files. Rules 3 and 4 are the inverse of rules 1 and 2, telling the UrlRewriteFilter to serve files from /compressed/minified/scripts/ and /compressed/minified/css/. For example, if an IE 7 browser requests /scripts/jquery-1.3.2.min.js, then the UrlRewriteFilter will add the Content-Encoding: gzip response header and serve /compressed/bin/scripts/jquery-1.3.2.min.js.gz. If an LWP Perl browser posted the same request, then the UrlRewriteFilter would serve the file located at /compressed/minified/scripts/jquery-1.3.2.min.js (assuming the LWP Accept-Encoding header is not set).

Using URL Rewriting in this manner, I don't serve files from my /scripts/ and /css/ directories, and, therefore, don't need these directories on my web server. To avoid confusion caused by developers searching for these files on my web server, I put readme.txt files in each of these directories to explain that URL's pointing at these directories are rewritten to the /compressed directory.

If you use the UrlRewriteFilter, then be sure to use filter-mapping patterns that won't rewrite PeopleSoft URL's. Here are my mappings for the /css/ and /scripts/ URL's

<filter-mapping>
    <filter-name>GzipFilter</filter-name>
    <url-pattern>/scripts/*</url-pattern>
</filter-mapping>

<filter-mapping>
    <filter-name>GzipFilter</filter-name>
    <url-pattern>/css/*</url-pattern>
</filter-mapping>

You can create GZip versions of your files using the following command:

cat $file_location/scripts/jquery.js | gzip --stdout -9 > $file_location/bin/scripts/jquery.js.gz

And on Windows:

cat %file_location%\scripts\jquery.js | gzip --stdout -9 > %file_location%\bin\scripts\jquery.js.gz

Unfortunately, Windows doesn't have a cat or gzip command. To utilize these commands on Windows, I recommend installing UnxUtils.

Caveat: adding ServletFilters as described in this post may violate your PeopleSoft limited use web server license. Consult your license agreement to ensure compliance. If you use WebLogic, you can leverage the full power of your WebLogic instance by purchasing a WebLogic license from your Oracle rep. For a low cost alternative, you can reverse proxy your PeopleSoft web server with Apache's httpd server and use Apache's URL Rewrite engine (mod_rewrite) instead of the ServletFilter mentioned in this post.

PeopleSoft as a Password Authentication "Ticket" Server

My post Generating an AuthToken for SwitchUser demonstrates how to acquire and expire PeopleSoft authentication tokens. Using this approach, you could hook any custom application into the PeopleSoft security model, allowing PeopleSoft to manage security for many of your custom enterprise applications. Continuous token (ticket) validation could be implemented through a very simple web service that calls SwitchUser and returns the result. If SwitchUser returns true, then the token is valid.

Really, if you are interested in a centeralized, integrated security solution, then you should speak with your Oracle rep about Oracle's Identity Management Suite.

Generating an AuthToken for SwitchUser

As the name implies, the PeopleCode SwitchUser function allows developers to switch the logged in Operator ID from one user to another. For security reasons, you can only switch identities if you either have another user's operator ID and password or another user's valid authentication token (AuthToken). While I'm sure there are numerous uses for this function, here are my top two:

• Presenting a sign on Pagelet to a GUEST user
• Switching the runtime context of Integration Broker PeopleCode

In both of these scenarios, PeopleCode is already running as one user, but you need to switch the runtime context to a different user. For example, if we build a web service that exposes Approval Workflow Engine (AWE) approvals, we must authenticate the caller to ensure the caller has access to a specific approval.

The PeopleCode SwitchUser function takes four parameters. If you use the first two parameters, UserID and Password, then you don't use the third. Likewise, if you use the third parameter, AuthToken, then you don't use the first and second. They are mutually exclusive. The fourth parameter is irrelevant for this discussion. Since the first two parameters are self explanatory, the remainder of this discussion will focus on the third parameter, the AuthToken.

Let's further consider the AWE example. Here is the scenario:

Managers want to approve AWE transactions from their mobile devices (BlackBerry, iPhone, etc). PeopleSoft, however, does not support mobile browsers. One method to enable mobile access is to create a stand alone web application that communicates with PeopleSoft using web services. The initial page for this web application will prompt the user for a PeopleSoft user name and password. The second page of this web application will display information about an approval and provide the manager with action buttons to approve, deny, or push back the transaction. When the manager selects one of the action buttons, the web application will use web services to update the PeopleSoft AWE transaction.

The process flow in this scenario requires the web application to call at least two PeopleSoft web services. Since web services are stateless, we need to authenticate the user on each call. Because these web service calls span multiple request/response cycles, the web application will need to store that authentication information between mobile client requests, making session variables the logical place to store this information. At this point, the question we need to ask is, "What authentication information do we want to store in a session variable?" User name and password? As an alternative, we could store an AuthToken in a session variable and use that as a parameter to SwitchUser. Unlike user names and passwords, authentication tokens can be invalidated and are subject to configurable expiration rules.

How do you generate an AuthToken? Here is the method I use: I create an HTTP connection to my PeopleSoft server's sign on URL and then parse the returned cookies. Here is an example that uses Java and Jakarta Commons HttpClient:

HttpClient client = new HttpClient();

// Posting to the PS login URL
PostMethod post = new PostMethod("http://my.ps-server.com/psp/hrms/?cmd=login");
post.addParameter("userid", username);
post.addParameter("pwd", password);

// expect redirect response code
if(client.executeMethod(post) != 302) {
    throw new Exception("Expected a redirect response code.");
}

Cookie[] cookies = client.getState().getCookies();
Cookie pstokenCookie = null;
String pstoken = null;

for (int cookieIdx = 0; cookieIdx < cookies.length; cookieIdx++) {
    Cookie cookie = cookies[cookieIdx];
    if (cookie.getName().equals("PS_TOKEN") && 
        cookie.getDomain().equals(".ps-server.com")) {
        pstoken = cookie.getValue();
        pstokenCookie = cookie;
    }
}

if (pstoken == null) {
    throw new Exception("Ack! Didn't find PS_TOKEN cookie");
}

With my AuthToken (PS_TOKEN) identified, I can store it in a session variable, pass it along to Integration Broker, and then invalidate it when the mobile user logs out. Here is some sample code that demonstrates how to invalidate an AuthToken:

HttpClient client = new HttpClient();

client.getState().addCookie(pstokenCookie);

GetMethod get = new GetMethod("http://my.ps-server.com/psp/hrms/?cmd=logout");

int httpResponseCode = client.executeMethod(get);
// TODO: validate response code

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.

Alliance 2009 Sessions

It is almost time for Alliance 2009. It looks like the Alliance technical content committee put together another outstanding lineup of technical customer led sessions:

• 26010: Iscript, You Script Why Not JavaScript? North Carolina State University
• 26100: Virtually Refreshing: Virtualizing and Refreshing your PeopleSoft Environments with VMWare Brigham Young University - Hawaii
• 26040: Enterprise Portal 9.0 - Is it worth it? Coppin State University
• 26243: Putting the Service into SOA - Consuming WSDL's in PeopleSoft University of Utah
• 26534: PeopleCode Enhanced Logging Using Log4j University of Utah
• 26609: Sign On Peoplecode: A Drilldown Texas Christian University

My Oracle peers will present several great technical sessions as well. To see a full list of Oracle technical sessions, browse to the Alliance 2009 Registration and Agenda Builder and select Track: Technical and Primary Speaker Organization: Oracle/PeopleSoft.

I will present session 26372: PeopleTools Tips and Techniques on tuesday, March 24th, 2009 from 9:50 AM to 10:50 AM. This year's Tips and Techniques presentation will major in productivity tips with a minor in Ajax and Java.

Exporting Attachments Part 2

In my post Export PeopleSoft Attachments using PL/SQL, I mentioned that PeopleSoft provides the File Attachment API for storing, retrieving, and viewing file attachments. These API functions comprise the recommended method for working with attachments. If you want to process an attachment, use the GetAttachment function to move that attachment into a file so you can access it from your app server. The ViewAttachment function, on the other hand, will send a copy of an attachment to a client browser. The ViewAttachment function is the only method provided by the Attachment API that allows a user to view the contents of an attachment. What if you want more control over the way PeopleSoft displays attachments? For example, let's say you use the File Attachment API to allow selected users to upload audio files (news, recorded training sessions, etc) and you have another page that allows other users to listen to those recordings. Should the "view" page display a "View Attachment" link or should it play the selected clip as embedded audio?

If you store attachments in the database using a RECORD:// style URL, then you can extract those attachments using PeopleCode. Here is an IScript that demonstrates this:

Function IScript_GetAttachment()
   Local any &data;
   Local string &file_name = "attachment.doc";   
   Local SQL &cursor = CreateSQL("SELECT FILE_DATA FROM PS_EO_PE_MENU_FILE WHERE ATTACHSYSFILENAME = :1 ORDER BY FILE_SEQ", &file_name);

   REM Set the following header if you want a download prompt. Don't set it if you want inline content;
   %Response.SetHeader("content-disposition", "attachment;filename=" | &file_name);

   While &cursor.Fetch(&data);
      %Response.WriteBinary(&data);
   End-While;
End-Function;

This method allows you to embed audio/video, provide user configurable background images, display inline PDF files, etc.

If you follow my blog closely, you may remember that my IScripts post claims there is no way to write binary data to an HTTP response. Notice the use of the %Response.WriteBinary method above? I was wrong. I was reading the comments of the post Browse the App Server Filesystem via iScript and noticed Joe's reference to %Response.WriteBinary(). Even though it isn't documented in PeopleBooks, I tried it and it worked. Thanks Joe!

Note that I hard coded my SQL statement. I did this for simplicity in this blog post. For production systems, I encourage you to use SQL definitions. Of course, if you implement this solution, you will need to change the SQL select table name to the name of your attachment table. Likewise, you will probably derive your file name from a parameter rather than hard code it as shown here.

As with any custom development, make sure you write secure code. For example, when executing SQL based on parameters, use SQL binds. Failure to use SQL binds can result in SQL injection flaws. Likewise, if you accept an attachment from a user and then display that attachment using a means other than the provided ViewAttachment function, then make sure you either validate that input or validate the output. For example, let's say you have a page that allows users to upload JavaScript or other HTML that will be executed on other pages. Failure to restrict this type of usage provides malicious users with an opportunity to create HTML injection and cross site scripting vulnerabilities.