AS400 JDBC with Groovy!

Using Groovy to pull data out of the AS400 database could not be easier. In this example, we'll run a script on the PC to view a RPG source member. Other AS400 tables are just as easily accessible. Groovy maintains its own classpath so all that you will need to do is put a copy of the jt400.jar in the lib folder of your Groovy installation. Get Groovy at http://groovy.codehaus.org/Download if you don't already have it. Add the following few lines of code to a text file of your choice (here, I named the file Groove400.groovy) and with modifications to the driver connection string, this code connects to the ol' midrange and returns with your data! Note that this code demonstrates the use of the reserved variable "it" inside closures, first in the sql.eachRow loop and then in the rpgCode.each loop.

import groovy.sql.Sql

rpgCode = new ArrayList()

Sql sql = Sql.newInstance("jdbc:as400://theAS400", "UserId", "Password", 
                          "com.ibm.as400.access.AS400JDBCDriver") 
sql.eachRow("select * from mp1heltog.qrpglesrc") { 
   rpgCode.add(it.srcdta)
   println it.srcdta
}

rpgCode.each{ 
   println(it)
}

First, I'll echo the CLASSPATH environment value to show that Groovy has eliminated the need for us to include jt400.jar explicitly and then, I'll run the script and display the results.

C:\src\groovy>echo %CLASSPATH%
.;C:\Program Files\Java\jre1.6.0_03\lib\ext\QTJava.zip

C:\src\groovy>groovy Groove400.groovy
     h option(*srcstmt:*nodebugio)

      /free
             *inlr = *on;
      /end-free
     h option(*srcstmt:*nodebugio)

      /free
             *inlr = *on;
      /end-free

C:\src\groovy>