You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
This post is going to be able how to write APIs that return JSON in your RPG. There is three layers to this:
Web layer - this could be PHP, Node.js or Python for example
SQL connection - from the web layer to Db2 for i
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:
**FREEDcl-PiSUM;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:
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.
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*IN34DOUEQ*ONC*IN95OREQ*ONC READ CUSTREC 34C*IN34IFEQ*OFFC ADD 1 RRN
C* WRITEDETAIL CUSTOMERS(RRN) = CUSTREC; //Store current rowC 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;
The text was updated successfully, but these errors were encountered:
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.
This post is going to be able how to write APIs that return JSON in your RPG. There is three layers to this:
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:
And the matching SQL:
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:
The only important part is the only piece of embedded SQL being used:
Exec SQL Set Result Sets Array :resultSet For :rowCount Rows;
resultSet
data structure (which is an array))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.
There could, of course, be improvements to your API handler:
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.Then at the end, you could use the embedded SQL to return the resulting data.
The text was updated successfully, but these errors were encountered: