Calling ILE programs from SQL (Node.js, PHP)

[worksofliam]

In today's blog, we're going to talk about stored procedures to call ILE programs from SQL. I've been inspired to write this blog because of an article: Recycling RPG as Stored Procedures, which was written by Susan Gantner and Jon Paris. Although fairly old, it's still a great article.

This article is based on IBM i 7.2. The plan is to go over creating the program, creating the SQL procedure and then also creating a Node.js script to call that procedure.

Creating the RPG Program

I use RPG by pure example. You could use any ILE language, even Java. Also, pCusAge could be Int(3) instead.

**FREE

Ctl-Opt DftActGrp(*No) ActGrp(*New);

Dcl-Pi Program;
  pCusID    Int(10);
  pCusName  Char(20);
  pCusEmail Char(25);
  pCusAge   Int(10);
END-PI;

Select;
  When (pCusID = 1);
    pCusName  = 'Barry';
    pCusEmail = '[email protected]';
    pCusAge   = 19;

  When (pCusID = 2);
    pcusName  = 'MillyMai';
    pCusEmail = '[email protected]';
    pCusAge   = 19;

  When (pCusID = 3);
    pcusName  = 'DarthJim';
    pCusEmail = '[email protected]';
    pCusAge   = 41;

  When (pCusID = 4);
    pcusName  = 'Lizzo';
    pCusEmail = '[email protected]';
    pCusAge   = 48;
ENDSL;

*InLR = *On;
Return;

Quite simply, you pass in data though the first parameter and the program will pass data out through the other paramaters (by reference). I call this program Program and placed it in my current library, which is #LALLAN. (#LALLAN.PROGRAM)

Creating the stored procedure

I use ACS->Run SQL Scripts to run this SQL statement, but you can probably use STRSQL if you wanted to. The following statement creates a stored procedure named getCusInfo in my #LALLAN library.

CREATE OR REPLACE PROCEDURE #LALLAN.getCusInfo (
  IN CusID INTEGER, 
  OUT CusName CHAR (20), 
  OUT CusEmail CHAR(25), 
  OUT pCusAge INTEGER
)
EXTERNAL NAME #LALLAN.PROGRAM
LANGUAGE RPGLE	
PARAMETER STYLE GENERAL

Using Node.js to call our SQL procedure

In this Node.js script, we use prepared statements to pass in data. The data comes out of the executeSync callback function (the first parameter). The script works in this order:

1. Define db which points to the DB2 API for Node.js
2. Create the SQL statement (sql)
3. Create a new DB2 connection instance (dbconn) and then connect to the *LOCAL database
4. Define a prepared statement (stmt)
5. Prepare our SQL statement (sql variable) to our prepare statement instance (stmt) using stmt.prepareSync
6. Bind the parameters with stmt.bindParamSync
7. Execute the prepared statement with stmt.executeSync

I put the following code into nodeDb2.js in my current directory, but you can call it whatever you like. (2019 update: this Node.js code is outdated.)

var db = require('/QOpenSys/QIBM/ProdData/OPS/Node4/os400/db2i/lib/db2a');

//Create out statement
var sql = "call #LALLAN.getCusInfo(?, ?, ?, ?)";

//Prepare our connection
var dbconn = new db.dbconn();
dbconn.conn("*LOCAL");

//Create out prepared statement
var stmt = new db.dbstmt(dbconn);

//Our customer object/array
var Customer  = [];
Customer.ID   = 1;
Customer.Data = [];

stmt.prepareSync(sql);
stmt.bindParamSync([
      [Customer.ID, db.SQL_PARAM_INPUT, 2],
      ['', db.SQL_PARAM_OUTPUT, 1],
      ['', db.SQL_PARAM_OUTPUT, 1],
      [0, db.SQL_PARAM_OUTPUT, 0]
]);
stmt.executeSync(function callback(out) { 
	Customer.Data = out;
});

console.log(Customer.Data);

//
delete stmt;
dbconn.disconn();
delete dbconn;

Testing the process

I've stopped using QSH and have started using SSH to log into my system when using Node.js. I call the script with node nodeDb2.js and my output is a little something like this:

C:\Users\Barry\Documents\GitHub> ssh barry@swagibmi
-bash-4.2$ node nodeDb2.js
[ 'Barry               ', '[email protected]      ', '19  ' ]

If I was to change my Customer.ID in my Node.js script to 2, 3 or 4, the output would have been something else.

Extra: Calling the SQL procedure from PHP!

Thanks to @phpdave (godzillai5.wordpress.com) for writing this script. This script will function the same as the Node.js one would - processes the statement, but instead puts the data back into the $Customer class.

<?php
//Create db connection
$hostname = "MYIBMI";
$user = "MYPROFILE";
$password = "";
$dbconn = new PDO("odbc:" . $hostname, $user, $password);

//SQL to run stored proc
$sql = "call #LALLAN.getCusInfo(?, ?, ?, ?)";

//Prepare SQL for execution
$stmt = $dbconn->prepare($sql);

//Create a test object for demo purposes
$Customer  = new stdClass();
$Customer->ID   = 1;
$Customer->Data1   = "";
$Customer->Data2   = "";
$Customer->Data3   = 1;

//Bind object properties to our object
$stmt->bindParam(1, $Customer->ID, PDO::PARAM_INT);
$stmt->bindParam(2, $Customer->Data1, PDO::PARAM_INPUT_OUTPUT);
$stmt->bindParam(3, $Customer->Data2, PDO::PARAM_INPUT_OUTPUT);
$stmt->bindParam(4, $Customer->Data3, PDO::PARAM_INPUT_OUTPUT);

//Run it
$stmt->execute();

//Dump our object to the screen to see what everything got set to.
//Or alternatively run xdebug or another debugger to view the
//$Customer object after execution
var_dump($Customer);
?>