You are currently on IBM Systems Media’s archival website. Click here to view our new website.

IBM i > DEVELOPER > GENERAL

Node Nods To RPG and DB2


In the “Node, The First Kiss” I showed how to get up and running with an initial Node.js “Hello World” Web application. That was pretty neat but the rubber hits the road when we start being able to interface with DB2 tables and existing RPG programs. The good thing is that IBM was fully aware this was a necessity and has given us some good tools to accomplish the task.

Connecting to DB2

First let’s work through connecting to DB2 and retrieving some data. Instead of creating a Web application, we’re going to use the Node.js REPL program to copy/paste code to more quickly and easily test functionality. My previous article showed how to set up your Node.js environment and start the REPL so I won’t be covering that here. Following is a complete Node.js script you can copy/paste right into the Node.js REPL.

var db = require('/QOpenSys/QIBM/ProdData/Node/os400/db2i/lib/db2');
db.init();
db.conn("*LOCAL");
db.exec("SELECT LSTNAM,CITY FROM QIWS.QCUSTCDT", function(result_set) {
 console.log(result_set);
})
db.close();

Figure 1 is what your screen should look like after loading the Node.js REPL and pasting the code. (Yes, the screenshot is a browser tab in an SSH session to my IBM i. I’m using the Secure Shell Chrome plugin developed by Google which I’ve found to be a nice alternative on Windows compared to putty and cygwin.)

Let’s talk through what’s going on. First we see a require statement to bring in the db2 module. The full path needs to be specified because the db2 module it isn’t in a location that Node.js will look for it. Node.js looks for modules in folders named node_modules, first in the current directory and then traverses up the directory structure until it gets to the root.

Next we have db.init() that will, you guessed it, initialize the database. You can specify a number of other parameters on db.init() as described in the documentation. After that, the specific database to connect to is specified. In this case, we specified *LOCAL because we’re going after the database on the same server that this script is running. The call to db.exec() is where the action starts happening. The first parameter is the SQL statement. The QCUSTCDT table should already be on your machine in library QIWS because it comes with the operating system. The second parameter is a JavaScript anonymous function that receives in a single parameter named result_set and then outputs the contents to the screen with console.log(). On the screen, we can see result_set was a JavaScript array with multiple objects. Lastly, the db.close() is invoked for cleanup purposes.

Connecting With RPG

Next let’s look at communicating with an RPG program using the XMLSERVICE Toolkit for Node.js. The tool has two components: the XMLSERVICE RPG code that IBM delivers in PTF SI50835(V7R1) or SI51153(V7R2), and the Node.js JavaScript portion. In this example, we’re going to focus on the Node.js portion and how it interfaces with the XMLSERVICE RPG side. You can learn more about XMLSERVICE on the YoungiProfessionals.com/wiki site. One important feature of XMLSERIVCE you should know is that it can receive calls via HTTP or DB2 stored procedures. This means that, if necessary or required, your Node.js application can reside on a separate machine and make HTTP calls to XMLSERIVCE to call on IBM i resources (RPG programs, DB2 tables, etc).

Following is a sample RPG program named PGM1 that has two pass-by-reference parameters. The program is very simple in that it only gives new values to the parameters and returns, but you should know that XMLSERIVCE can work with very complex parameter lists. I determined to write it in 100 percent free-form because I didn’t want my middle name to be Aaron “luddite” Bartell. I must admit I’m still getting used to free form. It’s a lot for my eyes to take in.

dcl-pr pgm1 extpgm; 
    char1 char(1);
    dec1 packed(7:4);
end-pr;
dcl-pi pgm1;
    char1 char(1);
    dec1 packed(7:4);
end-pi;

char1 = 'C';
dec1 = 321.1234;
return;

Following is the JavaScript code necessary to invoke the PGM1 RPG program. Paste this into a Node.js REPL to see the end results. You must compile the aforementioned RPG program first.

var xt = require('/QOpenSys/QIBM/ProdData/Node/os400/xstoolkit/lib/itoolkit');
var conn = new xt.iConn("*LOCAL");

var pgm = new xt.iPgm("PGM1", {"lib":"MYLIB"});
pgm.addParam("","1A");
pgm.addParam("0", "7p4");

conn.add(pgm.toXML());

conn.run(function (rsp) {
 var results = xt.xmlToJson(rsp);
 results.forEach(function(result, index){
   result.data.forEach(function(data, index2){
     console.log("type:" + data.type + " value:" + data.value);
   });
 });
});

Let’s break these down line by line. The first line is bringing in the itoolkit functionality located at the path specified. Note the file is actually named itoolkit.js but the extension can be omitted when using the require API. What’s cool is you can actually view the itoolkit.js source to learn how everything was put together –take a peek yourself.

var xt = require('/QOpenSys/QIBM/ProdData/Node/os400/xstoolkit/lib/itoolkit');

Next we see the PGM1’s parameter list being defined along with inbound values, though in this case we’re specifying default values of blank and zero. You can learn about the various data types by looking at the itoolkit.js file.

var pgm = new xt.iPgm("PGM1", {"lib":"MYLIB"});
pgm.addParam("","1A");
pgm.addParam("0","7p4");

Next we see the conn.add statement, which receives in an XML document declaring the structure for the call to PGM1. This is somewhat where XMLSERVICE gets its name—in that the interface to it is all XML-based. This is great for flexibility and loose coupling.

conn.add(pgm.toXML());

The next portion of code, conn.run(), is the actual invocation of PGM1. The first parameter to conn.run() is a JavaScript anonymous function that receives back the response from XMLSERVICE - pure XML. The result is converted to JSON (JavaScript Object Notation) with the call to xt.xmlToJson() to make the results easier to work through. Given the results variable is now a JavaScript object, we can easily traverse it with native JavaScript language constructs (i.e., forEach).

conn.run(function (rsp) {
 var results = xt.xmlToJson(rsp)
 results.forEach(function(result, index){
   result.data.forEach(function(data, index2){
     console.log("type:" + data.type + " value:" + data.value);
   });
 });
});

The end result is as follows where we display the response type and value.

type:1A value:C
type:7p4 value:321.1234

Wrap in an API?

This example was very simple in nature and the code is somewhat involved. That’s where encapsulation can come into play—effectively wrapping all this with an easy to call API. If you look in folder /QOpenSys/QIBM/ProdData/Node/os400/xstoolkit/lib/ you’ll see IBM has already started doing this with the likes of iUserSpace.js, iProd.js and iObj.js. My hope is IBM will create a iPgm.js in the future. Or maybe, just maybe, that’s something the community should develop?

Aaron Bartell is Director of IBM i Innovation for Krengel Technology Inc. and an IBM Champion.



Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



Advertisement

Advertisement

2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Are You Multilingual?

Rational enables development in multiplatform environments

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters