2 Replies Latest reply on Oct 9, 2009 9:51 AM by Julien Kronegg

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

    Valerie Griffin Newbie
      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.