Node Js

Yep, you heard correct, IBM i supports node.js via PTF.

PTF Number	In Group	                         Description
SI55499                                                    It ships install exit programs and base program files.
SI55638           SF99713-5,SF99368-31    Make program node/npm as global commands.
SI55747                                                    Update notices.txt and enbale profile valiation for xstoolkit.
SI56246           SF99713-6,SF99368-32    Upgrade node to v0.10.35. Fix the profile validation issue for xstoolkit.

Find tests in PTF

  • DB2 for i - /QOpenSys/QIBM/ProdData/Node/os400/db2i/test
  • Toolkit for i - /QOpenSys/QIBM/ProdData/Node/os400/xstoolkit/test
  • Run test
    ssh me@myibmi (call qp2term)
    > export PATH=/QOpenSys/QIBM/ProdData/Node/bin
    > export LIBPATH=/QOpenSys/QIBM/ProdData/Node/bin
    > node test.js
    

Our friends at Litmis/Krengel made a bitbucket repository for Node.js on IBM i …

IBM i toolkit on bluemix to IBM i …

CCSID issues

Have CCSID issues? Junk characters? Nothing works?

  • IBM i actions, see CCSID

Example using DB2 CCSID 1141 and nodejs unicode characters.

===
table - (using strsql)
===
create schema test
create table test/italy(f1 char(10) ccsid 1141)

==
output (browser is ok)
==
เศ่้์๒๙ 
[{"F1":"เศ่้์๒๙ "},{"F1":"เศ่้์๒๙ "},{"F1":"เศ่้์๒๙ "}] 
[{"00001":"4474545158CDDD404040"},{"00001":"4474545158CDDD404040"},{"00001":"4474545158CDDD404040"}] 
[{"00001":"00E000C800E800E900EC00F200F9002000200020"},{"00001":"00E000C800E800E900EC00F200F9002000200020"},{"00001":"00E000C800E800E900EC00F200F9002000200020"}] 

===
run
===
bash-4.3$ export CCSID=1208
bash-4.3$ node mynode.js 
Server listening on: 8080

==
code
==
mynode.js
var db = require('/QOpenSys/QIBM/ProdData/Node/os400/db2i/lib/db2.js');
//Lets require/import the HTTP module
var http = require('http');
//Lets define a port we want to listen to
const PORT=8080; 
//We need a function which handles requests and send response
function handleRequest(request, response){
    var str = '';
    var str1 = '';
    var str2 = '';
    var titl = "Italy";
    // real unicode characters below (utf-8 nodejs)
    var body = "\u00E0\u00C8\u00E8\u00E9\u00EC\u00F2\u00F9";
    // db.debug(true);
    db.init();
    db.conn("*LOCAL");
    db.exec("INSERT INTO TEST.ITALY (f1) values('"+body+"')");
    db.exec("SELECT * FROM TEST.ITALY", function(jsonObj) { str = JSON.stringify(jsonObj); });
    db.exec("SELECT HEX(f1) FROM test.italy", function(jsonObj) { str1 = JSON.stringify(jsonObj); });
    db.exec("SELECT HEX(cast(f1 as vargraphic(20) ccsid 1200) ) FROM test.italy", function(jsonObj) { str2 = JSON.stringify(jsonObj); });
    db.commit();
    db.close();
    var code =  [
        '<!DOCTYPE html>',
        '<html>',
            '<head>',
                '<meta charset="utf-8" />',
                '<title>' + titl + '</title>',
            '</head>',
            '<body>',
                '<br>' + body,
                '<br>' + str,
                '<br>' + str1,
                '<br>' + str2,
            '</body>',
        '</html>'
    ].join('\n');
    response.writeHead(200, {"Content-Type": "text/html; charset=utf-8"});
    response.write(code, "utf-8");
    response.end(); 
}
//Create a server
var server = http.createServer(handleRequest);
//Lets start our server
server.listen(PORT, function(){
    //Callback triggered when server is successfully listening. Hurray!
    console.log("Server listening on: %s", PORT);
});

DB2 for i test (included PTF)

  • /QOpenSys/QIBM/ProdData/Node/os400/db2i/test/basic.js
    // This tests the basic functions of the DB2 extension. 
    // --The testConn() tests the connection functions.
    // --The testQurey() queries the built-in table QIWS.QCUSTCDT and returns the result as a JSON object.
    // --The testServerMode() verifies the serverMode() API.
    // --The testAutoCommit() verifies the autoCommit() API.
    // --The testCursorType() verifies the cursorType() API.
    // --The testEnvAttr() verifies the setEnvAttr()/getEnvAttr() API.
    // --The testConnAttr() verifies the setConnAttr()/getConnAttr() API.
    // --The testStmtAttr() verifies the setStmtAttr()/getStmtAttr() API.
    // --The testSimpleSQL() creates a new table TEST.NAMEID and insert a row of record. Then queries the table and drop it.
    
    var db = require('../lib/db2.js');
    var conf = require('../../config.js');
    
    function testConn() {
    	console.log("\n  testConn()\n----------------------------------");
    	try{
    		db.debug(true);
    		db.init();
    		db.conn(conf.Database);
    		db.close();
    
    		db.init();
    		db.conn(conf.Database, function(){
    			console.log("call back in db.conn().")
    		});
    		db.close();
    
    		db.init();
    		db.conn(conf.Database, conf.User, conf.Password);
    		db.close();
    
    		db.init();
    		db.conn(conf.Database, conf.User, conf.Password, function(){
    			console.log("call back in db.conn().")
    		});
    		db.close();
    		return 0;
    	} catch(e) {
    		console.log(e);
    		return -1;
    	}
    };
    
    function testQurey() {
    	console.log("\n  testQurey()\n----------------------------------");
    	var rc = db.SQL_SUCCESS;
    	try{
    		db.debug(true);
    		db.init();
    		db.conn(conf.Database);
    		db.exec("SELECT LSTNAM, STATE FROM QIWS.QCUSTCDT", 
    			function(jsonObj) {
    				console.log("Result: %s", JSON.stringify(jsonObj));
    			}
    		);
    		db.close();
    		return 0;
    	} catch(e) {
    		console.log(e);
    		return -1;
    	}
    };
    
    function testServerMode() {
    	console.log("\n  testServerMode()\n----------------------------------");
    	var mode = true;
    	try{
    		db.debug(true);
    		db.init( function(){
    			db.serverMode(true);
    			if(mode != db.serverMode()) {
    				db.close();
    				return -1;
    			}
    			mode = false;
    			db.serverMode(mode);
    			if(mode != db.serverMode()) {
    				db.close();
    				return -1;
    			}
    		});
    		db.close();
    		return 0;
    	} catch(e) {
    		console.log(e);
    		return -1;
    	}
    } 
    
    function testAutoCommit() {
    	console.log("\n  testAutoCommit()\n----------------------------------");
    	var mode = true;
    	try{
    		db.debug(true);
    		db.init();	
    		db.conn(conf.Database, function(){
    			db.autoCommit(mode);
    			if(mode != db.autoCommit()) {
    				db.close();
    				return -1;
    			}
    			mode = false;
    			db.autoCommit(mode);
    			if(mode != db.autoCommit()) {
    				db.close();
    				return -1;
    			}
    		});
    		db.close();
    		return 0;
    	} catch(e) {
    		console.log(e);
    		return -1;
    	}
    } 
    
    function testCursorType() {
    	console.log("\n  testCursorType()\n----------------------------------");
    	var mode = db.SQL_CURSOR_DYNAMIC;
    	try{
    		db.debug(true);
    		db.init();
    		db.conn(conf.Database);
    		db.cursorType(mode);		
    		if(mode != db.cursorType()) {
    			db.close();
    			return -1;
    		}
    		mode = db.SQL_CURSOR_FORWARD_ONLY;
    		db.cursorType(mode);
    		if(mode != db.cursorType()) {
    			db.close();
    			return -1;
    		}
    		db.close();
    		return 0;
    	} catch(e) {
    		console.log(e);
    		return -1;
    	}
    } 
    
    function testEnvAttr() {
    	console.log("\n  testEnvAttr()\n----------------------------------");
    	var mode = db.SQL_TRUE;
    	try{
    		db.debug(true);
    		db.init(function(){
    			db.setEnvAttr(db.SQL_ATTR_ENVHNDL_COUNTER, mode);
    			if(mode != db.getEnvAttr(db.SQL_ATTR_ENVHNDL_COUNTER)) {
    				db.close();
    				return -1;
    			}
    			mode = db.SQL_FALSE;
    			db.setEnvAttr(db.SQL_ATTR_ENVHNDL_COUNTER, mode);
    			if(mode != db.getEnvAttr(db.SQL_ATTR_ENVHNDL_COUNTER)) {
    				db.close();
    				return -1;
    			}
    		});
    		db.close();
    		return 0;
    	} catch(e) {
    		console.log(e);
    		return -1;
    	}
    } 
    
    function testConnAttr() {
    	console.log("\n  testConnAttr()\n----------------------------------");
    	var mode = "MYLIB";
    	try{
    		db.debug(true);
    		db.init();	
    		db.conn(conf.Database, function(){
    			db.setConnAttr(db.SQL_ATTR_DBC_DEFAULT_LIB , mode);
    			if(mode != db.getConnAttr(db.SQL_ATTR_DBC_DEFAULT_LIB )) {
    				db.close();
    				return -1;
    			}
    		});
    		db.close();
    		return 0;
    	} catch(e) {
    		console.log(e);
    		return -1;
    	}
    } 
    
    function testStmtAttr() {
    	console.log("\n  testStmtAttr()\n----------------------------------");
    	var mode = db.SQL_TRUE;
    	try{
    		db.debug(true);
    		db.init();
    		db.conn(conf.Database);
    		db.setStmtAttr(db.SQL_ATTR_FOR_FETCH_ONLY, mode);		
    		if(mode != db.getStmtAttr(db.SQL_ATTR_FOR_FETCH_ONLY)) {
    			db.close();
    			return -1;
    		}
    		mode = db.SQL_FALSE;
    		db.setStmtAttr(db.SQL_ATTR_FOR_FETCH_ONLY, mode);		
    		if(mode != db.getStmtAttr(db.SQL_ATTR_FOR_FETCH_ONLY)) {
    			db.close();
    			return -1;
    		}
    		db.close();
    		return 0;
    	} catch(e) {
    		console.log(e);
    		return -1;
    	}
    } 
    
    function testSimpleSQL() {
    	console.log("\n  testSimpleSQL()\n----------------------------------");
    	try{
    		db.debug(true);
    		db.init(function(){
    			db.serverMode(true);
    		});
    		db.conn(conf.Database, conf.User, conf.Password, function(){
    			db.autoCommit(true);
    		});
    		db.exec("CREATE TABLE TEST.NAMEID (ID INTEGER, NAME VARCHAR(50), SALARY DECIMAL(8,2))");
    
    		db.exec("INSERT INTO TEST.NAMEID VALUES (0, 'David', 999.99)");
    		console.log("There are %d rows affected.", db.numRows());
    		//TODO: Add 
    
    		db.exec("SELECT * FROM TEST.NAMEID", 
    			function(jsonObj) {
    				console.log("Result: %s", JSON.stringify(jsonObj));
    				var fieldNum = db.numFields();
    				console.log("There are %d fields in each row.", fieldNum);
    				console.log("Name | Length | Type | Precise | Scale | Null");
    				for(var i = 0; i < fieldNum; i++)
    					console.log("%s | %d | %d | %d | %d | %d", db.fieldName(i), db.fieldWidth(i), db.fieldType(i), db.fieldPrecise(i), db.fieldScale(i), db.fieldNullable(i));
    			}
    		);
    		db.exec("DROP TABLE TEST.NAMEID");
    		db.close();
    		return 0;
    	} catch(e) {
    		console.log(e);
    		return -1;
    	}
    };
    
    testConn();
    testQurey();
    testServerMode();
    testAutoCommit();
    testCursorType();
    testEnvAttr();
    testConnAttr();
    testStmtAttr();
    testSimpleSQL();
    

Toolkit for i (included PTF)

  • /QOpenSys/QIBM/ProdData/Node/os400/xstoolkit/test/pgmme.js
    var xt = require('../lib/itoolkit');
    var conf = require('../../config.js');
    
    // var option = {
    	// host : conf.Host,
    	// port : conf.Port,
    	// path : conf.Path
    // };
    function cbJson(str) {console.log(JSON.stringify(xt.xmlToJson(str)));}
    function cb(str) {console.log(str);}
    
    var conn = new xt.iConn(conf.Database, conf.User, conf.Password);  // DB2 
    // var conn = new xt.iConn(conf.Database, conf.User, conf.Password, option);  // REST 
    
    
    function fixMe(origText,badText,goodText) {
      var strReplaceAll = origText;
      var intIndexOfMatch = strReplaceAll.indexOf( badText );
      while (intIndexOfMatch != -1){
        strReplaceAll = strReplaceAll.replace( badText, goodText );
        intIndexOfMatch = strReplaceAll.indexOf( badText );
      }
      return strReplaceAll;
    }
    
      // prepare iPgm Class
      var pgm = new xt.iPgm("PRODUCT"); 
        pgm.addParam(3,  "10i0");
        pgm.addParam(20, "10i0");
        pgm.addParam(0,  "10i0", {"enddo":"acount"});
        pgm.addParam([
          [0, "10i0"],
          [0, "10i0"],
          ["", "64a", {"varying":4}],
          ["", "64a", {"varying":4}],
          [0, "12p2"]
        ], {"dim":999,"dou":"acount"});
      var bugXml = pgm.toXML();
      // bugXml = fixMe(bugXml," dim='999'>", ">");
      // bugXml = fixMe(bugXml,"<data type='64a'>", "<data type='64a' varying='4'>");
      // bugXml = fixMe(bugXml," error='fast'>" , ">");
      console.log(bugXml);
      // run (call xmlservice)
      conn.add(xt.iCmd("CHGLIBL LIBL(HATS) CURLIB(HATS)"));
      conn.add(bugXml);
      conn.run(function (str) {
        var jobj = xt.xmlToJson(str);
        var list = [];
        var i = 0;
        var j = 0;
        var prod = "";
        var cat = "";
        var desc = "";
        var photo = "";
        var price = "";
        console.log("from xmlToJson jobj[1].data ... ");
        console.log(jobj[1].data);
        jobj[1].data.forEach(function(row){
          if (i > 2) {
            j++;
            switch(j) {
    	  case 1:
    	    prod = row.value;
    	    break;
    	  case 2:
    	    cat = row.value;
    	    break;
    	  case 3:
    	    desc = row.value;
    	    break;
    	  case 4:
    	    photo = row.value;
    	    break;
    	  case 5:
    	    price = row.value;
    	    j = 0;
    	    list.push([prod,cat,desc,photo,price]);
    	    break;
    	  default:
    	    j = 0;
    	    break;
    	}
          }
          i++;
        });
        console.log(list);
      });
    
    

Other Examples

  • sqljava.js
    // "create procedure sleeper(millis BIGINT) language java parameter style java external name 'java.lang.Thread.sleep'"
    //   D Main            PI 
    //   D  ms                           10i 0
    //    /free
    //      Exec Sql call hats/sleeper(:ms);
    //      return;
    //    /end-free
    var xt = require('../lib/itoolkit');
    var conf = require('../../config.js');
    
    function cb(str) {console.log(str);}
    
    // {'ctl' : "*java" } (php $conn->setOptions(array('customControl' => '*java')); )
    // *java mode, xmlservice does not own DB2 connection created by java opened PASE environment,
    // therefore xmlservice avoids executing 'change' operations on connection (attributes, free, etc.).
    // xmlservice toolkit default is system naming, wherein unqualified SQL operations use *libl,
    // *java mode default is sql naming, wherein *libl is ignored for unqualified SQL operations,
    // therefore users should expect *libl issues for some mixed DB2 script applications.
    var conn = new xt.iConn(conf.Database, conf.User, conf.Password, {'xslib':'XMLSERVICE', 'ctl' : '*java' });
    
    var JavaMe = function(callback) {
      // ** call CMD chglib
      conn.add(xt.iCmd("CHGLIBL LIBL("+conf.DemoLib+") CURLIB("+conf.DemoLib+")")); // add chglibl to call
      // ** create the test java elements
      var sql = new xt.iSql();
      sql.addQuery("set schema "+conf.DemoLib); // dunno why i need to do this (db2 bug i think)
      // ** java stored proc
      //sql.addQuery("create procedure gc() language java parameter style java external name 'java.lang.System.gc'");
      //sql.free();
      // ** java stored proc
      //sql.addQuery("create procedure sleeper(millis BIGINT) language java parameter style java external name 'java.lang.Thread.sleep'");
      //sql.free();
      // ** java UDF
      //sql.addQuery("create function getProperty(prop varchar(1024)) returns varchar(1024) language java parameter style java external name 'java.lang.System.getProperty'");
      //sql.free();
      //sql.addQuery("set current path "+conf.DemoLib);
      // ** call java stored proc
      sql.addQuery("call gc()");
      sql.free();
      // ** call java stored proc
      sql.addQuery("call sleeper(5000)");
      sql.free();
      // ** call java UDF
      sql.addQuery("select getProperty('java.home') from sysibm.sysdummy1");
      sql.fetch();
      sql.free();
      conn.add(sql);
      // ** RPG program that calls java stored proc
      var pgm = new xt.iPgm("ZZJAVA",{"lib" : "XMLSERVICE"}); // , "mode": "opm"
      pgm.addParam(5000,  "10i0"); // sleep for 5 seconds in java.lang.Thread.sleep
      conn.add(xt.iSh("hostname; date")); // start at ...
      conn.add(pgm); // add pgm to call
      conn.add(xt.iSh("hostname; date")); // ... run how long???
      // ** check the joblog
      // conn.add('<diag/>');
      // ** run xmlservice
      conn.run(callback); // call xmlservice and callcakc with answer
    }
    
    JavaMe(cb);