Wednesday, May 06, 2009

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.

Monday, May 04, 2009

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

Sunday, May 03, 2009

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:


In fact, rewrite that as:


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 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(;
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);
SQLExec("%Insert(:1)", &rec);

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