Fully integrating your RPG/COBOL with ODBC / You don't need a connector

[worksofliam]

I am known for telling people I do business with that they do not need to buy any type of product to integrate their open-source environment to their existing ILE RPG or COBOL stack. What you really need to do is invest in learning how to do achieve what those 'connectors' can do without spending any money, which is totally possible. The difference is how much money do you want to spend vs how much effort do you want to put in to learn.

I am going to focus on two primary pieces of technology on IBM i:

1. Node.js
2. ODBC (SQL driven) (github, driver)

I am primarily using this tech in my day-to-day and as a developer, you should understand how powerful this combination can be. Below, I will describe some of the most common things that people would use connectors for, but can do at no cost using only ODBC.

Running SQL statements

Of course, a very basic one, but ODBC can run SQL statements.

async function queryExample() {
    const pool = await odbc.pool(`${process.env.CONNECTION_STRING}`);
    const result = await pool.query('SELECT * FROM MY_TABLE');
    console.log(result);
}

Ideally, you should always be using pooling too. Don't forget, you can run any type of SQL statement. For example:

• A normal CRUD statement,
• CALL to a stored procedure which has an output result set,
• SELECT * FROM TABLE(x) x to call a UDTF which returns a result set.

Calling a stored procedure

While you can call a stored procedure using a normal query, ODBC also provides methods to call stored procedures with IN, OUT, and INOUT parameters. (docs here)

async function callProcedureExample() {
    const connection = await odbc.connect(`${process.env.CONNECTION_STRING}`);
    const result = await connection.callProcedure(null, null, 'MY_PROC', [undefined]);
    // result contains an array of results, and has a `parameters` property to access parameters returned by the procedure.
    console.log(result);
}

Now you have two ways of calling procedures! Calling procedures doesn't only involve SQL in 2021 - a procedure can also be an RPG or COBOL program. This is where things get cool.

Calling a program with output parameters

A while back, I wrote an entry about calling programs with input and output parameters - it is dead simple.

Here's an example program:

**FREE

Dcl-Pi SUM;
  numa int(10);
  numb int(10);
  result int(10);
End-Pi;

result = numa + numb;

Return;

And here is the stored procedure for that program

create or replace procedure barry.sumpgm (IN numa INT, IN numb INT, OUT result INT) 
LANGUAGE RPGLE  
EXTERNAL NAME BARRY.SUM GENERAL;

And because you now know how to call stored procedures from SQL & ODBC, you can pretty much call that program from anywhere!

Programs with result sets

Now, this is a nifty trick I love to use. People love this too because it makes consuming data from RPG just that little bit easier.

SQL ILE programs, whether it is COBOL or RPG, can return structs as the result set from the program using a very simple Embedded SQL statement:

**FREE

Dcl-Pi RESULTTEST;
End-Pi;

Dcl-S rowCount Int(10);
Dcl-Ds resultSet Dim(5) Qualified;
  Name  varchar(20);
  Money packed(11:2); //SQL decimal
  Email varchar(32);
End-Ds;

resultSet(1).Name   = 'Liam';
resultSet(1).Money = 20.00;
resultSet(1).Email = 'liam@me.com';

resultSet(2).Name   = 'Beth';
resultSet(2).Money = 9876543.21;
resultSet(2).Email = 'beth@me.com';

resultSet(3).Name   = 'Steph';
resultSet(3).Money = 12345678.90;
resultSet(3).Email = 'steph@me.com';

rowCount = 3;

Exec SQL Set Result Sets Array :resultSet For :rowCount Rows;

Return;

Calling programs with complex data structures

I wrote an open-source tool called Logicgoose for Node.js, which simply creates buffers to match how ILE programs would interpret them.

For example, let's say we have this PI:

Dcl-Ds name Qualified Template;
  first Char(20);
  last Char(20);
End-Ds;

Dcl-Ds base Qualified Template;
  username Char(10);
  name LikeDS(name);
  email Char(50);
End-Ds;

Dcl-Pi PROGRAM;
  inputBase LikeDS(base) Qualified;
End-Pi;

Using Logicgoose, it would be possible to recreate this structure using its APIs, which would then allow us to create a matching buffer to pass directly into the program.

//https://github.com/worksofbarry/logicgoose
const rpgleDS = require('rpgleds');

const name = new rpgleDS(
    [
        { "name": "first", "length": 20 },
        { "name": "last", "length": 20 }
    ]
);

const base = new rpgleDS(
    [
        { "name": "username", "length": 10 },
        { "name": "name", "type": name },
        { "name": "email", "length": 50 }
    ]
);

Pretty simple! Next, we just need the stored procedure over our program - but make a note: the size of the parameters have to match the size of the structures in your program, otherwise data will go missing!

create or replace procedure schema.name (IN base CHAR(100)) 
LANGUAGE RPGLE
EXTERNAL NAME LIB.PROGRAM GENERAL;

Now, to actually call the program you can use:

1. A method from Logicgoose to create the buffer.
2. ODBC to call the procedure with the buffer!

//Function to call our procedure
async callProgram(inJSON) {
  //Convert a JSON object to the buffer for the DS
  const inputBuffer = base.toBuffer(inJSON);

  const results = await db2.callProcedure(SCHEMA, 'PROGRAM', [inputBuffer]);

  //Maybe if you eventually add a result struct, then convert to JSON
  //const result = base.fromBuffer(results.paramaters[1]);
  return result;
}

//Call it with our object
const result = await callProgram({
    username: "yaboy",
    name: {first: "liam", last: "barry"},
    email: "barry@me.com"
})

Using Logicgoose, it is also possible to parse a result structure if you were returning one on an OUT parameter. Also, you don't have to write all this code yourself - I made a useful tool to generate all of this for you.

• RPGLE + Node.js generator
• Logicgoose repo which has examples and source code.

End

I would love to hear your thoughts on this topic.

Also, if you know something a connector can do that you can't really do over SQL/ODBC I would love to hear it.

[tullyelly]

Well written and thought provoking. I'm going to point the developers I work with here for some inspiration.

[JuanmaAlcudia]

I think the best way to go is to pass the RPG parameters as JSON. Keep it simple :)

[Villafuertito95]

Hello Liam, I'm trying to use ODBC to call a stored procedure that has an input and an output of type CLOB, but I can't call the SP because it's giving me a data type error.

[brandonp42]

Hi @Villafuertito95, are you using NodeJS? If so please see IBM/node-odbc#355 for a recent discussion about using CLOB parameters with stored procedures. There seems to be an issue with the node-odbc driver but there is a workaround if you can make the alternative work for you.

[Villafuertito95]

Thanks @brandonp42