seam-gen search problem with "lower" in DB2 with fix

[vgriffin]

This problem, and a workaround, was first encountered by one of my colleagues.

We use DB2.

The application (so far) is created by seam-gen.

If there are varchar elements, the search allows the user to enter the first part and uses a case-insensitive test. This is accomplished by generating RESTRICTIONS like:
  "lower(tbl.colName) like concat(lower(#{tblList.tbl.colName}),'%')"

Hibernate converts it to a prepared statement containing:
  ... where lower(tbl.colName) like lower(?)||'%'

The problem with this is that DB2 does not accept untyped input for the lower function. It returns an error SQLCODE: -418, SQLSTATE: 42610

There is a discussion of this problem in the hibernate forum with the suggestion that the input to lower be cast to an appropriate data type. Here, the fix is easier. My colleague discovered that if the nesting order is changed to "lower(concat" hibernate no longer applies its simplification and the query works.

This change should work for other database types.

Of course, this means we have to change each of our generated files....

Fortunately, there's an easier way. I changed seam-gen/src/EntityList.java.ftl and seam-gen/icefaces-staging/src/EntityList.java.ftl to use "lower(concat" instead. Now, the code is generated with the test that works.

[vgriffin]

As I was driving home, I remembered that I had omitted part of the change. You also need to move the text ", '%'" into the nested parenthesis so you end up with code like:
  lower(concat(${'#'}{${listName}.${componentName}.${property.name}},'%'))

There are two lines to change in each ftl.

[jkronegg]

See also JBSEAM-3297 on that topic