Saturday, July 11, 2009

Calling an RPG program from Groovy

I've been integrating Java applications with AS400/iSeries/Systemi RPG programs for over ten years. I've covered it in my book (Java Application Strategies for the iSeries) as well as in many of my articles. Calling RPG from Java can be complex. There are 4 or so options but I've been fairly emphatic about using JDBC callable statements as it is the simplest approach. To do that you need to create a stored produce "wrapper" for the RPG. But the Java code still can be quite verbose. Not so with Groovy.

The following shows how easy it is to call an RPG from Groovy. Note that this particular RPG returns information via a parameter; it does not return a result set.


boolean isDuplicatePO(String custNo, String poNo, String orderNo) {
def sql = new groovy.sql.Sql(sessionFactory.
getCurrentSession().connection())
boolean duplicate = false
sql.call ("call o99lib.o99epo (?,?,?,?)",
[Sql.in(Types.CHAR, custNo),
Sql.in(Types.CHAR, poNo),
Sql.out(Types.CHAR),
Sql.in(Types.CHAR, orderNo)
]) { dup -> duplicate = (dup == 'Y')}
return duplicate
}


The Groovy Sql object was built in the above code from a connection obtained from a Grails DataSource. But you could build your own with:


def sql = groovy.sql.Sql.newInstance(
"jdbc:as400://192.168.1.50/mylib",
"don", "secret",
"com.ibm.as400.access.AS400JDBCDriver")


To create the stored procedure wrapper, I used the following:


CREATE PROCEDURE O99LIB/O99EPO(
IN custNo CHAR(7),
IN poNo CHAR(25),
OUT dup CHAR(1),
IN ordNo CHAR(6))
LANGUAGE RPGLE
NOT DETERMINISTIC
NO SQL
EXTERNAL NAME O99LIB/O99EPO
PARAMETER STYLE GENERAL


Most RPG code does not make it obvious whether or not the parameters are input, output, or input/output. But it is worth the effort to figure out what is what and appropriately define the parameter usage in the create procedure statement.