Wednesday, September 30, 2015

JavaScript on the App Server: Scripting PeopleCode

It has been nearly a decade since I started playing with JavaScript on the PeopleSoft application server. Back then I had to deploy a couple of JAR files to the app server. At that time, maintaining and deploying unmanaged files seemed more headache than benefit. Today Java provides full scripting support through the ScriptEngineManager and embedded Mozilla Rhino JavaScript script engine. Why would I want to script PeopleCode? Here are a few of my favorite reasons:

  • Low-level socket communication
  • Avoid reflection: JavaScript executes all methods regardless of variable type whereas PeopleCode only recognizes the returned type, not the real type
  • Process simple JSON structures that can't be modeled with the Documents module

Here is the PeopleCode required to invoke JavaScript

Local JavaObject &manager =  CreateJavaObject("javax.script.ScriptEngineManager");
Local JavaObject &engine =  &manager.getEngineByName("JavaScript");

REM ** Evaluate a simple JavaScript;
&engine.eval("var result = Math.random();");

REM ** Access the value of the JavaScript variable named result;
Local string &result_text =  &engine.get("result").toString();

Here is some JavaScript that converts the variable &json_string into a JSON Array and then iterates over each entry, inserting values into a table. Notice that I'm invoking the PeopleCode SQLExec function from JavaScript.

var result = (function() {
  var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec;
  var json = JSON.parse(json_string);
  var count = 0;
  json.forEach(function(item, idx) {
    SQLExec("INSERT INTO ... SYSTIMESTAMP", [idx, item]);
  return count + " rows inserted";

Where did that &json_string variable come from? Here:

&engine.put("json_string", "[""item1"", ""item2"", ""item3""]");


Jared Norris said...

Hi Jim,

I'm trying to use this method to parse JSON and insert into a table, but it seems that SQLExec is not available for some reason. I am on PeopleTools 8.53. Do you know how I could troubleshoot this?

Java Exception: javax.script.ScriptException: Can't find method PeopleSoft.PeopleCode.Func.SQLExec(string,string,string,string,string,string,string,string). (#2) in at line number 2: during call of javax.script.ScriptEngine.eval. (2,763)

My code:
Local JavaObject &manager = CreateJavaObject("javax.script.ScriptEngineManager");
Local JavaObject &engine = &manager.getEngineByName("JavaScript");
Local string &str_jsFunction = "";
&str_jsFunction = &str_jsFunction | "var result = (function() {";
&str_jsFunction = &str_jsFunction | "var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec;";
&str_jsFunction = &str_jsFunction | "var jsonIn = " | &msg_CalendarList_Response.GetContentString() | ";";
&str_jsFunction = &str_jsFunction | "var json = JSON.parse(JSON.stringify(jsonIn));";
&str_jsFunction = &str_jsFunction | "var count = 0;";
&str_jsFunction = &str_jsFunction | "var ITEM = '';";
&str_jsFunction = &str_jsFunction | "SQLExec('TRUNCATE TABLE PS_UC_ADA_JSON_IN');";
&str_jsFunction = &str_jsFunction | ", idx) {";
&str_jsFunction = &str_jsFunction | "ITEM = 'IDX: ' + idx + ' item.length: ' + item.length + 'item[0]: ' + item[0];";
&str_jsFunction = &str_jsFunction | "SQLExec('INSERT INTO PS_UC_ADA_JSON_IN (UC_ADA_JSON_TITLE, UC_ADA_JSON_STRM, UC_ADA_JSON_SDTTM, UC_ADA_JSON_EDTTM, UC_ADA_JSON_EXMKEY, UC_ADA_JSON_SISKEY, UC_ADA_JSON_LOCATN) ', item[0], item[1], item[2], item[3], item[4], item[5], item[6]);";
&str_jsFunction = &str_jsFunction | "count++;";
&str_jsFunction = &str_jsFunction | "});";
&str_jsFunction = &str_jsFunction | "return count + ' rows inserted';";
&str_jsFunction = &str_jsFunction | "})();";


Jim Marion said...

@Jared, I believe the problem is with your method call. Packages.PeopleSoft.PeopleCode.Func.SQLExec has just two parameters:

1: SQL string
2: Array containing bind variables and output fields

Notice the brackets around the parameters in my example:

SQLExec("INSERT INTO ... SYSTIMESTAMP", [idx, item]);

idx and item are surrounded by brackets to signify an Array.

What do you think about moving your code into an HTML definition or a message catalog entry? I have trouble reading embedded code with all the quotes and concatenations.

PS Dev said...

Thanks for the great post! How can invoke javacript stored in HTML document. The normal way would be GetHTMLText(HTML.XXXX, "MyFunction()"); I'm a bit confused how it will be called with eval?

Jim Marion said...

@PSDev, It would look something like this:

Local string &script = GetHTMLText(HTML.XXXX, "bind1", "bind2");

HTML definitions are a great place to store JavaScript. GetHTMLText doesn't invoke JavaScript, it just selects the content of the HTML definition from the metadata store (along with bind variable replacement). With this in mind, you would use GetHTMLText to extract the JavaScript, and then eval it using the JavaScript Engine.