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.

Monday, May 23, 2016

HIUG Interact 2016 Agenda

In a couple of weeks, I will be presenting the following sessions at the HIUG Interact 2016 conference in San Antonio

  • 16165 : PeopleSoft Fluid User Interface – Deep Dive: Grand Oaks D, Mon, Jun 13, 2016 (03:15 PM - 04:15 PM)
  • 16164 : PeopleTools Tips & Techniques: Grand Oaks D, Tue, Jun 14, 2016 (02:30 PM - 03:30 PM)
  • 16163 : Tech Clinic: Application Designer Grand Oaks D, Wed, Jun 15, 2016 (12:30 PM - 02:30 PM)

Tuesday, March 29, 2016

Collaborate 2016

I just wanted to post a quick note with my schedule for Collaborate 2016. I will be presenting PeopleSoft Developer Tips and Techniques on April 13th at 9:15 AM in Reef C. My publisher assured me that I will have a box of books waiting for me at the conference and we plan to give them away at various sessions during the conference. Here are a couple of other sessions I recommend attending at Collaborate:

There is one session I want to specifically highlight: A Designers Intro to the Oracle JET Framework. PeopleTools 8.55 includes Oracle JET, Oracle's brand new open source JavaScript Extension. You may never directly interact with Oracle JET, but it is always good to understand the tools and frameworks used by PeopleTools. Oracle JET is based on common JavaScript libraries such as RequireJS and Knockout and PeopleTools includes these related open source libraries (note: I have written about using RequireJS with PeopleSoft in prior posts).

Thursday, March 03, 2016

Alliance 2016

HEUG Alliance is next week. I hope you are registered. I know this will be a fun and informative conference (as always). I am scheduled for two sessions at Alliance:

  • PeopleSoft Developer: Tips and Techniques on Monday at 3:30 PM in room 6B/C and
  • PeopleSoft Meet the Experts on Tuesday at 10:15 AM in room 307/308 table 2.

When I'm not in sessions, you will find me in the demo grounds. Stop by and say "Hello!"

Tuesday, November 10, 2015

JavaScript and PeopleCode Array Parameters

I have been experimenting with scripting PeopleCode using JavaScript. This is possible because Java includes Mozilla's Rhino JavaScript engine. I took one of my experiments to OpenWorld 2015 which shows creating a Microsoft Excel Spreadsheet using POI and JavaScript. Here we are, a couple of weeks later, and I see this in the PeopleSoft OTN Discussion Forum: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke. Perfect! That is my exact use case from OpenWorld. I just happen to have a code sample to share on the forum. The developer's scenario was a bit more complicated. As you will note from the forum post, the developer needed to invoke SQL.Fetch from JavaScript. The JavaScript version of SQL.Fetch, which uses the PeopleCode Java interface, requires an array of selected columns. My first thought was just to use a standard JavaScript array. Since the SQL only has one column, I just needed an array with one item. This didn't work. JavaScript Arrays clearly are not Java Arrays. Here is an example:

var result = (function() {  
    var ReflectiveArray = java.lang.reflect.Array;  
    var CreateSQL = Packages.PeopleSoft.PeopleCode.Func.CreateSQL;  
    var columns = ReflectiveArray.newInstance(java.lang.Object,
        1 /* number of selected columns */);  
    var results = [];  
      
    SQL = CreateSQL("SELECT OPRDEFNDESC FROM PSOPRDEFN WHERE ROWNUM < 10");  
    while (SQL.Fetch(columns)) {  
        results.push(columns[0]);  
    }  
      
    return results.join();  
      
}());  

Monday, November 09, 2015

Western Canada Regional Users Group 2015

I will be in Calgary next week presenting PeopleTools topics at the Western Canada Regional Users Group meeting. My sessions are at 1 PM and 2:15 PM. See you there!

Friday, November 06, 2015

OpenWorld 2015 Presentations Available

OpenWorld 2015 presentations are now available in the content catalog. My PeopleTools Tips and Techniques session presentation is available here.