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]);
    count++;
  });
  return count + " rows inserted";
}());

Where did that &json_string variable come from? Here:

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

9 comments:

Unknown 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?

Error:
Java Exception: javax.script.ScriptException: sun.org.mozilla.javascript.internal.EvaluatorException: 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 | "json.data.forEach(function(item, 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 | "})();";

&engine.eval(&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.

Unknown 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");
&engine.eval(&script);

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.

lifeandshit said...

Jim I have a question. Its quite important and really tricky to understand but here I go.
Suppose the peoplesoft is running on an https with a SSL certificate on it. Now one of my page calls an API which has only https on it no SSL certificate. I am calling the ajax post method on the page to the API through javascript. Should it work or it shouldn't? What should I do? Should I write the javascript on the application server like u did in the above blog post will that solve my problem?

Jim Marion said...

@life... you are right, I didn't fully understand the question. The problem with an Ajax request is going to be protocol and domain. Cross domain Ajax will not work. Likewise, cross protocol Ajax will not work.

BS said...

Hi,
I have one doubt different topic i.e. on REST PATCH method. We don't have REST PATCH method in PS 8.56. Is there any alternative to perform similar operation like REST PATCH method from PeopleSoft?
Thanks.

Unknown said...

Hi Jim

I created a HTML definition to load some custom Javascript on the page. I wanted to make it dynamic by querying a PeopleSoft table using the SQLExec function. but when I include the statement var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec like you did its throwing me an error saying Packages is not defined. How can my Javascript find the delivered Packages.

Please help

Thanks
Ravi.

Jim Marion said...

@Ravi, that is correct. My JavaScript is running in the context of the App Server and within the JVM of the App Server. Yours is running in a web browser. To invoke database commands from the web browser, you need to make an Ajax request to the server and have the server run the commands.