Don Denoncourt

Grails AS400 RPG Connection Pool

2010-12-13T07:26:00.000-08:00

In my last post I said: "For the most part, I did not have to do anything overly special to work with the AS400" in the development of the AS400-based Kettler USA retail site. The one area that took a bit of Grails-magic was some direct calls to RPG and some indirect calls to RPG via messages. The direct calls were done via JDBC stored procedures, so the standard JDBC connection pool set up by the Grails DataSource.groovy configuration worked fine. But for the message-base communication, I used AS400 data queues -- which are not supported by JDBC. So I needed to create an AS400 connection object. You really don't want to create an AS400 connection object for each request. Instead you want to use IBM's AS400 Connection Pool.

Once the AS400 Connection Pool is available, I'm able to use it in my service class like so:


class PreAuthorizationService implements Serializable {
  def as400ConnPool
  String preAuth(Cart cart){
    AS400 conn = getConnection()
    ...

But where do you create the AS400 Connection Pool and when? Grails makes that easy.

In grails-app/conf/spring/resources.groovy place the following code:


import org.codehaus.groovy.grails.commons.ConfigurationHolder as CH
beans = {
  if (CH.config.dataSource.driverClassName == 
      'com.ibm.as400.access.AS400JDBCDriver') {
      as400ConnPool(AS400ConnPoolFactory) {bean ->
          ip = CH.config.iseriesIPAddress
          userId = CH.config.iseriesUserId
          password = CH.config.iseriesPwd
      }
  }
}

I put the IP, username, and password in Config.groovy (hence the use of ConfigurationHolder. Also note that, because I often use a local MySQL-based AS400-connection-less test environment, I predicate the creation of the AS400 connection so it is only created if the IBM driver was active. The IBM AS400 Connection Pool needs a bit of Spring infrastructure, which I coded in my AS400ConnPoolFactory wrapper class:


import org.springframework.beans.factory.FactoryBean
import org.springframework.beans.factory.InitializingBean

import com.ibm.as400.access.AS400
import com.ibm.as400.access.AS400ConnectionPool

class AS400ConnPoolFactory implements FactoryBean {
  String ip
  String userId
  String password

  public Object getObject() throws Exception {
    // note: try/catch removed for brevity
    AS400ConnectionPool as400ConnPool = new AS400ConnectionPool()
    as400ConnPool.setMaxConnections(128)
    as400ConnPool.fill(ip, userId, password, AS400.COMMAND, 5)
    return as400ConnPool
  }
  public Class getObjectType() {AS400ConnectionPool.class}
  public boolean isSingleton() {true}
}

It's all pretty simple. AS400 connection pools made easy by Grails!

Grails 400 Experiences

2010-12-09T06:11:00.000-08:00

I received a number of responses to my post about the launch of the retail site www.kettlerusa.com. I was happy to hear that there are folks out there that are either considering or using Grails on the AS400 (a.k.a. iSeries, System-i.) I will be doing a series of posts on my experiences Grails and the AS400.

For the most part, I did not have to do anything overly special to work with the AS400. DB2/400 supports all the features that Grails (via Hibernate) uses. The biggest issue was working with tables that have composite keys. Note that, although Grails has facilities for working with composite keys, rather than keying the required GORM code, I used the Systemi Grails Domain Plugin. This plugin has a script that takes an AS400 table and generates the Grails domain class. I have used that plugin (which I wrote with help from Mike Brown) to generate hundreds of Grails domain classes for tables on about a dozen different AS400s (all at different companies.) You can read more about the tool in my article Rapid IBM i Web Dev with Open Source Tools (note that article will require a web subscription to www.systeminetwork.)

KETTLER USA was deployed to a 64-bit Windows server that is running on Tomcat. The Tomcat server is behind a firewall with an Apache server running outside the firewall as a proxy server. The Tomcat server is able to connect to the AS400 via JDBC. The Tomcat server can only be accessed from the Apache server so it is a secure AS400 connection.

Most of the application is written using Grails domain classes to access DB2/400, but there are some points where the application does direct calls to RPG via JDBC stored procedures or indirect RPG calls via AS400 data queues. I will be elaborating on this in a future post.

I've been coding 12x7 for so many months getting the Kettler retail site up that I've forgotten some of the slick things that I was able to do with Grails (where coding the solution in Java would have been far more difficult.) But one thing, off the top of my head, that I loved being able to do was to put the Grails domain classes in their own Grails project and pull that project into the UI project as a Grails plugin. The reason why I needed to do this was that I had written a Business-to-business Grails application for Kettler last year. And the retail application used many of the same Grails domain classes. As they say "there shall be no duplicate code." And I certainly didn't want to maintain the 85 domain classes required for Kettler in two places. With one of the more recent versions of Grails (we are on 1.3.5 now) Grails made using your own project Grails plugins very easy. Instead of having to generate a Grails zip archive and import it to the project that requires that plugin -- everytime that supporting project is changed -- you simply put a reference to that project in your grails-app/conf/BuildConfig.groovy:


grails.plugin.location.'shared-domain-plugin' = "../SharedDomainPlugin"

Again, I will be added more Grails400 posts in the future.

Grails 400 Retail Site Now Online

2010-12-02T18:50:00.000-08:00

I haven't posted in months because I was developing a Grails-based retail web site www.kettlerusa.com. I've been primarily developing Grails applications for the past 4 years now and I'm still impressed with Grails. This is the first retail site that I've developed -- all my prior sites have been internal, or business-to-business password based applications that I couldn't easily share. The back-end database is DB2/400 running on an iSeries/AS400 box. The application has some direct integration with RPG via stored procedures and data queues but otherwise it uses the Grails domain classes and GORM to work with the DB2/400 database.

If you are interested in Fitness, Bikes, Toys, Patio, or Table Tennis, check out www.kettlerusa.com.

In future posts I plan to blog about issues I had in the development of this application that were elegantly solved with Groovy and/or Grails.

Building a Browser for Amazon S3 with Grails

2010-07-28T06:11:00.000-07:00

Are you using Amazon S3 and considering learning Grails? Or are you a Grails developer and beginning to look at Amazon S3?
Check out my Amazon article:
Building a Browser for Amazon S3 with Grails

DRY with Grails namedQueries and a Super-Dynamic Search Strategy

2010-06-24T05:09:00.000-07:00

I just finished developing a simple query engine with Grails 1.2.1 for one of my clients. As usually, Grails did most of the hard work. The requirement was to provide a query engine for customer-specific flat files that contained all information about transactions that occurred in the last year. Previously the flat files were delivered to the clients as Microsoft Access binary files. The problem was that the clients never took the initiative to write apps to search and aggregate the information. So I was brought in to use Grails to rapidly develop a web application that provided search and aggregation of the client-specific web flat files.

The resulting Grails application is hosted on Amazon EC2 with MySQL. Anyway, I wanted to share with you my use of Grails 1.2's namedQueries and my simple strategy to support a dynamic search in the standard list.gsp.

The application's list page shows all the columns of the table. To do that it needs to know the properties of the domain. Each client has a custom domain class specific to their flat file (which, for some reason, have different column names.) The domain classes have a getColumnProperties():

static List getColumnProperties() {
def columnProperties = []
CustDomainName.properties.declaredFields.each {prop ->
if (prop.modifiers == 2            &&
!NO_SHOW_COLS.find {it == prop.name} ) {
columnProperties << prop
}
return columnProperties
}

The column properties are passed to list.gsp as [properties:domain.columnProperties] so the page can list all columns:


<tbody>
<g:each in="${domainInstances}" status="i" var="domainObj">
<tr class="${(i % 2) == 0 ? 'odd' : 'even'}">
<g:each in="${properties}" var="property">
 <td>${domainObj[property.name]}</td>
</g:each>
</tr>
</g:each>
</tbody>

Each column has a search added to the the top of the table:


<tr>
<g:each in="${properties}" var="property">
<td>
<% if (property.type.name != 'java.util.Date') { %>
<input type="text" name="${property.name}" value="${(params[property.name])}"
<% if (property.type.name == 'java.lang.String') {%>
    title="Enter a character prefix to filter ${property.name}"
<% } else { /* assume numeric */ %>
   title="Enter a numeric value to filter ${property.name}"
<% } %>
/>
<% } else { %>
<richui:dateChooser format="MM/dd/yyyy" name="${property.name}" value="${(params[property.name])}" />
<% } %>
</td>
</g:each>
</tr>
<tr>
<g:each in="${properties}" var="property">
<td>
<% if (property.type.name == 'java.util.Date') { %>
 <richui:dateChooser format="MM/dd/yyyy" name="${property.name}To" value="${(params[property.name+'To'])}"  />
<% } else if (property.type.name != 'java.lang.String') {
 def propOp = property.name+'_Op'
%>
<g:select name="${propOp}" value="${params[propOp]}"
   from="${['eq', 'gt', 'ge', 'lt', 'le', 'ne']}"
   valueMessagePrefix="critera.operator"  />
<% } %>
</td>
</g:each>
</tr>

Note how dates have a from and to with the RichUI dateChooser and numerics have operators. Strings work as prefixes to the SQL like clause. The list action is then implemented as follows:


def list = {
params.max = Math.min(params.max ? params.int('max') : 10, 100)
params.offset = params.offset ? params.int('offset') : 0
params.sort = params.sort?:'id'
params.order = params.order?:'desc'

List domainInstances = domain.dynaCrit(params) // .list(params) ignors sort
    .findAllByIdGreaterThan(0,
         [max: params.max, offset:params.offset,
          sort:params.sort, order:params.order])

int count = domain.dynaCrit(params).count()

[domainInstances: domainInstances, count:count,
properties:domain.columnProperties, params:params]
}

Prior to namedQueries (with Grails 1.2.) I've always had to copy-and-paste criterion logic from the search to the count query. This violated the Don't Repeat Yourself (DRY) principle. Now I use a named query. Note that I did have an issue with the sort facilities in the list method so I did a hack with the findAllByIdGreaterThan (perhaps this is fixed with Grails 1.3.) Finally, here's the namedQuery in the domain class:


static namedQueries = {
dynaCrit {params ->
params.each {filterProp ->
  if (filterProp.value) {
    def property = CustDomainName.columnProperties.find {it.name == filterProp.key}
    if (property) {
      if (property.type == String) {
        ilike ("$property.name",  filterProp.value+'%')
      } else if (property.type == Date) {
        between ("$property.name",  filterProp.value, params[filterProp.key+'To'])
      } else {
        "${(params[filterProp.key+'_Op'])}" ("$property.name",  property.type.newInstance(filterProp.value.trim()))
      }
    }
  }
}
}
}

The application does provide a number of other handy features:

The ability on the list page to remove (and re-add) columns
Dynamic summary reports with prompt pages prompting for groupBy and sum columns along with filter criterion.
Excel download option of the user-defined report
The ability to save user-defined queries.

What I found amazing during the development of this application was how Grails made it ridiculously easy to develop dynamic reporting.

Handling Select-Multiple with Grails

2010-06-10T06:41:00.000-07:00

When a page sends multiple values with the same HTTP parameter name, Grails stuffs them into an ArrayList. That is very handy but there's an issue when the user only selects one value. When only one value is passed Grails does not put it into an array. And your code has to detect whether or not the parameter is an array.

Perhaps the best solution is to use a Command object. The following, for example, handles the user selection of one or multiple items (orders, qtys, etc.):


class BuildReturnItemsCommand {
   String[] itemNo
   int[] orderNo
   int[] qty
   BigDecimal[] unitPrice
   String[] desc
}

[recent add: check out http://www.intelligrape.com/blog/2010/06/14/getting-params-attribute-as-list/ for another solution]

But, I'm lazy. If I have simple input where no validation is required, I don't want to take the time to create a command object. The following shows the preamble of a Controller closure that handles a single selection of a multiple-select list of regions:


def salesReport = {
  params.regions = (params.regions)?[params.regions].flatten():null

Groovy to the rescue!
The ternary, if there is a regions parameter, stuffs the regions into a List. But, because the parameter might already be a list, it is flattened.

The following Groovy snippet tests this code:


def params = [:]
params.regions = 'West'
params.regions = (params.regions)?[params.regions].flatten():null

assert params.regions == ['West']

params.regions = ['East', 'North', 'South', 'West']
params.regions = (params.regions)?[params.regions].flatten():null

assert params.regions == ['East', 'North', 'South', 'West']

Groovy wrapping of long table td text with no blanks

2010-05-06T07:47:00.000-07:00

Subtitle: Groovy's a Wrap

Just today I had a client -- who had just tested a fairly complex GSP -- ask to have text in a table data element wrap. He had tested it with a long string of characters with no spaces. He wanted:

asldfjas;fjasodfsaodjfsaodfdsadfa;ljlj

To show as:

asldfjas;fj
asodfsaod
jfsaodfdsa
dfa;ljlj

And not just the first 12 characters that fit in the td.

Quick solution: Hey buddy, use spaces and the browser will wrap for you.
Groovy solution: Insert thespaces for them.

I took the Groovy option. What I did was extend the String class in BootStrap to have a wrapAt method:


class BootStrap {

def init = { servletContext ->
String.metaClass.wrapAt = {width ->
matcher = (delegate =~ /\w{1,$width}/);
def that = ''
matcher.each {
 that += "${it.trim()} "
}
return that
}
}
def destroy = {
}
}

Then, in my GSP (where the em width was 12) I added:

<td>${option.value.wrapAt(12)}</td>

I tested the code with the following:


String.metaClass.wrapAt = {width ->
println delegate
matcher = (delegate =~ /\w{1,$width}/);
def that = ''
matcher.each {
that += "${it.trim()} "
}
return that
}

def str = 'one two thetimehascomeforalgoodmentocometotheaidoftheircountry'
assert "one two thetimehas comeforalg oodmentoco metotheaid oftheircou ntry " == str.wrapAt(10)

Sure, not a perfect solution, but all the text the user keyed is viewable on the page (and the optional PDF that is generated from that page.) It looks like this:

one two
thetimehasco
meforalgoodm
entocometoth
eaidoftheirc
ountry

Also, it gave me a use for Groovy meta programming

Kindle for Technical Content

2010-04-16T08:10:00.000-07:00

I'm a reader. I started reading novels at a very young age. As a developer and reader, I'm addicted to reading technical books. I am also a technical writer so I'm constantly buying and reading books. And I'm not a fan of reading on-line. When I sit in front of my PC, I expect the instant gratification of coding or writing. I find it difficult to read a PDF book cover-to-cover (or should that be byte-to-byte?) Case in point: To save money, I opted for the PDF version of Venkat Subramaniam's "Programming Groovy: Dynamic Productivity for the Java Developer." Great book, by the way. But I ended up getting frustrated and printed the book -- which ended up costing me more in paper and ink than that print version would have.

A month ago I was at Barnes and Nobles and I became intrigued by the Nook. The Nook is B&N's book reader. I almost bought it. Luckily I backed out of the store and began researching the Nook and compared it with Amazon's Kindle.

The Nook is pretty neat but, unlike the Kindle, it does not have a keyboard. With the Nook, you can bookmark your documents -- but you can't highlight text or add notes. The Kindle has an integrated keyboard and you can highlight and add notes.

I got my Kindle yesterday. And, so far, I love it.

Book Availability:

I am currently researching Cloud Computing for use in Grails applications using both Amazon Web Services and Google App Engine. To that end I've been wanting to buy two books: "Programming Amazon Web Services" and "Programming Google App Engine" (both from O'Reilly.) Curiously, the Amazon book was not available for Kindle at Amazon's site but the Google book was. So I bought the GAE book from Amazon and went to the O'Reilly site for the AWS book. At O'Reilly I found that they do make an eBook download available that is Kindle compatible (.mobi.) O'Reilly gives you both the Kindle and PDF versions of the book. To get the AWS book's mobi file on my Kindle I simply plugged in the Kindle's USB and copied the mobi file from my PC to the Kindle's documents directory.

My next issue was that I wanted to get some of my PDF books on the Kindle. All I had to do was plug in the USB and copy the PDFs from the PC to my Kindle. There are two issues with PDF on the Kindle: 1) It shows the entire page on the screen, which requires a very small font (but it is readable). 2) While you can add bookmarks to a PDF, you can't add notes or highlight text. Note that I believe there are PDF to mobi converters available. But, for right now, I'm happy with Kindle's PDF reader.

Reading Experience:

I was reticent to get an electronic reading device because I thought I needed the spacial context of a printed book. When I first get a printed copy of technical book, I put a half-dozen sticky notes inside the front cover. Then I hook a highlighter and a mechanical pencil on the back cover. I use the sticky notes to bookmark important sections and my current page. Those sticky notes stay in the book forever. When I go to my book shelve, I usually find what I'm looking for by going to the various bookmarked pages.

As I read my print books, I use the highlighter on important sections and I use the pencil to make notes. If I'm doing research for an article, I also write notes on the front cover with page references.

The Kindle does all that for me only in a cleaner, greener fashion. First off, I love that when I put the Kindle down (which has been difficult) and power it off and then later return to the Kindle and power it back on, it goes right to the page of the document that I was last reading. I also love that I can make bookmarks with notes and then Kindle shows me my bookmarks, notes, and highlights in an easy to read index. For article research, the notes are invaluable. For programming, the bookmarks and highlights are extremely useful.

I was also concerned about the display of tables and code on the Kindle. But I discovered that the Kindle allows you to change the view from portrait to landscape (a feature the Nook does not provide.) The Kindle also allows you to change your font size (which the Nook allows as well.)

At any rate... I'm loving my Kindle

Free Book on Grails

2010-02-08T07:04:00.000-08:00

Three years ago, when I first began developing with Grails, I benefited from a book by Jason Rudolph called "Getting Started with Grails" The book was well written, short, and available as a free PDF. I recommended it to everyone interested in Grails. But, as Grails rapidly improved, the book began to become outdated.

No longer.

Jason Rudolph teamed up with Scott Davis (author of "Groovy Recipes") to update the book. It now covers Grails 1.2 and, once again, I highly recommend it.
You can download the book from:
http://www.infoq.com/minibooks/grails-getting-started
But note that InfoQ will ask you to register with their site before you can download the PDF. But it is well worth it as InfoQ is a great site with lots of technical information, in form of articles and videos, on application development.

Comment on IE

2010-02-05T13:53:00.000-08:00

Half my work is creating Web solutions for my clients.
The other half of my work is getting it to work on Internet Explorer....

Real World Observation about Grails

2010-02-05T13:31:00.000-08:00

In the past 3 years I've developed 7 Grails applications for a variety of customers. And I have to make an observation: Maintaining Grails applications is a breeze. It is far more easy than with any other development platform I've used. I've had to revisit Java applications that I had written. But it always takes me a while to figure out the design strategies. For instance: what MVC pattern did I use?

With Grails applications -- whether written by myself or not -- the MVC is always the same. The directory structure is always the same. It is fabulous being able, in Eclipse, to switch workspaces from one Grails project to another and have the structure look identical. They look so similar sometimes, when I come back from lunch or something, I have think "OK, what project is this."

This is an observation of the benefits of the Grails philosophy of "Convention over Configuration." This Grails feature provides a huge ROI when someone else can take over a Grails project and know where everything is. In fact, I benefited from this myself two years ago when I joined an existing Grails project team. The project was Circuit City's return system (I have some gift cards if anyone would like to buy them). The return system had been in production for a year and it was very complex (for example it had DB connections to Informix, Oracle, and DB2i.) Yet I was able to be productive in a very short time after joining the project. By the way, that Grails app is the only Grails app that I worked on that is no longer in use -- for obvious reasons....

Grails makes life easier.

Book Review: Grails: A Quick-Start Guide

2010-02-05T13:16:00.000-08:00

It's been 5 months since my last post. I have dozens of times thought "I need to post this." But -- as I was working 12X7 on a major Grails-based order entry system and two other smaller Grails projects and authored 3 articles on Grails and delivered a multi-city seminar tour on Grails -- I simply didn't have the time. But things are slowing down now.

I recently burned through The Pragmatic Programmer's new book: "Grails: A Quick-Start Guide" by Dave Klein. I was impressed. Attendees to my seminars and Grails mentees usually ask "What's a good book to buy on Grails?" Well, "The Definitive Guide to Grails" authored by the creator of Grails, Graeme Rocher, is a bit too long for beginners. I think of it as the Grails bible. "Grails in Action," by Glen Smith and Peter Ledbrook, is fabulous as it is very written. But its 487 pages can be a bit daunting. "Grails: A Quick-Start Guide" is very approachable. It is just barely over 200 pages and it very succinctly written. It is now my recommended book for Grails beginners.

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

2009-08-04T05:46:00.000-07:00

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

2009-07-11T11:48:00.000-07:00

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

2009-06-11T19:22:00.001-07:00

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

2009-06-09T05:31:00.000-07:00

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

2009-06-09T05:21:00.000-07:00

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

Grails Tests: Cannot send redirect - response is already committed

2008-08-13T10:09:00.001-07:00

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

2008-08-13T09:50:00.000-07:00

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.

Grails UI for Integer Dates

2008-04-15T05:46:00.000-07:00

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

Integration testing for Grails controllers

2008-02-22T14:47:00.000-08:00

I've been negligent in writing integration tests for my controllers, mostly because I ran into some issues and put off figuring it out. So while on a train I wrote a simple 24-line controller with action-closures that used the 5 basic operations most in my applications:

render

rended JSON

return map

redirect

return string

And I threw in a service for good measure, and then wrote integration tests.
Here's a couple of points to remember:

When a map is returned, the map is available directly as the return value from the action

When JSON is returned, use the controller.response.contentAsString

When a String is returned, it is available directly as the return value from the action

When the action does a render, use the controller's ModelAndView's model property to test model data. Note that the view did not actually render but you can test the viewName property value. Also note that what you can't seem to be able to do is see how the page would ultimately be rendered.

When the action does a redirect, check the url with the controller's MockHttpServletResponse

To use a service, the test has to interject the service. Note there may be other things that Grails normally interjects for you that you may have to manually set

Here's the domain:


class Person {
    String firstName, lastName, email
    String toString() {"$id $firstName $lastName <$email>" }
}

Here's the controller:


class PersonController {
    def list = {
        [ personList: Person.list( params ) ]
    }
    def showWithReturnMap = {
        def person = Person.get( params.id )
        if(!person) {
            flash.message = "Person not found with id ${params.id}"
            redirect(action:list)
        }
        else { return [ person : person ] }
    }  
    def showWithReturnString = {
      return Person.get( params.id ).toString()
    } 
    def showWithRender = {
     render view:'show', model:[ person : Person.get( params.id ) ] 
    }
    def showWithJSON = {
      render Person.get( params.id ) as JSON
    }

    MathService mathService 
    def usesService = {
     return mathService.add(params.a, params.b).toString()
    }
}

And here's the integration test:


class PersonControllerTests extends GroovyTestCase {
    // when true, Grails does a rollback after each test method
    boolean transactional = false

 void setUp() {
  new Person(lastName:'Denoncourt', firstName:'Don', email:'ddenoncourt@cassevern.com').save()
 }

 void testShowWithReturnMap() {
  def controller = new PersonController()
  controller.params.id = 1
  def model = controller.showWithReturnMap()
  assertFalse "Person was found",  
    controller.flash.message ==~ /Person not found with id 1/
  assertNotNull "Person returned as model", model
  assertEquals "Person is a Denoncourt", 
    model.person.lastName, 'Denoncourt'
 }

    /* When a String is returned, 
     * it is available directly as the return value 
     * from the action   
     */
 void testShowWithReturnString() {
  def controller = new PersonController()
  controller.params.id = 1
  def model = controller.showWithReturnString()
  assertEquals "Don Denoncourt found", 
     model, "1 Don Denoncourt "
 }

    /* When the action does a render, 
     * use the controller's ModelAndView's model property 
     * to test model data 
     * Note that the view did not actually render but you can test 
     * the viewName property value 
     * What you can't seem to be able to do is see how the page 
     * would ultimately be rendered.
     */
 void testShowWithRender() {
    def controller = new PersonController()
    controller.params.id = 1
    controller.showWithRender()
    assertTrue "Person was found", 
      !( controller.flash.message =~ 
         /Person not found with id/
       )
    assertNotNull "Person returned in ModelAndView", 
      controller.modelAndView.model
    assertEquals "Person is a Denoncourt", 
       controller.modelAndView.model.person.lastName, 
      'Denoncourt'
    assertEquals "view should be show", 
       controller.modelAndView.viewName, 
       "/person/show"
 }
     
    /* When the action does a redirect, check the url with the controller's MockHttpServletResponse */
    void testShowButRedirected() {
      def controller = new PersonController()
      controller.params.id = 9898
      def model = controller.showWithReturnMap()
      assertNotNull "Should have a flash message", controller.flash
      assertTrue "Person should not be found", 
         controller.flash.message ==~ 
         /Person not found with id 9898/
      assertEquals "/person/list", 
        controller.response.redirectedUrl
    }
    /* When JSON is returned, 
     * use the controller.response.contentAsString  
     */
    void testShowWithJSON() {
      def controller = new PersonController()
      controller.params.id = 1
      controller.showWithJSON()
      assertEquals("""{"id":[1,"class":"Person",
         "email":"ddenoncourt@cassevern.com",
         "firstName":"Don","lastName":"Denoncourt]"}""",
         controller.response.contentAsString)
    }
    
    /* to use a service, 
     * the test has to interject the service 
     */
    MathService mathService
    void testUsesService() {
      def controller = new PersonController()
      controller.mathService = mathService
      controller.params.a = 2
      controller.params.b = 2
      def model = controller.usesService()
      assertEquals "2+2=4", model, "4"
    }
}

GORM insert and update auto-set of createUser and updateUser

2008-02-16T12:27:00.000-08:00

Our company has a standard of using the following attributes on all database tables:


 String createUser 
 Date createDate  
 String updateUser 
 Date updateDate

But we did not want to put code in all controller's to set these fields on insert or update. Grails has a nice facilities where you specify code in the beforeInsert and beforeUpdate closures to set the dates:


 def beforeInsert = {
  createDate = new Date()
 }
 def beforeUpdate = {
  updateDate = new Date()
 }

Grails will call these closures automatically. But the issue is setting the createUser and updateUser. What we did is create an abstract base class (concrete makes Grails look for a table called base):


abstract class Base {
  
 String createUser = ''
 Date createDate = new Date()
 String updateUser = '' 
 Date updateDate = new Date()

 def beforeInsert = {
  setCreateUsername() // must be injected in login controller
  createDate = new Date()
  delegate.createUser = this.createUser
  delegate.updateUser = this.updateUser
 }
 
 def beforeUpdate = {
  setUpdateUsername() // must be injected in login controller
  updateDate = new Date()
  delegate.updateUser = this.updateUser
 }
}

Each of our domain classes extended Base (so they did not have to specify the 4 fields.)
Then, when the user login is handled by the Login controller, we inject the setCreateUsername and updateUsername "methods" as closures that set the username with the HttpSession value:


def user = User.findWhere(userId:params.userId, userPwd:params.userPwd)
session.user = user 
Base.metaClass.setCreateUsername = {
  createUser = session.user.userId
  updateUser = session.user.userId
}
Base.metaClass.setUpdateUsername = {
  updateUser = session.user.userId
}

And the four columns are automatically set on all insert and updates.

Grails WebFlow and explicit events

2008-02-16T11:38:00.000-08:00

Grails WebFlow enables the ability to keep a context specific to a set of pages. Grails webflow is built on top of Spring webflow but, as always, Grails adds ease of use with a DSL.
When do you need webflow? Whenever you find yourself stuffing things in the session context.
One side note, if you are putting something in the session that is used in the very next request, use the flash context, as it retains items in the flash scope for two request cycles (unless the item is refreshed on the second request.

A webflow is contained in one controller. Reading existing webflows is fairly easy but, until you become comfortable with a few terms, writing webflows can be problematic. Things that you need to understand are Flow Scopes, View States, and Action States.

To get started with webflow, read, in its entirety, the documentation at WebFlow and then download and play with the book-flow
sample application. If you do not know how to check out a Subversion, email me and I'll walk you through it.

Grails WebFlow seems to be limited somewhat in GSP tags: g:submitButton and g:link. Remember that in the Grails RESTful architecture, URLs follow the domain/controller/action convention to identify the controller and closure (the action) to handle the request. WebFlows adds the concept of an event. Strangely the g:submitButton's name attribute defines the event but the g:link uses the more obvious event attribute. But what if you want to use an image button or invoke a request from a JavaScript event?

If you look at the HTML generated from the g:submitButton or g:link tags you will see a request parameter with a name the is prefixed with _eventId_. The string that follows the second underscore identifies the WebFlow event.
So for an application that requires an image, a click of which should be handled by the edit event of a webflow:


<input class="edit" name="_eventId_edit"id="_eventId_edit" 
 value="Edit" type="image" 
  src="/FAB/images/skin/database_edit.png" 
/>

To have a select list that runs the createMop (method of payment) event when a user select a item from the method of payment types list:


<g:select id='mopType' name='mopType'
  onchange="location='/FAB/document/documentWork?_eventId_createMop=createMop&'+Form.serialize(\'docLogForm\');"
  from='${(BootStrap.mopTypes.entrySet().value)}' 
  keys='${(BootStrap.mopTypes.entrySet().key)}'    
/>

Notice the use of the serialize method of the Form object. The serialize method comes from the prototype JavaScript library.

Grails Eclipse Plugin, very basic functionality

2007-12-10T14:12:00.000-08:00

The Grails generation utilities are designed to run from a system command line. But I keep running into folks that just don't want to go to DOS. They want a GUI. Well, someone has created an Eclipse plugin (which works with WDSc 7.0). It is available at:

http://www.groovy-news.org/e/page/axelclk?entry=eclipse_grails_plugin_very_early

Please don't forget to restart Eclipse with the clean option after copying the plugin to your Eclipse directory.
For WDSc users that means copying the downloaded org.codehaus.grails.eclipse.externaltools_1.0.0.jar file to
C:\Program Files\IBM\SDP70\plugins
Then ending WDSc and, in a DOS window:


cd C:\Program Files\IBM\SDP70
eclipse -clean

The plugin is invoked by right-mousing on your Grails project in Eclipse and selecting Grails.

It is limited to the following options:

Create Domain Class

Create Controller

Create Testsuite

Create Domain Class

Create Taglib

Generate Controller

Generate Views

Generate All

Create Webtest

Run Webtest

Test Application

Run Application

Note too that there's an issue with Run Application that you need to be aware of: While its output goes to the Eclipse console, clicking on the red square "terminate" icon does not stop the launched JVM, which prevents you from running the application again (you'll get a TCP/IP bind error as port 8080 is allocated to the JVM.)
A simple solution for this is to bring up Windows Task Manager (with Control-Alt-Delete,) click on the Processes tab, find the image name of Java (you can sort by clicking on the Image Name tab,) select java.exe, and finally click the End Process button.
If you have multiple java.exe processes listed, be careful because you may terminate a process that's doing something that you may not want to terminate.

Grails template for Web-enabling RPG

2007-11-30T11:47:00.001-08:00

The Grails Utilities for System i includes a utility to generate RPG call beans from SQL External Stored Procedures that are implemented with RPG. The utility creates both Java classes and Groovy classes. There is a Groovy and a Java class that wrappers a call to the stored procedure and there is (currently just Java) a class wrapper for the returned result set. The generated Java code can then be used in Java applications and the Groovy (and Java) can be used in Grails apps.

I'm planning on, first of all, adding the Groovy class wrapper to hold the result set values. But also I'm going to add the generation of a Grails domain class to hold the input parameters for the RPG call bean class (which calls the stored procedure.) The idea is that then it will be easy to generate a GSP prompt for the input parameters. Also, the generated domain class will have Grails constraints to validate user input.

But, you ask, isn't a domain coupled with a table? Only if you use the domain CRUD methods. What I do is use domain.validate() to use Grails' fantastic validation feature then call the RPG call bean.

So, going the extra mile (which, with Groovy is really only a couple of yards,) I plan on extending the tools with:

A domain class generator, that will also gen constraints for user input for RPG parameters

A controller template to generate Grails actions to invoke the Groovy RPG Call beans

A GSP template to generate an RPG Call Bean prompt form and a result form

While the generated code and GSP will function, in practice it really wouldn't be used in production. Rather you'd use it to test your RPG invocation and to have example, runnable code that can be refactored into production quality applications.

Groovy Futures

2007-11-29T11:49:00.000-08:00

I wanted to provide a bit of history going back to Spring 2007 when I first realized that the language called Groovy and the framework called Grails had bright futures. I kept hearing about Groovy on various technical podcasts (such as The Java Posse so I started to look curious about this new language. Then I listened to a podcast by Scott Davis The Groovy Programming Language and Scott convinced me that Groovy was indeed, as it's name suggests, pretty cool.

So I began to listen to all the podcasts I could find on Groovy and Grails. The Grails Podcast provided plenty (it now has 50.) Another site that has a number of great Grails Podcasts is About Groovy. My favorite is Neal Ford's

I also began to play with Groovy and Grails. I did what everyone should do and ran the Quick Start and then a couple Tutorials.

My timing was pretty good because four books on Groovy and Grails had just become available:

Groovy In Action

The Definitive Guide to Grails

Getting Started with Grails

Groovy Programming

Groovy in Action is my favorite. Java developers should read that book cover-to-cover. Non-Java developers need only read, initially, through chapter 6. Then read the rest of the book after they've worked with Groovy for awhile.

The Definitive Guide to Grails is just what it says. It was written by the creator of Grails. I would recommend that you read Chapter 1 and Chapter 2 of Groovy in Action before reading The Definitive Guide to Grails as the Groovy intro in the in action book is very well written.

Getting Started with Grails is tutorial based. Jason Rudolph does a very nice job. You might even read this before working your way through The Definitive Guide to Grails

Groovy Programming was written as a college text and, while thorough, it is a bit boring.

Anyway, after reading all the books and doing the various tutorials and playing with some ad hoc applications, I started to work with System i legacy database tables. And I ran into a few issues. I've resolved those issues, using the Groovy language and the Grails philosophy of "convention over configuration." I developed several tools and launched an open-source project Grails400Utils: Grails Utilities for the IBM System i

I published an article The Search for the Holy Web Development Grail(s) in the November issue of System i News. And I did my own Groovy and Grails podcast What's in a name: Groovy and Grails - The implementation, language and more

I've also developed and deployed two Grails applications that are now in production. I am currently working on three other commercial Grails applications.

The future for me is Groovy (and Grails) and I'm continuing to develop the Grails Utilities for the IBM System i while being billable doing GonG development (although I can still be coerced to do Java coding.)

Blog start

2007-11-18T13:29:00.000-08:00

My initial post. In this blog I intent to track my work as I investigate new technologies, specifically Groovy and Grails. I will be back-tracking in time to spring where I first became enamored with the hot new Groovy language and the Grails web framework.