"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.

Serve those JavaScript Libraries Quickly... and Safely

If at all possible, don't serve JavaScript libraries from PeopleSoft applications (dynamic PeopleCode). The best place to serve a JavaScript library is from a static file residing on the PeopleSoft web server. Many PeopleSoft developers, however, don't have access to their web server file systems. The next logical place to store a JavaScript library is in an HTML definition in app designer. This is where the PeopleTools developers store JavaScript libraries and, therefore, is the preferred location for small JavaScript libraries. Unfortunately, the maximum size of a PeopleTools 8.4x HTML definition is too small to fit a good JavaScript library like jQuery. Enter John and Derek. John and Derek both wrote about a workaround to this limitation. Rather than using HTML Definitions, these innovative developers used the message catalog to store their JavaScript libraries (jQuery, of course). Here are links to those blog posts:

• Ajax and PeopleSoft from John Wagenleitner
• Injecting Javascript & AJAX into a PeopleSoft Page from Derek Tomei

Both of these posts provide an alternative for PeopleSoft developers that want to use a JavaScript library but don't have access to their PeopleSoft web servers' file structure. Building upon John's and Derek's approach, here are some ideas to help improve performance.

Packing/Minification/Raw

Based on Julien Lecomte's post Gzip Your Minified JavaScript Files, we can see that Dean Edwards's Packer algorithm produces the smallest compressed file. As Julien notes, however, the benefit of that smaller download is offset by a corresponding increase in execution/evaluation time. Julien points out that other compression techniques like Douglas Crockford's JSMin and Yahoo's YUI Compressor produce larger files that evaluate much faster. Therefore, when using JavaScript compression alone, you, as an analyst or developer, need to determine which method is appropriate for your implementation. Do you have low bandwidth connecting powerful computers to your PeopleSoft instance? If so, then maybe the Packer algorithm is more appropriate for you. If you have good bandwidth (or low bandwidth and low power client computers), then maybe you should consider JSMin or the YUI Compressor. Both of these compression techniques produce compressed text files that can be stored in a message catalog entry as described by John and Derek.

Caching

Browsers are designed to cache static, externally referenced resources. The methods demonstrated in the previously mentioned blogs insert the contents of a JavaScript library into the body of an HTML page. By inserting the contents directly into a page, the browser is not able to cache it with other JavaScripts, stylesheets, and images. An alternative approach is to serve JavaScript libraries stored as message catalog entries from IScripts. Using this approach, you could reference your JavaScript library using HTML like:

<script type="text/javascript" language="JavaScript" src="/psc/portal/EMPLOYEE/EMPL/s/WEBLIB_JS_LIBS.ISCRIPT1.FieldFormula.IScript_jQuery">
</script>

The PeopleCode for this IScript would look something like:

Function IScript_jQuery()
   Local string &jq = MsgGetExplainText(28000, 1, "alert('Unable to load the jQuery JavaScript library!\n\nThe Message Catalog entry (28000, 1) does not exist.');");
   %Response.SetHeader("Cache-Control", "max-age=28800, proxy-revalidate");
   %Response.WriteLine(&jq);
End-Function;

Update November 23, 2009: PeopleSoft actually overwrites the Cache-Control header. I have not found a way to set this through an IScript. The only solution I have found to cache IScript content is to use a ServletFilter to set the Cache-Control header after PeopleSoft returns a response.

The benefit of this approach is that users only download the JavaScript library once, rather than once per page visit. Considering cache, file size is not as important as execution time. Because the Packer algorithm produces a smaller file size, it excels in low bandwidth scenarios. With caching, however, the one time bandwidth hit might not compensate for the on going evaluation cost of the Packer algorithm. If you cache your JavaScript library, then a minification algorithm like JSMin or YUI Compressor may provide better performance.

GZip Compression

The first column of stats on Julien Lecomte's matrix compares the size of jQuery when packed, minified, or YUI Compressed. The second column compares that same content GZip compressed. GZip compressed, all three JavaScript compression methods result in a fairly comparable download size. When GZipped, the main difference between JSMin/YUI and Packer is the browser evaluation time required to "unpack" the packed JavaScript library.

The next performance improvement to explore with our IScript scenario is GZip compression. Fortunately, PeopleSoft takes care of GZip compression for us. If you look at your Web Profile settings, you will notice a check box labeled Compress Responses. If you enable this setting, PeopleSoft will automatically GZip compress the contents of your IScripts (and the contents of any other PeopleSoft page).

Whatever approach you take, be sure to consider security. OWASP listed Injection flaws as the Number 2 security concern in the OWASP 2007 top 10. Injection isn't limited to SQL. If you take user entered values and insert them into the HTML of a page, unescaped, then you have given that user the ability to inject malicious content into a page. If you use a message catalog approach and insert the contents of a message catalog entry into a page, then be sure to secure your message catalog. Failure to do so could have the same impact as the number one security threat in the OWASP 2007 Top 10: Cross Site Scripting (XSS)

Injecting JavaScript Libraries into PeopleSoft Pages

Before you start creating Ajax and Dynamic HTML usability enhancements for your PeopleSoft applications, I recommend learning a good JavaScript library. When creating client side usability enhancements, JavaScript libraries significantly reduce the amount of JavaScript you have to write and maintain. The first page of a Google search for JavaScript libraries should display several alternatives. Wikipedia also maintains a list of JavaScript libraries (my favorite JavaScript library is jQuery). After you select a JavaScript library, read the library's documentation, read some tutorials, and write some static HTML test pages. It is a good idea to get to know your chosen JavaScript library before you try to integrate it with PeopleSoft.

After you find and become proficient with a good JavaScript library, you will need a way to inject that library into your PeopleSoft application. If you limit your Ajax/DHTML plans to modifying a select number of delivered pages or enhancing a custom page, then you can inject your JavaScript library by adding a new HTML Area to your target page with the appropriate <script> tag pointing to the location of your JavaScript library. If your plans call for adding a generic usability enhancement to every page, then you will want a different solution. It is not practical or advisable to modify every page of your PeopleSoft application.

One way to make a JavaScript modification available to all pages within your application is to find an Application Designer managed object that can act as a vehicle, carrying that JavaScript modification to the client browser. We could then modify that object, injecting our JavaScript library into all PeopleSoft pages. Now that we have a potential solution, let's use our analytical skills to try to find a managed object that is common to all PeopleSoft pages. An analyst is a lot like a detective; investigating computer systems looking for patterns and solutions. Like any mystery, let's start with what we know - the facts. One thing we know is that the PeopleSoft user interface is comprised of HTML, JavaScript, CSS, and images. Using our browser we can view the source of the PeopleSoft generated HTML, CSS, and JavaScript, looking for patterns and other clues. Browser based tools like Firebug dramatically simplify this type of investigation, allowing us to view all of a page's JavaScript and CSS resources from a drop-down list. Using Firebug, we can see that the cs servlet serves many of the images, CSS, and JavaScript resources used by PeopleSoft pages. Since we know that the PeopleSoft application compiles CSS files from App Designer stylesheets and that images served by PeopleSoft come from Image Definitions stored in App Designer, it stands to reason that those JavaScript files served by the cs servlet would also come from managed objects in App Designer. Making the case stronger, we can see that PeopleSoft applications store JavaScript files in the web server cache directory, the location for all client-side managed objects.

Based on the previously established anecdotal evidence, we will assume that JavaScript files are managed object. If this is true, the next logical question to ask is, "What type of managed object?" Just as CSS files translate to Stylesheet managed objects, there must be some type of managed object that contains JavaScript. Searching PeopleBooks for JavaScript, we can see that the function GetJavaScriptURL() serves an HTML Definition as a JavaScript file. Based on this information, we form the following hypothesis:

JavaScript files served by the cs servlet are HTML Definitions and can be modified in App Designer like any other HTML Definition.

To test this, we can copy the name of a JavaScript file, like PT_PAGESCRIPT, and try to open that item as an HTML Definition. This test passes. Should we now conclude that JavaScript files served by the cs servlet are HTML Definitions? Two more tests:

1. Compare the HTML Definition to the downloaded JavaScript file. Don't expect a perfect match. The HTML Definition may contain Meta-HTML whereas the downloaded JavaScript file will contain the Meta-HTML's resolved value.
2. Modify an HTML Definition and check the results.

Using a file diff utility like WinDiff or jEdit's JDiffPlugin, we can compare the downloaded JavaScript file to the contents of the PT_PAGESCRIPT HTML Definition.

To satisfy the modification test, I suggest adding a short comment to the end of PT_PAGESCRIPT, something like /* XXX */. Since both of these tests pass, we have very strong evidence that JavaScript files served by the cs servlet are, in fact, HTML Definitions and can be modified using App Designer. Based on this investigation, we have discovered an object type that can we can use as a vehicle to carry our global customizations to our users' browsers. Since we have been using PT_PAGESCRIPT for testing, it would seem logical to continue with that HTML Definition, customizing it as needed to add additional JavaScript based DHTML usability enhancements. I tried this once. After writing a few lines of code and saving, App Designer displayed an error telling me that the HTML Definition had exceeded the maximum size and would be truncated. Generally speaking, the actual size limitation is not relevant. What is relevant is that we know a size limitation exists. Considering the size of PT_PAGESCRIPT without any modifications, there is no room for us to add additional JavaScript to PT_PAGESCRIPT. Looking through the list of JavaScript files common to all PeopleSoft pages, I suggest PT_COPYURL. PT_COPYURL appears to contain the JavaScript required to make the copy URL button work. The copy URL button is that double paper/carbon copy button in the page bar at the top of most PeopleSoft pages. I don't think early 8.4x versions of PeopleTools had this button. I don't remember when it was added, but I do remember seeing it as early as PT 8.46. If you have that button, then chances are, you have the PT_COPYURL HTML Definition. If you don't, then you may have to find a different HTML Definition to modify.

Once you identify an HTML Definition, add JavaScript similar to the following to the end of the delivered HTML Definition:

/* Conditionally include a JavaScript/Ajax libary */
if(!window.jQuery) {
  document.write("<scr" + "ipt id='jq' " + "src='/scripts/jquery.js'><\/script>");
}

/* Unconditionally insert a JavaScript file */
document.write("<scr" + "ipt id='xxx_ui' " + "src='/scripts/ui.js'><\/script>");

The first 3 lines demonstrate how to insert a static JavaScript file into all PeopleSoft pages conditioned upon the existence of an object. We typically display PeopleSoft pages in a frameset where the content frame only contains the HTML, JavaScript, and CSS required for that page. It is possible, however, to use a display template that proxies a page's content into the same HTML page as the header. The HOMEPAGE_DESIGNER_TEMPLATE is an example of this type of HTML template. If your header also contains a reference to your JavaScript library, then, best case, you will have multiple instances of your JavaScript library in memory. Worst case, your page will quit working. One way to work around this issue is to test for the existence of your JavaScript library prior to inserting it into a page. Since I use jquery, my code tests for the existence of the jQuery object.

The last line of the example above shows how to blindly insert a static JavaScript file into a PeopleSoft page. This approach works well for static JavaScript that you know isn't used by your header.

If you don't have access to your web server to install static JavaScript files or if your JavaScript needs to be dynamic, then take a look at John's post AJAX and PeopleSoft. In the post and comments, you can read about alternative ways of storing and serving JavaScript to PeopleSoft pages.

Changing a delivered HTML Definition is considered a modification. Like all modifications, you will need to consider compatibility and upgrade issues. To manage this modification through PeopleTools upgrades and patches, make sure you adequately document your modifications with code comments, project comments, and additional project management documentation. When considering upgrades, your documentation goal is to identify your modification and point the person applying an upgrade to any documentation related to this modification. Because of size limitations, you may not be able to document your entire modification inline. You will, however, be able to point other people at your documentation for this modification. For an effective, short, inline comment, I suggest something like:

<!% BEGIN xxx_1234, 13-DEC-2008, you@yourcompany.com -->
Your modified code goes here...
<!% END xxx_1234, 13-DEC-2008, you@yourcompany.com -->

With this comment, I have documented the start and end of this modification, the project name of the modification (xxx_1234), the date of the modification (13-DEC-2008), and the developer that made the modification (you@yourcompany.com). I have applied several patches over other developers' modified code. Without this type of START/END comment, it is impossible to differentiate between delivered code and modified code. Likewise, sharing a common prefix for all modifications (xxx_ in this case), dramatically simplifies searching for and identifying modifications.

Any time you modify a delivered object, you risk rendering that object unusable. If you modify a delivered PeopleTools object like PT_PAGESCRIPT, ensure that the delivered code works the same as it did before you modified it.

See you at UKOUG

I am headed to Birmingham, UK next week to present PeopleTools tips and PeopleSoft RIA at the UKOUG PeopleSoft conference. I will be traveling from the US, West Coast. If you have ventured this route before, then please share your travel tips with me. For example, what is the cheapest way to get to London from Heathrow?

While in Birmingham, I will have the opportunity to chair a PeopleTools round table. If you are attending, please bring questions and advice. This will be an open forum where we will all have the opportunity to share suggestions and solutions.

OOW 2008 Presentation Available

My PeopleTools Advanced Tips and Techniques session slides are available from Oracle's OOW content catalog. To download OpenWorld presentations, you will need to log into the content catalog using your OpenWorld Registration ID. After you log in, you can download these slides by following the instructions given here.

If you didn't register for OpenWorld, but still want to see the slides, you can purchase Oracle OpenWorld OnDemand from the log in page.

printf for Peoplesoft

Many languages include a printf function for formatting strings. The main point of printf is to provide programmers with a way to insert dates, times, numbers, and other strings into a final string. This final string is sometimes referred to as a format string or pattern because it contains the formatting characters required by printf to convert numbers and dates into strings. For example, should that floating point decimal have 2 or 3 digits after the decimal place? printf has its roots in C++, where string construction requires memory buffer allocation, etc. Simply put, printf simplifies formatting strings in languages that don't treat strings as native objects. Many languages support printf. As of Java 1.5, the Java runtime environment included with PeopleTools 8.49, supports printf. Since Java supports it, we can use it from PeopleCode. If you are new to printf, then take a look at the printf Wikipedia entry. If you are familiar with printf and just want to know how to use it, then take a look at the Format String Syntax of the java.util.Formatter class. The code below actually uses the format(Locale l, String format, Object... args) method of the java.lang.String class. If you are interested in using printf with PeopleCode, here is an example to get you started:

Function printf(&language As string, &country As string, &message As string, &parms As array of any) Returns string
   Local JavaObject &jLocale = CreateJavaObject("java.util.Locale", &language, &country);
   Local JavaObject &jParms = CreateJavaArray("java.lang.Object[]", &parms.Len);
   
   CopyToJavaArray(&parms, &jParms);
   Return GetJavaClass("java.lang.String").format(&jLocale, &message, &jParms);
End-Function;

Function IScript_TestPrintf()
   Local string &message = "Amount gained or lost since last statement dated %1$tc: $ %2$(,.2f";
   Local array of any &parms = CreateArrayAny();
   &parms.Push(%Datetime);
   &parms.Push(252356.69);
   
   %Response.SetContentType("text/plain");
   %Response.WriteLine(printf("en", "us", &message, &parms));
   %Response.WriteLine(printf("es", "es", &message, &parms));
End-Function;

Notice that my printf function takes a language code and a country code. These are the ISO language and country codes defined in ISO-639 and ISO-3166 respectively. One of the places Java's formatting functions shine is in creating locale specific strings. If you don't need to format strings for different Locales, then you can delete these parameters and use the 2 argument version of the format method.

One thing that is interesting to note is that the format method of the java.lang.String class takes a variable length list of arguments. PeopleCode functions don't have this concept. In fact, Java objects called from PeopleCode don't have this concept either. As I was trying to figure out how to call this function from PeopleCode, I did a little research on Java's variable length parameter lists. It appears that this convention is a design time convention and that the compiler actually converts variable length lists into arrays. At runtime, these lists actually appear as arrays. Therefore, we can call a method that takes variable length parameters by passing that method an array. In this case, since the parameters are of type java.lang.Object, we can use the CopyToJavaArray PeopleCode function to copy an array of type Any into a Java Array of type java.lang.Object.