Automatic SQL Stored procedures as RESTfull services.
procREST is a simple way to expose Db2 stored procedures as RESTservice on the IBM i. In this examples everything is full open, however you might contain the services you expose either by access security or by user defined access rules added to this code - Whatever serves you best.
This application is using IceBreak - however you can easy follow the steps below and use the ILEastic and noxDB open source project. You will see in the code that it is actually noxDB is doing all the magic.
Db2 stored procedures on the IBM i can be used if they have:
- Only input parameters
- Return one dynamic result set: Look in the example belowe.
Installation on your IBM i of procREST
should be done with git
which are available via yum
- you can read more about yum here.
On your IBM i
First ssh
or call qp2term
into your IBM i, install git and clone this repo into the IFS:
yum install git
git -c http.sslVerify=false clone https://github.com/NielsLiisberg/procREST.git /www/procrest
This will create a directory /www/procrest
Now on a 5250 terminal:
GO ICEBREAK
ADDICESVR SVRID(PROCREST) TEXT('Stored Procedures as REST services') SVRPORT(7007)
STRICESVR SVRID(PROCREST)
Compile the stored procedure router:
CRTICEPGM STMF('/www/ProcRest/procRest.rpgle') SVRID(procrest)
Add the procREST in the routing section in you webconfig.xml file in your server root:
<routing strict="false">
<map pattern="procREST/" pgm="procrest" lib="*LIBL" />
</routing>
Procedures can be used if they have:
- Only inputparameters
- Return one dynamic result set:
Example:
Build a test stored procedure - paste this into ACS:
-- Procedure returns a resultset
--------------------------------
CREATE or REPLACE PROCEDURE procrest.custlist (
in custName varchar(20) default null
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
declare c1 cursor with return for
select *
from qiws.QCUSTCDT
where custName is null
or upper(lstnam) like '%' concat upper(custName) concat '%';
open c1;
END;
comment on procedure procrest.custlist is 'Customer list';
comment on parameter procrest.custlist (custname is 'Search customers by name');
-- Test the procedure works in ACS:
call procrest.custList (custName => 'j');
call procrest.custList ();
From your browser type the following, where myibmi
is the name or TCP/IP address of your system:
http://myibmi:7007/procREST
It will ask for a schema, so we suply out library procrest
where we put our procedure:
http://myibmi:7007/procREST/procrest
This will format the list of currently exposed stored procedures in the schema (library). For now this is simple HTML however a swagger/openAPI interface is on its way.
Here we have the custlist
service and it takes a paramter custname
- Nothing is btw case sensitive. So simply run it like this:
http://myibmi:7007/procRest/procrest/custList?custName=John