Node Nods to RPG and DB2

[et_pb_section bb_built=”1″ fullwidth=”on” specialty=”off” _builder_version=”3.17.2″ next_background_color=”#000000″][et_pb_fullwidth_post_title admin_label=”Blog Post Header” _builder_version=”3.17.2″ title=”off” meta=”off” categories=”off” comments=”off” featured_placement=”background” text_orientation=”center” module_class=”blog_post_header” title_font_size=”60px” background_blend=”saturation” title_font=”Raleway|on|||” title_text_color=”#274060″ title_letter_spacing=”2px” meta_font=”Raleway||||” meta_font_size=”20″ meta_text_color=”#274060″ meta_letter_spacing=”2px” /][/et_pb_section][et_pb_section bb_built=”1″ _builder_version=”3.0.63″ prev_background_color=”#000000″][et_pb_row _builder_version=”3.0.63″][et_pb_column type=”4_4″][et_pb_text admin_label=”Blog Post” _builder_version=”3.17.2″ module_alignment=”left” max_width_tablet=”50px”]

Originally published: IBM Systems Magazine on 3/10/2015

Updated author notes: Check with IBM to obtain newer versions of Node.js. If you install a newer version then know the paths to modules will change. Also note this was the original synchronous DB2 adapter and now there is an asynchronous one.

Interface with DB2 tables and existing RPG programs

In the previous article we showed how to get up and running with an initial Node.js “Hello World” web application. That was pretty neat and all 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.

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. The [[previous article(*Note: Node, The First Kiss*)]] showed how to setup your Node.js environment and start the REPL so we won’t be covering that here. Below 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();

Below what your screen should look like after loading the Node.js REPL and pasting the code. Side Note: Yes, the below screenshot is a browser tab, and yes it is an SSH session to my IBM i. I am 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 is 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() which 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 are 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 clean-up purposes.

Next let’s look at communicating with an RPG program using the XMLSERVICE Toolkit for Node.js. There are two components to this tool: 1 – the XMLSERVICE RPG code that IBM delivers in PTF SI50835(V7R1) or SI51153(V7R2), and 2 – 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).

Below 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% free-form because I didn’t want my middle name to be Aaron “Luddite” Bartell. I must admit I am still getting used to the 100% free form – 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;

Below is the Javascript code necessary to invoke the PGM1 RPG program. Go ahead and paste this into a Node.js REPL to see the end results. Note you need to compile the above 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 in 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 are 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 from – 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 which 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

This example was very simple in nature and the code is somewhat involved. That’s where encapsulation can come into play – effectively wrapping all the above 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 an iPgm.js in the future. Or maybe, just maybe, that’s something the community should develop?

[/et_pb_text][/et_pb_column][/et_pb_row][et_pb_row][et_pb_column type=”4_4″][et_pb_text admin_label=”Contact Form Title” _builder_version=”3.17.2″ background_layout=”dark” text_font=”Raleway|on|||” text_text_color=”#f45d01″ text_font_size=”26″ text_letter_spacing=”2px” module_alignment=”left”]

Need help integrating Node.js with your existing RPG? Let’s chat.

[/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]