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.

Thursday, June 11, 2009

Grails custom message.properties

This really is a simple thing.... Now that I know how it works. But I was attempting to override the constraint validation messages in messages in message.properties. In the past I've either simply modified the default message or created custom validators and completely new messages. But I just wanted to override the default messages. Several documents, posts, and blogs say to follow what the Grails docs say and use {className}.{attributeName}.{errorCode} and then provide a sample. But, the thing is, the {errorCode} is not obvious as it is not the same format as what's in the default message.

Here's the thing, my 2-second tip: the Grails documentation for the contraints (http://grails.org/doc/1.1.x/) show the error code to use. For example, for min:

Error Code: className.propertyName.min.notmet

I would have never guessed that in a million years (I did guess a few others though, which is probably why I spent a half-hour guessing at this one but. come on, "min.notmet?"

So, for my ShipTo class's shipToNo attribute I used:

shipTo.shipToNo.min.notmet=Ship-To number must be greater than or equal to {3}

Tuesday, June 9, 2009

A GSQL script for populating test tables

I always try to have a locally-based database for development. Typically I used MySQL although I may use a Windows or Unix-based desktop development version of Microsoft, IBM, or Oracle's databases. At any rate, I have not been able to find a simple utility to populate a local test database from the host. So I wrote the script that follows. This script specifically was for the iSeries (a.k.a. IBMi and AS400) database to MySQL but it is easily modifiable to other drivers. Note that I typically get my Grails domain classes working to the production system then I change the DataSource to generate the schema to my PC. And then I run my script. One issue with copying (via SQL) is that the column order may be different. So my script dynamically builds the insert statements from the table definitions in the host database.
Note that my select limits rows to 3500 so you may modify that to whatever strategy works for you. If your database is huge you may have to pick, for example, every seventh row, from a master table then predicate the selection of associated table rows on the key from that master database.

import groovy.sql.Sql

Sql iSeries = Sql.newInstance(
"jdbc:as400://192.168.1.50;naming=sql;errors=full;libraries=donfiles",
"denoncourt", "secret", "com.ibm.as400.access.AS400JDBCDriver")
Sql mysql = Sql.newInstance(
"jdbc:mysql://localhost/don", "", "", "com.mysql.jdbc.Driver")

def tables = ['custmast', 'itemmast', 'itemwhs']
tables.each {file ->
def rs = iSeries.getConnection().getMetaData().
getColumns(null, 'donfiles', file, null)
def cols = []
while (rs.next()) {
cols << rs.getString("COLUMN_NAME")
}
def insert = "insert into $file ("
cols.each {insert += "`$it`" + ','}
insert = insert.replaceAll(/,$/, '')
insert += ') value('
cols.each {insert += '?,'}
insert = insert.replaceAll(/,$/, '')
insert += ') '
println insert
mysql.execute("delete from $file".toString())
iSeries.eachRow (
"select * from $file fetch first 3500 rows only"
.toString())
{row ->
def data = []
cols.each { data << row[it] }
mysql.execute(insert.toString(), data)
}
}

Grails Validator for bank routing numbers

US Bank routing numbers have a checksum constraint built in. A Grails validator for that constraint is simple.


routingNum (blank:false, matches:/^\d{9}$/,
validator: {val, obj ->
if (val?.size() < 9) return false
def n = 0;
for (def i = 0; i < val.size(); i += 3) {
n += val[i].toInteger() * 3
n += val[i + 1].toInteger() * 7
n += val[i + 2].toInteger();
}
return (n != 0 && n % 10 == 0)
})

Wednesday, August 13, 2008

Grails Tests: Cannot send redirect - response is already committed

When testing controllers, if you make more than one call to a action/closure you may get the error: "Cannot send redirect - response is already committed"
But if you want to call one action to modify the database and then test the results in a subsequent call, if you put that call in a different test method, the prior test's transaction will probably be rolled back. So you want to make both action/closure calls in the same test method.

To get around this mess I simply MOPed it up (with help from Venkat Subramaniam's book "Programming Groovy"):


controller.metaClass.redirect = { Map args -> return args}


Note that the action of the redirect will not be invoked.
Also note that I am MOPing an instance, not the Class.

Grails Controller Tests: ModelAndView

I just joined the team at AlterThought.com and my first task was to revitalize the integration test for a bunch of controllers. But Grails no longer seems to have the modelAndView as a standard attribute of a controller. And we have assertions like:


assert controller.modelAndView.model.contractList.size() > 1


My solution was to override the controller's render method and stuff in a modelAndView to the controller myself:


import org.springframework.web.servlet.ModelAndView

class IntegrationTestUtil {
void overrideRender(controller) {
def dir = controller.class.name.replaceAll(/Controller/, '')
dir = dir[0].toLowerCase() + dir.substring(1)
def originalRender =
controller.metaClass.getMetaMethod("render", [Map] as Class[])
controller.metaClass.render = { Map args ->
if(args.view && args.model) {
delegate.metaClass.getModelAndView = {->
new ModelAndView("/${dir}/${args.view}", args.model)
}
} else {
originalRender.invoke(delegate, args)
}
}
}
}


My controllers then invoke the overrideRender method in setUp and the test run fine.

Note the override only changes render invocations that pass a map with model and view entries.

Tuesday, April 15, 2008

Grails UI for Integer Dates

Legacy tables often have dates kept in integer, zoned, or packed fields. This is unfortunate because the UI developer has to do all the validation and formatting of the numeric value. But there's a simple solution for Grails applications: create getter/setter methods to simulate a Date attribute.

Grails' generate-view will use the g:datePicker tag in the create and edit GSPs. Typically I replace g:datePicker with the RichUi tag.

In the following example, the domain has an integer sales date, in month/day/year format but getSaleDate() and setSaleDate() surfaces a Date attribute. The trick is to not present the integer date attribute for update (otherwise the value will overwrite the change made by setSaleDate()).


class Sale {
String last
int saleMDY
BigDecimal amount

Date getSaleDate() {
Calendar cal = Calendar.getInstance();
if (!saleMDY) {
return new Date()
}
int year = (saleMDY % 100)
int day = saleMDY / 100
int month = day / 100
day %= 100
cal.set((2000+year), (month-1), day)
return cal.time
}
void setSaleDate(Date date) {
Calendar cal = Calendar.getInstance();
cal.setTime(date)
int day = cal.get(Calendar.DAY_OF_MONTH)
int month = cal.get(Calendar.MONTH) + 1
int year = cal.get(Calendar.YEAR) % 100
saleMDY = (month * 10000) + (day * 100) + year
}
}