Built with Seam
You can find the full source code for this website in the Seam package in the directory /examples/wiki. It is licensed under the LGPL.
| Online: | 29 Members of 4060 |
| Forum: Seam Users |
 |
14. Aug 2008, 04:27 CET | Link |
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.
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.
Tags: database
lower(concat(${'#'}{${listName}.${componentName}.${property.name}},'%'))
There are two lines to change in each ftl.