Using RPG to make your web APIs

[worksofliam]

This post is going to be able how to write APIs that return JSON in your RPG. There is three layers to this:

1. Web layer - this could be PHP, Node.js or Python for example
2. SQL connection - from the web layer to Db2 for i
3. RPG programs - this is the part that has the business logic

We are going to cover 2 and 3 first.

Calling programs from SQL

Stored procedures

Hopefully, we are all aware that we can call programs from SQL using stored procedures. Here's a simple RPG program that takes parameters in and may pass one out:

Here's the RPG code:

**FREE

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

result = numa + numb;

Return;

And the matching SQL:

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

Programs via SQL

Now, our SUM program is simple. All three parameters are integers. This means, using just SQL, we can call our program without needing to even create a stored procedure. The only reason we would use a stored procedure, in this case, is that it allows us to see the results from the program call (the output parameter we added).

RPG result sets

Now, this is where it gets really interesting. Any SQL/ILE program can actually return a data set form the program that we call via SQL. Let's take this simple RPG program for example:

**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 = '[email protected]';

resultSet(2).Name   = 'Beth';
resultSet(2).Money = 9876543.21;
resultSet(2).Email = '[email protected]';

resultSet(3).Name   = 'Steph';
resultSet(3).Money = 12345678.90;
resultSet(3).Email = '[email protected]';

rowCount = 3;

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

Return;

The only important part is the only piece of embedded SQL being used:

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

• This tells the parent SQL handler what the result array is and converts it into a relational table (our resultSet data structure (which is an array))
• We can also pass in the number of rows we want it to handle.

Again, no stored procedure needed.

Mind opening

Now, this really does open up a lot of doors. We can create this dynamic API in a language of your choice (I am using Node.js with express) to call programs using SQL and simply return the result set as JSON.

app.get('/:library/:program', async (req, res) => {
  const library = req.params.library, program = req.params.program;
  const resultSet = await db2.executeStatement(`CALL ${library}.${program}()`);
  res.json(resultSet);
});

There could, of course, be improvements to your API handler:

• Authentication to make sure they are allowed to call the program they specified (you may have an explicit list of programs)
• Handle parameters in your APIs to be passed into the programs

Subfiles

Imagine you have a program with a display file which contains a subfile. You might want to take the rows from the subfile and instead use the embedded SQL statement to return them as rows in result set. This means you could turn your subfile rows into an API easily!

This is a subroutine from an example I found online where the WRITE was replaced with code to store the resultSet in a DS. First, you could create a DS array that matches the record format, which is also the structure of the resulting relational data.

Dcl-Ds CUSTOMERS LikeRec(CUSTREC) Dim(1000);

     C           LOAD      BEGSR 
     C           *IN34     DOUEQ*ON 
     C           *IN95     OREQ *ON 
     C                     READ CUSTREC                  34 
     C           *IN34     IFEQ *OFF 
     C                    ADD  1         RRN 
     C*                    WRITEDETAIL 
        CUSTOMERS(RRN) = CUSTREC; //Store current row
     C                     ENDIF 
     C                     ENDDO 
     C                     ENDSR 

Then at the end, you could use the embedded SQL to return the resulting data.

Exec SQL Set Result Sets Array :CUSTOMERS For :RRN Rows;

[ryaneberly]

First time I've seen the feature of being able to call a program from SQL. I discovered it by accident about a year ago. Is it officially 'supported'

[priceaj]

It's definitely supported, it's in the SQL programming manual https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/sqlp/rbafyresultsets.htm

[ryaneberly]

Programs returning result sets is definitely supported. That page you linked doesn't seem to mention the wierd integration of allowing bare programs to be called without a stored procedure wrapper.