Friday, July 22, 2016

Dynamic Java in PeopleCode

The PeopleCode language has a lot of features and functions. But sometimes, it seems there are tasks we need to accomplish that are just out of reach of the PeopleCode language. It is at these times that I reach for Java. I have written a lot about Java, so I'm sure many of you already know how to mix Java with PeopleCode. While certainly a rational solution, one of the pain points of a Java solution is managing custom Java on the app and process scheduler servers. Each time you update your compiled class (or jar) files, you have to restart the server. That might be OK in a stable production environment, where you don't intend to change code often, but in development, it is a real pain! Likewise, maintaining custom Java class and jar files through upgrades can be a little sketchy. Specifically, if you redeploy PeopleTools or rewrite psconfig, then it is possible you may miss some of your custom Java code. PeopleBooks tells us how to setup psconfig for custom Java classes, but again, that is just one more thing to manage through upgrades. Now, imagine being able to update your custom Java code with a Data Mover script. Further, imagine being able to run custom Java without making any changes to your application server. Imagine what it would be like to run Java without having to beg (or bribe) your admin for a "no customization" exception. It is possible today. The answer: Use JavaScript to interface between PeopleCode and the delivered Java Runtime Environment. Through the embedded Mozilla Rhino JavaScript script engine of Java, we have full, dynamic access to the JRE. When and how would you use this? Let's review some examples.

Custom HTTP Connections

For various reasons, some customers choose not to implement Integration Broker. These customers find themselves requiring integration, but without IB's networking features. An alternative to %IntBroker.ConnectorRequestURL is to use Java's HttpURLConnection.I strongly discourage this approach, but the question arises. The JRE is there, well integrated with PeopleCode, and ready for use. From PeopleCode, it is possible to create a Java URLConnection using CreateJavaObject("java.net.URL", "http...").openConnection(). A problem arises when we try to invoke methods of a HttpURLConnection, the real return value of URL.openConnection. Unfortunately, PeopleCode doesn't see it that way, which leads down the reflection path (we don't want to go there). This is where JavaScript can help us. JavaScript doesn't mind that URL.openConnection returned an HttpURLConnection even though it said it would just return a URLConnection. Here is an example:

var result = (function() {
    // declare pointers to Java methods to make it easier to invoke the methods
    // by name later
    var URL = Packages.java.net.URL;
    var InputStreamReader = Packages.java.io.InputStreamReader;
    var BufferedReader = Packages.java.io.BufferedReader;
    var StringBuilder = Packages.java.lang.StringBuilder;
    
    var serverAddress = new URL(
            "http://hcm.oraclecloud.com/hcmCoreApi/atomservlet/employee/newhire"
        );
    
    
    // Creates an HttpURLConnection, but returns URLConnection. If I was using
    // PeopleCode, PeopleCode would see this as a URLConnection. To invoke
    // HttpURLConnection methods, I would need to resort to reflection. This is
    // the power of JavaScript in this scenario...
    var connection = serverAddress.openConnection();

    // ... for example, setRequestMethod is NOT a method of URLConnection. It is
    // a method of HttpURLConnection. PeopleCode would throw an error, but
    // JavaScript recognizes this is an HttpURLConnection and allows the method
    // invocation
    connection.setRequestMethod("GET");
    
    // Timeout in milliseconds
    connection.setReadTimeout(10*1000);
    
    // Finally, make the connection
    connection.connect();

    // Read the response
    var reader  = new BufferedReader(
        new InputStreamReader(connection.getInputStream()));
    var sb = new StringBuilder();
    var line;
    
    while ((line = reader.readLine()) !== null) {
      sb.append(line + '\n');
    }
    
    // Return the response to PeopleCode. In this case, the response is an XML
    // string
    return sb;
}());

Excel Spreadsheets

PeopleTools 8.55+ has a PeopleCode API for Excel, which means this solution is now irrelevant. I'm listing it because not everyone is up to PeopleTools 8.55 (yet). If you use this idea to build a solution for 8.54 and later upgrade, Oracle recommends that you switch to the PeopleCode Excel API. The solution will still work with 8.55+, but just isn't recommended post 8.54.

This solution uses the Apache POI library that is distributed with PeopleTools 8.54+ to read and write binary Microsoft Excel files. As with the networking solution above, it is possible to use POI directly from PeopleCode, but a little difficult because POI uses method overloading in a manner that PeopleCode can't resolve. Furthermore, POI uses methods that return superclasses and interfaces that PeopleCode can't cast to subclasses, leading to awful reflection code. Here is an example that reads a spreadsheet row by row, inserting each row into a staging table for later processing.

// endsWith polyfill
if (!String.prototype.endsWith) {
  String.prototype.endsWith = function(searchString, position) {
      var subjectString = this.toString();
      if (typeof position !== 'number' || !isFinite(position) ||
            Math.floor(position) !== position ||
            position > subjectString.length) {
        position = subjectString.length;
      }
      position -= searchString.length;
      var lastIndex = subjectString.indexOf(searchString, position);
      return lastIndex !== -1 && lastIndex === position;
  };
}

// open a workbook, iterate over rows/cells, and then insert them into a
// staging table
var result = (function() {
    // declare pointers to Java methods to make it easier to invoke the methods
    // by name
    var FileInputStream = Packages.java.io.FileInputStream;
    
    var HSSFWorkbook = Packages.org.apache.poi.hssf.usermodel.HSSFWorkbook;
    var Workbook = Packages.org.apache.poi.ss.usermodel.Workbook;
    var XSSFWorkbook = Packages.org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    // declare a PeopleCode function
    var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec;

    // internal "helper" function that will identify rows inserted into 
    var guid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g,
        function(c) {
            var r = Math.random()*16|0, v = c == 'x' ? r : (r&0x3|0x8);
            return v.toString(16);
        }
    );
    
    // open a binary Microsoft Excel file
    var fis = new FileInputStream(fileName);
    
    var workbook;
    
    if(fileName.toLowerCase().endsWith("xlsx")) {
        workbook = new XSSFWorkbook(fis);
    } else if(fileName.toLowerCase().endsWith("xls")) {
        workbook = new HSSFWorkbook(fis);
    }
    
    var sheet = workbook.getSheetAt(0);
    var rowIterator = sheet.iterator();
    var roleName,
        descr,
        row;

    // iterate over each row, inserting those rows into a staging table
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        roleName = row.getCell(0).getStringCellValue();
        descr = row.getCell(1).getStringCellValue();
        
        // TODO: turn this into a stored SQL definition, not hard coded SQL
        SQLExec("INSERT INTO PS_JM_XLROLE_STAGE VALUES(:1, :2, :3, SYSTIMESTAMP)",
            // notice that the SQLExec parameters are wrapped in an array
            [guid, roleName, descr]
        );
    }
    
    // return the unique identifier that can later be used to select the rows
    // inserted by this process
    return guid;

}());

Here is an example of writing/creating a Microsoft Excel spreadsheet:

var result = (function() {
    // import statements
    var XSSFWorkbook = Packages.org.apache.poi.xssf.usermodel.XSSFWorkbook;
    var FileOutputStream = Packages.java.io.FileOutputStream;

    // variable declarations
    var workbook = new XSSFWorkbook();
    var sheet = workbook.createSheet("Countries");
    var fileName = "c:/temp/countries.xlsx";
    
    var row = sheet.createRow(0);
    var cell = row.createCell(0);

    cell.setCellValue("United States of America");
    cell = row.createCell(1);
    cell.setCellValue("USA");

    row = sheet.createRow(1);
    cell = row.createCell(0);
    cell.setCellValue("India");
    cell = row.createCell(1);
    cell.setCellValue("IND");

    row = sheet.createRow(1);
    cell = row.createCell(0);
    cell.setCellValue("Denmark");
    cell = row.createCell(1);
    cell.setCellValue("DNK");

    var fos = new FileOutputStream(fileName);
    workbook.write(fos);
    fos.close();
    
    return "Created workbook " + fileName;

}());

JSON Parsing

If your goal is to convert a JSON string into SQL insert statements, then this is a very painless alternative:

/* Sample JSON data that will be selected from a record definition
[
    {"emplid": "KU0001", "oprid": "HCRUSA_KU0001"},
    {"emplid": "KU0002", "oprid": "HCRUSA_KU0002"},
    {"emplid": "KU0003", "oprid": "HCRUSA_KU0003"}
];*/

var result = (function() {
    var CreateRecord = Packages.PeopleSoft.PeopleCode.Func.CreateRecord;
    var Name = Packages.PeopleSoft.PeopleCode.Name;
    var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec;
    
    // example of how to reference a PeopleCode record definition from
    // JavaScript. Later we will select JSON_DATA from this table
    var rec = CreateRecord(new Name('RECORD', 'NAA_SCRIPT_TBL'));

    var count = 0;
    var json_string;
    var json;

    var guid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
        var r = Math.random()*16|0, v = c == 'x' ? r : (r&0x3|0x8);
        return v.toString(16);
    });
    
    // Select JSON string from a table. Normally this would come from a variable,
    // a service, etc. Here it makes a great example of how to select rows from
    // a record definition
    rec.GetField(new Name('FIELD', 'PM_SCRIPT_NAME')).setValue('JSON_TEST_DATA');
    rec.SelectByKey();
    json_string = rec.GetField(new Name('FIELD', 'HTMLAREA')).getValue();
    
    // now convert that received string into an object.
    json = JSON.parse(json_string);

    // Iterate over json data and...
    json.forEach(function(item, idx) {
        // ... insert into a staging table
        SQLExec("INSERT INTO PS_NAA_TEST_TBL VALUES(:1, :2, :3, SYSTIMESTAMP)",
            // notice the array wrapper around SQLExec bind values
            [guid, item.emplid, item.oprid]
        );
        count += 1;
    });
    
    return "Inserted " + count + " rows";
    
}());

I could go on and on with examples of creating zip files, encrypting information, base64 encoding binary data, manipulating graphics using Java 2D, etc, but I think you get the idea.

34 comments:

Gary F said...

Jim, have you experimented at all with passing variables back and forth at different steps of a PeopleSoft -> Java -> JavaScript interaction (e.g., dynamic data being written to spreadsheet). I know there's a bind method that lets you do something like this.

Jim Marion said...

@Gary, yes I have. There are two ways:

#1 as function parameters
#2 as global variables

With #2 you are basically declaring variable names and values.

It is my experience that this works really well with primitive data types (string, number, etc), and not so well with objects. That is why my examples include how to create PeopleCode objects from definitions. That way I can pass in a primitive string name for a definition and then create an instance of it in JavaScript.

The Java interface will retrieve any object and it will be an Object on the PeopleCode side. The problem is that it becomes an Object, which means any method invocation requires reflection. The whole point of this is to avoid reflection. With that in mind, I usually write JavaScript helper methods that will return primitives for each of the object properties, etc. Basically, I'm creating a helper/wrapper routine, which is what I would have done in Java anyway, so that I have something that is easier to work with from PeopleCode.

Unknown said...

Hi Jim...Great work explaining this concept. I was able to run with this idea on tools 8.49 and 1.5 JVM and the Rhino standalone JAR. Binding properties to the javascript context works a little differently so I wanted to post my code in case someone else is trying to implement a similar solution.

PeopleCode:

Local JavaObject &cx = CreateJavaObject("org.mozilla.javascript.Context");

&jobviteURL = "http://api.jobvite.com/api/v2/candidate?api=company&sc=key&format=json%20&start=1&count=5";

/* get json from jobvite. Note that I'm not setting the content type of the header but that doesnt seem to be an issue returning the jason*/
/* also this is connecting NOT through SSL - ssl connection may take some extra work if its needed */
&JSONfromJobvite = %IntBroker.ConnectorRequestUrl(&jobviteURL);

/* setup javascript runtime environment */
&cx = &cx.enter();
&scope = &cx.initStandardObjects();

&scriptableObject = GetJavaClass("org.mozilla.javascript.ScriptableObject");

/* BIND the returned json on a variable in the javascript */
&scriptableObject.putProperty(&scope, "json_data", &JSONfromJobvite);

/* get the javascript code */
&string = GetHTMLText(HTML.XX_JSCRIPT_JOBVITE_STRING);

/* run the javascript */
&return = &cx.evaluateString(&scope, &string, "EvaluationScript", 1, Null);

%Response.Write("complete");

The javascript...

var result = (function() {
// declare pointers to Java methods to make it easier to invoke the methods
// by name later
var CreateRecord = Packages.PeopleSoft.PeopleCode.Func.CreateRecord;
var Name = Packages.PeopleSoft.PeopleCode.Name;
var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec;

var rec = CreateRecord(new Name('RECORD', 'XX_JSON_DATA'));

//this is javascript!!!


//json data is passed to the jscript context via the json_data variable. parsing it allows easy dot notation access
//to its members

json = JSON.parse(json_data);

//loop over the candidates object

for(var i = 0; i < json.candidates.length ; i++) {

//insert value to table from the candidate object

SQLExec("INSERT INTO PS_XX_JSON_DATA VALUES(:1, :2 )",
// notice the array wrapper around SQLExec bind values
[ i , json.candidates[i].firstName.toString() ]
);


}
// Return the response to PeopleCode.
// string
return "ok";
}());

Jim Marion said...

@Andy, that is perfect! Thank you for sharing.

Nalini said...

Jim,
We are on PT 8.55, we are working with portal objects. When we look at the navigation collections and the folders, each folder is having folder attributes, and the most common name is PTPP_IS_PWDEFN with attribute value Y. I am trying to understand what these folder attributes really mean and how do we identify which one to use when trying to use the Nav collection as landing page.

Thanks for your help !!!
Nalini

Jim Marion said...

@Nalini, I don't believe there is a published list of those attributes. The best way to find the list is to query the PeopleTools PSPRSM%/PSPRDM% tables.

Nalini said...

Jim,
Is it going to be pick one by one until you find which one suits your needs? Should we start with PTPP_IS_PWDEFN and keep moving.

Thanks for your help !!
Nalini

Jim Marion said...

I thought that location in the portal registry determined if a folder was a nav collection, not attributes. I believe the attributes are used to specify nav collection configuration information.

PS Developer said...

Jim,
Can you use custom funclibs or application packages in javascript?

Jim Marion said...

Yes, absolutely! App Packages are available through a Java interface and are documented in the Java section of PeopleBooks. FUNCLIBs are not available directly, but through App Class wrappers.

Unknown said...

Jim,

We just recently upgraded to PT 8.54 and are having some issues with the Apache POI libraries that are used for psquery. We have a very large client with over 350K employees and our HR users need to run queries with all employees in the result. When running to excel we are running into GC overhead limit reach errors and have to restart our PSQRYSRV processes to reset the heap. Any thoughts on how best to tune appserver memory for these new libraries with larger results?

Thanks

Jim Marion said...

@Steve, I don't think query uses POI. PS/nVision uses POI. Besides that, no I'm sorry, I don't have recommendations. You might try posting the tuning and confguration question on the OTN PeopleSoft discussion forum.

Unknown said...
This comment has been removed by the author.
Unknown said...

Hi Jim,

I am very happy to send this post, i have one quick question. my requirement was user's while click on one hiperlink/pushbutton one excel sheet should be downloaded based online line data. so how to write the code and if incase if i create Java script how to pass my bind values.

Please let me know if you have any idea about that it's very helpful for me.


Thanks,
Ram Y.

Jim Marion said...

@Ram the easiest way today is to use the PeopleCode API for Excel. If that isn't an option, then you can pass bind values or other parameters to the ScriptEngineManager as variables. You can see an example in this blog post: https://jjmpsj.blogspot.com/2015/09/javascript-on-app-server-scripting.html.

Durai said...

Hi Jim,

How do we invoke JAR file from PeopleCode? I gave the following code and getting class not found error.
Local JavaObject &jExcel = GetJavaClass("java.lang.class");

&ret = &jExcel.ExcelCopyData("/intfc/HCMDEVD/Job_Profiles_Test.xlsx", "data", 1);

I'm getting Java Exception: java.lang.NoClassDefFoundError
ANy inputs?

Thanks
Durai

Durai said...

Hi Jim,

How do we invoke JAR file from PeopleCode? I gave the following code and getting class not found error.
Local JavaObject &jExcel = GetJavaClass("java.lang.class");

&ret = &jExcel.ExcelCopyData("/intfc/HCMDEVD/Job_Profiles_Test.xlsx", "data", 1);

I'm getting Java Exception: java.lang.NoClassDefFoundError
ANy inputs?

Thanks
Durai

Jim Marion said...

@Durai, Java is case sensitive, so it would be java.lang.Class.

Regarding jars, just make sure they are in the app or process scheduler class path.

Durai said...

Thanks Jim. Do we need to place the JAR file on both App & process scheduler class path?

Jim Marion said...

@Durai, only if you plan to use it in both. If it will only be used for online PeopleCode (iScripts, FieldChange, etc), then just put it in the app server. Likewise, if it is just used in AppEngine, then put it in the process scheduler.

Durai said...

@Jim- Getting the following error while referring the JAR file. What version do we need to look for?

Java Exception: java.lang.UnsupportedClassVersionError: CopyExcelData : Unsupported major.minor vers ion 52.0: finding class CopyExcelData (2,725)

Thank you

Jim Marion said...

@Durai, it depends on your PeopleTools release. The easiest way to find out is to find the JRE that is used with the app server and run java -version.

Durai said...

@Jim- Do we need to include package path in the Java program? I'm keep on getting
Java Exception: java.lang.NoClassDefFoundError
Any input or reference within PeopleCode. Thank you.

Jim Marion said...

@Durai,

Step 1: deploy Java to the app server's classpath. This should be something like $PS_HOME/appserv/classes. You can also modify the classpath in psappsrv.cfg.

Step 2: Restart the app server. PeopleSoft loads Java with the first Java request, and then doesn't unload the JVM until restarting the app server. Jars in that folder are concatenated into the classpath by startup scripts.

Step 3: Run PeopleCode to test.

You can also test locally in an App Engine run through App Designer. The great benefit with this approach is you don't have to restart the app server until you have tested and are ready to deploy.

Durai said...

Thanks Jim, I used Exec command with AE to call JAR file. Working as required.

Jim Marion said...

That works if the Java is utility in nature and you don't need to process a response. In some respects it may be easier for a developer to manage because you have complete control over the class path.

Unknown said...

Being new to the PeopleSoft and PeopleCode environment (started 3 months ago) I'm quickly learning the ins and outs of these tools. I've recently found myself wanting to use the tricks described in this post. Namely calling JS from with PeopleCode and then being able to call PC functions from JS.

Currently I'm just trying to call SQLExec via JS like so:

var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec;
SQLExec("update tableName set column1 = current_timestamp WHERE column2 = :1", ['9923']);

I'm getting an Uncaught ReferenceError in the browser console, Packages is not defined.

We are on an old set of tools (8.49) which might be the only part of the problem.

Thanks for any input.

Jim Marion said...

@Unknown, great comment! The JavaScript in this blog MUST be run in the context of the app server JVM. It CANNOT be run from a browser console. It would be awesome if it were that easy! But then that might be considered a security vulnerability.

Viswanatha Reddy said...

Hi Jim

I would like to call function within DLL from peoplecode, in that case where should we place the DLL?

Thanks
Vis

Jim Marion said...

@Viswanatha, PeopleSoft follows standard Operating System path loading procedures. Operating systems vary, but the search path usually involves the same folder where the executable loaded, the path environment variable, etc.

Unknown said...

Hi Jim.

I am trying to access a zip file via a REST API going out to a third party. Attempting to replicate your Custom HTTP Connections example. But I am getting an SSL error. The url I'm attempting to access is a HTTPS. Any suggestions? Should your example work for an https url too?

Thanks!
Tom

Jim Marion said...

Hi Tom,

The JavaDoc says there should already be a protocol handler for https, so I believe the answer is Yes: https://docs.oracle.com/javase/7/docs/api/java/net/URL.html. Here is a Java example that accesses a site through SSL: https://mkyong.com/java/java-https-client-httpsurlconnection-example/.

Parth said...

Hi Jim,

We have a requirement where PeopleSoft has to parse JWT token in Sign-on PeopleCode.
Kindly shed some light on this matter. How to achieve it through Peoplecode.

Jim Marion said...

@Parth, great question! A JWT is a three-part token that uses a modified base64 encoding. The final part contains a signature that we are supposed to verify. Common encryption for the signature uses passphrases (HMAC SHA 256) or certificates. In its simplest form, without signature verification, you would do something like this:

Local Array of String &parts = Split (&jwt, ".");

Each part would then contain the modified base64. You then decode the part and JSON Parse it using the JsonParser.

I have this all documented in our Integration Tools courses, but don't have the algorithm published outside yet.