101 Ways to Process JSON with PeopleCode

... well... maybe not 101 ways, but there are several!

There is a lot of justified buzz around JSON. Many of us want to (or must) generate and parse JSON with PeopleSoft. Does PeopleSoft support JSON? Yes, actually. The Documents module can generate and parse JSON. Unfortunately, many of us find the Documents module's structure too restrictive. The following is a list of several alternatives available to PeopleSoft developers:

• Documents module
• Undocumented JSON objects delivered by PeopleTools
• JSON.org Java implementation (now included with PeopleTools)
• JavaScript via Java's ScriptEngineManager

We will skip the first two options as there are many examples and references available on the internet. In this post, we will focus on the last two options in the list: JSON.org and JavaScript. Our scenario involves generating a JSON object containing a role and a list of the role's permission lists.

PeopleCode can do a lot of things, but it can't do everything. When I find a task unfit for PeopleCode, I reach out to the Java API. PeopleCode has outstanding support for Java. I regularly scan the class and classes directories of PS_HOME, looking for new libraries I can leverage from PeopleCode. One of the files in my App Server's class path is json.jar. As a person interested in JSON, how could I resist inspecting the file's contents? Upon investigation, I realized that json.jar contains the json.org Java JSON implementation. This is good news as I used to have to add this library myself. So how might we use json.jar to generate a JSON file? Here is an example

JSON.org has this really cool fluent design class named JSONStringer. If the PeopleCode editor supported custom formatting, fluent design would be really, really cool. For now, it is just cool. Here is an example of creating the same JSON using the JSONStringer:

What about reading JSON using json.org? The following example starts from the JSON string generated by JSONStringer. It is a little ugly because it requires Java Reflection to invoke the JSONObject constructor. On the positive side, though, this example demonstrates Java Class casting in PeopleCode (hat tip to tslater2006 for helping me with Java Class casting in PeopleCode)

What is that you say? Your PeopleTools installation doesn't have the json.jar (or jsimple.jar) files? If you like this approach, then I suggest working with your system administrator to deploy the JSON.org jar file to your app and/or process scheduler's Java class path

But do we really need a special library to handle JSON? By definition, JSON describes a JavaScript object. Using Java's embedded JavaScript script engine, we have full access to JavaScript. Here is a sample JavaScript file that generates the exact same JSON as the prior two examples:

... and the PeopleCode to invoke this JavaScript:

Did you see something in this post that interests you? Are you ready to take your PeopleTools skills to the next level? We offer a full line of PeopleTools training courses. Learn more at jsmpros.com.

Using PeopleCode to Read (and process) Binary Excel Files

At HIUG Interact last week, a member asked one of my favorite questions:

"Does anyone know how to read binary Microsoft Excel files from PeopleSoft?"

Nearly 15 years ago my AP manager asked me the same question, but phrased it a little differently:

"We receive invoices as Excel spreadsheets. Can you convert them into AP vouchers in PeopleSoft?"

Of course my answer was "YES!" How? Well... that was the challenge. I started down the typical CSV/FileLayout path, but that seems to be a temporary band aid, and challenging for the best users. I wanted to read real binary Excel files directly through the Process Scheduler, or basically, with PeopleCode. But here is the reality: PeopleCode is really good with data and text manipulation, but stops short of binary operations. Using PeopleCode's Java interface, however, anything is possible. After a little research, I stumbled upon Apache POI, a Java library that can read and write binary Excel files. With a little extra Java code to interface between PeopleCode and POI's Java classes, I had a solution. Keep in mind this was nearly 15 years ago. PeopleSoft and Java were both a little different back then and today's solution is slightly simpler. Here is a summary of PeopleSoft and Java changes that simplify this solution:

• As of PeopleTools 8.54, PeopleSoft now includes POI in the App and Process Scheduler server Java class path. This means I no longer have to manage POI as a custom Java library.
• The standard JRE added support for script engines and included the JavaScript script engine with every deployment. This means I no longer have to write custom Java to interface between POI and PeopleCode, but can leverage the dynamic nature of JavaScript.

How does a solution like this work? The ultimate goal is to process spreadsheet rows through a Component Interface. First we need to get data rows into a format we can process. Each language and operating environment has its strengths:

• PeopleCode can handle simple Java method invocations,
• JavaScript can handle complex Java method invocation without compilation,
• Java is really good at working with binary files, and
• PeopleCode and Component Interfaces play nicely together.

My preference is to capitalize on these strengths. With this in mind, I put together the following flow:

1. Use PeopleCode to create an instance of a JavaScript script interpeter,
2. Use JavaScript to invoke POI and iterate over spreadsheet rows, inserting row data into a temporary table, and
3. Use PeopleCode to process those rows through a component interface.

The code for this solution is in two parts: JavaScript and PeopleCode. Here is the JavaScript:

Next hurdle: where do we store JavaScript definitions so we can process them with PeopleCode? Normally we place JavaScript in HTML definitions. This works great for online JavaScript as we can use GetHTMLText to access our script content. App Engines, however, are not allowed to use that function. An alternative is to use Message Catalog entries for scripts. The following PeopleCode listing uses an HTML definition, but accesses the JavaScript content directly from the HTML definition Metadata table:

To summarize this PeopleCode listing, it first creates a JavaScript script engine manager, it then evaluates the above JavaScript, and finishes by processing rows through a CI (the CI part identified as a TODO segment).

This example is fully encapsulated in as few technologies as possible: PeopleCode and JavaScript, with a little SQL to fetch the JavaScript. The code will work online as well as from an App Engine. If this were in an App Engine, however, I would likely replace the JavaScript GUID section with the AE's PROCESS_INSTANCE. Likewise, I would probably use an App Engine Do-Select instead of a PeopleCode SQL cursor.

Did you see something on this blog that interests you? Are you ready to take your PeopleTools skills to the next level? We offer a full line of PeopleTools training courses. Learn more at jsmpros.com.