Server side (case insensitive) filtering using JPADataSource

[david.mason]

We encountered the following error when trying to perform server side filtering using a JPADataSource:

=== 2012-02-24 13:54:35,103 [l 99] INFO  RequestContext - URL: '/users-proto/com.puffersoft.esp.users.Application/sc/IDACall', User-Agent: 'Mozilla/5.
0 (Windows NT 6.1; WOW64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11': Safari with Accept-Encoding header
=== 2012-02-24 13:54:35,135 [l 99] INFO  IDACall - Performing 1 operation(s)
=== 2012-02-24 13:54:35,135 [l 99] DEBUG JPADataSource - [builtinApplication.entities_fetch] Creating EntityManager and starting transaction.
=== 2012-02-24 13:54:35,135 [l 99] DEBUG JPADataSource - [builtinApplication.entities_fetch] Executing fetch.
=== 2012-02-24 13:54:35,136 [l 99] DEBUG JPADataSource - [builtinApplication.entities_fetch] Query string: select _EntityImpl from EntityImpl _EntityI
mpl where (lower(_EntityImpl.name) like lower(:p0) escape '~') and _EntityImpl.parentId is null
=== 2012-02-24 13:54:35,140 [l 99] DEBUG JPADataSource - [builtinApplication.entities_fetch] Parameter p0: %win%
=== 2012-02-24 13:54:35,144 [l 99] DEBUG JPADataSource - [builtinApplication.entities_fetch] Marking transaction for roll back.
=== 2012-02-24 13:54:35,145 [l 99] DEBUG JPADataSource - [builtinApplication.entities_fetch] Got exception while executing. Transaction will be rolled
 back.
<openjpa-1.2.1-r752877:753278 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: Encountered "lower ( _EntityImpl . name ) like lo
wer" at character 55, but expected: ["(", ")", "+", "-", ".", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG
", "BETWEEN", "BOTH", "BY", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "
EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INNER", "IS", "JOIN", "LEADING", "LEFT", "LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MI
N", "MOD", "NEW", "NOT", "NULL", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRI
M", "UPDATE", "UPPER", "WHERE", <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>, <IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].
        at org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9501)
        at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9378)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1947)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1925)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1791)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1753)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1942)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1925)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1791)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1753)
        at org.apache.openjpa.kernel.jpql.JPQL.where_clause(JPQL.java:1556)
        at org.apache.openjpa.kernel.jpql.JPQL.select_statement(JPQL.java:91)
        at org.apache.openjpa.kernel.jpql.JPQL.parseQuery(JPQL.java:63)
        at org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.parse(JPQLExpressionBuilder.java:1740)
        at org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.<init>(JPQLExpressionBuilder.java:1727)
        at org.apache.openjpa.kernel.jpql.JPQLParser.parse(JPQLParser.java:48)
        at org.apache.openjpa.kernel.ExpressionStoreQuery.newCompilation(ExpressionStoreQuery.java:149)
        at org.apache.openjpa.kernel.QueryImpl.newCompilation(QueryImpl.java:657)
        at org.apache.openjpa.kernel.QueryImpl.compilationFromCache(QueryImpl.java:639)
        at org.apache.openjpa.kernel.QueryImpl.compileForCompilation(QueryImpl.java:605)
        at org.apache.openjpa.kernel.QueryImpl.compileForExecutor(QueryImpl.java:667)
        at org.apache.openjpa.kernel.QueryImpl.getOperation(QueryImpl.java:1492)
        at org.apache.openjpa.kernel.DelegatingQuery.getOperation(DelegatingQuery.java:123)
        at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:243)
        at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:294)
        at com.isomorphic.jpa.JPADataSource.executeFetch(JPADataSource.java:789)
        at com.isomorphic.datasource.DataSource.execute(DataSource.java:1287)
        at com.isomorphic.jpa.JPADataSource.execute(JPADataSource.java:439)
        at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:721)
        at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
        at com.isomorphic.application.AppBase.execute(AppBase.java:491)
        at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:1948)
        at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:199)
        at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:156)
        at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:121)
        at com.isomorphic.servlet.IDACall.doPost(IDACall.java:73)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:713)
        at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:806)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.geronimo.tomcat.GeronimoStandardContext$SystemMethodValve.invoke(GeronimoStandardContext.java:420)
        at org.apache.geronimo.tomcat.valve.GeronimoBeforeAfterValve.invoke(GeronimoBeforeAfterValve.java:47)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:567)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:361)
        at org.apache.geronimo.pool.ThreadPool$1.run(ThreadPool.java:214)
        at org.apache.geronimo.pool.ThreadPool$ContextClassLoaderRunnable.run(ThreadPool.java:344)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:662)
=== 2012-02-24 13:54:35,189 [l 99] WARN  RequestContext - dsRequest.execute() failed:
<openjpa-1.2.1-r752877:753278 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: Encountered "lower ( _EntityImpl . name ) like lo
wer" at character 55, but expected: ["(", ")", "+", "-", ".", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG
", "BETWEEN", "BOTH", "BY", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "
EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INNER", "IS", "JOIN", "LEADING", "LEFT", "LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MI
N", "MOD", "NEW", "NOT", "NULL", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRI
M", "UPDATE", "UPPER", "WHERE", <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>, <IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].
        at org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9501)
        at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9378)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1947)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1925)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1791)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1753)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1942)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1925)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1791)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1753)
        at org.apache.openjpa.kernel.jpql.JPQL.where_clause(JPQL.java:1556)
        at org.apache.openjpa.kernel.jpql.JPQL.select_statement(JPQL.java:91)
        at org.apache.openjpa.kernel.jpql.JPQL.parseQuery(JPQL.java:63)
        at org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.parse(JPQLExpressionBuilder.java:1740)
        at org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.<init>(JPQLExpressionBuilder.java:1727)
        at org.apache.openjpa.kernel.jpql.JPQLParser.parse(JPQLParser.java:48)
        at org.apache.openjpa.kernel.ExpressionStoreQuery.newCompilation(ExpressionStoreQuery.java:149)
        at org.apache.openjpa.kernel.QueryImpl.newCompilation(QueryImpl.java:657)
        at org.apache.openjpa.kernel.QueryImpl.compilationFromCache(QueryImpl.java:639)
        at org.apache.openjpa.kernel.QueryImpl.compileForCompilation(QueryImpl.java:605)
        at org.apache.openjpa.kernel.QueryImpl.compileForExecutor(QueryImpl.java:667)
        at org.apache.openjpa.kernel.QueryImpl.getOperation(QueryImpl.java:1492)
        at org.apache.openjpa.kernel.DelegatingQuery.getOperation(DelegatingQuery.java:123)
        at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:243)
        at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:294)
        at com.isomorphic.jpa.JPADataSource.executeFetch(JPADataSource.java:789)
        at com.isomorphic.datasource.DataSource.execute(DataSource.java:1287)
        at com.isomorphic.jpa.JPADataSource.execute(JPADataSource.java:439)
        at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:721)
        at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
        at com.isomorphic.application.AppBase.execute(AppBase.java:491)
        at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:1948)
        at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:199)
        at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:156)
        at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:121)
        at com.isomorphic.servlet.IDACall.doPost(IDACall.java:73)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:713)
        at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:806)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.geronimo.tomcat.GeronimoStandardContext$SystemMethodValve.invoke(GeronimoStandardContext.java:420)
        at org.apache.geronimo.tomcat.valve.GeronimoBeforeAfterValve.invoke(GeronimoBeforeAfterValve.java:47)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:567)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:361)
        at org.apache.geronimo.pool.ThreadPool$1.run(ThreadPool.java:214)
        at org.apache.geronimo.pool.ThreadPool$ContextClassLoaderRunnable.run(ThreadPool.java:344)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:662)
=== 2012-02-24 13:54:35,246 [l 99] DEBUG JPADataSource - Rolling back current transaction.
=== 2012-02-24 13:54:35,250 [l 99] DEBUG JPADataSource - Releasing entity manager.

It seem that the problem lies in the generated JPQL statement. It does not allow something like "lower(entity-name) like lower(:p0)". Refer to the following link for more information:

http://sourceforge.net/projects/jpasecurity/forums/forum/790334/topic/4558979

The fix for this is to write the JPQL into something like "lower(entity-name) like :p0" and convert the parameter to lowercase programmatically before setting the parameter into the query.

FYI, this is using the SmartGWT 3.0 EE (Evaluation), the client is running on Google Chrome.

The client side code for performing the filtering is:

tree.filterData(new Criteria("name", "test"));

This doesn't work as well:

tree.filterData(new AdvancedCriteria(new Criterion("name", OperatorId.ICONTAINS, "test")));

But this works (note the CONTAINS instead of ICONTAINS), because it doesn't involve case-insensitive filter:

tree.filterData(new AdvancedCriteria(new Criterion("name", OperatorId.CONTAINS, "test")));

[alius]

I would say it is a problem with OpenJPA.

select _EntityImpl from EntityImpl _EntityImpl where (lower(_EntityImpl.name) like lower(:p0) escape '~') and _EntityImpl.parentId is null

is valid JPQL statement because standard SQL allows following statement:

SELECT * FROM table WHERE LOWER(col) like LOWER('%param%')

I do not know why OpenJPA does not implement it.

As for converting parameter in java and then setting it to JPQL: it would be a workaround for particular JPA provider. On the other hand it potentially creates a problem when java upper/lower conversion rules does not match DB server rules. You will not notice it in English but there are lot of languages with non-latin alphabet.

Try to change your JPA provider to Hibernate or EclipseLink.

Regards,
Alius

[jp8080]

We encountered the same problem with OpenJPA.

I don't think it is a good approach to force somebody to use an other jpa framework than OpenJPA...

[alius]

We would be happy to see OpenJPA as possible JPA provider but it does not comply to standards: there is no implementation of LOWER() on field.

Please direct your complaints to OpenJPA forums and ask them to implement standard functionality.

Regards,
Alius