Dealing with Dates: Sorting Legacy Month/Day/Year Dates

Half of my Grails applications use legacy AS400 databases whose tables frequently have dates stored in 6-bytes in MMDDYY format. I map those columns to Date attributes in my domain classes using a custom Hibernate type class (written in Groovy) and everything is hunky-dory -- until I try to sort or filter values on those dates.
The solution is actually quite simple: use HSQL with an algorithm to convert the dates to YYMMDD.

The following looks for contract prices where August 14, 2009 is between the MMDDYY begin and expire dates:

def query = 
ContractPrice.executeQuery(
  """FROM ContractPrice cp WHERE 
     ? BETWEEN 
       MOD((cp.beginDate * 10000.01), 1000000) 
       AND 
       MOD((cp.expireDate * 10000.01), 1000000) 
  """, 
  [90814], 
  [max:params.max.toInteger(), 
   offset:params.offset.toInteger(), 
   sort:params.sort])

The trick is an old RPG programmers trick of multiplying the MDY date by 10000.01.
Note that the query will be fairly slow as it will have to do a table scan. But what my applications normally do is use precedes the date math with a predicate on a column that performs well with the query optimizer.

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.

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}

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)
})