Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2057
  • Last Modified:

HQL Query parse error when using UPPER keyword

Here is an easy one. The next piece of code shows an HQL Query I am executing in my program.

String queryString = "update Instance model set model.excluded = :value where model.label in (:instances)";


When I execute it like this everything executes without any error. But when I add an UPPER() keyword to it, i get a parse error:

String queryString = "update Instance model set model.excluded = :value where UPPER(model.label) in (:instances)";



<4|false|0.9.7> org.apache.openjpa.persistence.ArgumentException: An error occurred while parsing the query filter 'update Instance model set model.excluded = :value where UPPER(model.label) in (:instances)'. Error message: <4|false|0.9.7> org.apache.openjpa.kernel.jpql.ParseException: Encountered "UPPER ( model . label ) in" at character 57, 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", "MIN", "MOD", "NEW", "NOT", "NULL", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM", "UPDATE", "UPPER", "WHERE", <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>, <IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].


As you can see, "UPPER" is in the expected keywords?!?! I guess there is something I am missing!

Context:
EJB v3.0
AppServer: Weblogic 10
DB: Oracle 10g


0
kmapper
Asked:
kmapper
  • 7
  • 3
  • 2
  • +1
1 Solution
 
chaitu chaituCommented:
use lowercase upper();

"update Instance model set model.excluded = :value where upper(model.label) in (:instances)";

http://www.hibernate.org/hib_docs/nhibernate/html/queryhql.html


0
 
kmapperAuthor Commented:
Same result :/
0
 
sujith80Commented:
the error is at parse time or run time?

Can you print the bind variable values just before the execution of the sql and post the results. (Basically I would like to see the actual update statement that runs using the code.)

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
kmapperAuthor Commented:
at parse time so there is no resulting sql query beeing sent to the BD.

So the bind values are like: 'Y' for the value and a Set of String for :instances
0
 
sujith80Commented:
Do you mind posting the actual values get bound to the bind variables?
0
 
chaitu chaituCommented:
i think u have use setParameterList(instances,valueslist) instead of setString(instances,valueslist);
0
 
kmapperAuthor Commented:
sujith80: I'll post it asap (I'm on the road right now). But I'm pretty sure the bind variables are not the problem since the query without the upper keyword works :/

chaituu: I'm using a javax.persistence.query which does not expose a setParameterList. The query does work with setParameter except it does not modify all the records i want it to modify since the case is different, but I'm sure it does work. Only the upper keyword is not parsed correctly.

http://java.sun.com/javaee/5/docs/api/javax/persistence/Query.html
0
 
awking00Commented:
See attached comments.
comments.txt
0
 
kmapperAuthor Commented:
awking00: I'll try it again on monday but as far as i can remember i tried to take off the "model" qualifier and it still didn't work.  I'll confirm that asap. Thanks
0
 
kmapperAuthor Commented:
sujith80:
Bind values:
:value = Y
:instances is a Collection of String:
:instances[0] = SquareYard
:instances[1] = Meter
:instances[2] = Grain
:instances[3] = Strong
:instances[4] = Static
:instances[5] = Year


awking00:

String queryString = "update Instance set excluded = :value where name in (:instances)";

When removing the model qualifier, I get:

<4|false|0.9.7> org.apache.openjpa.persistence.ArgumentException: An error occurred while parsing the query filter 'update Instance set excluded = :value where name in (:instances)'. Error message: <4|false|0.9.7> org.apache.openjpa.kernel.jpql.ParseException: Encountered "=" at character 30, but expected: ["."].


chaituu:
When using lowercase upper();

"update Instance model set model.excluded = :value where upper(model.label) in (:instances)";
 
<4|false|0.9.7> org.apache.openjpa.persistence.ArgumentException: An error occurred while parsing the query filter 'update Instance model set model.excluded = :value where upper(model.name) in (:instances)'. Error message: <4|false|0.9.7> org.apache.openjpa.kernel.jpql.ParseException: Encountered "upper ( model . name ) in" at character 57, 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", "MIN", "MOD", "NEW", "NOT", "NULL", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM", "UPDATE", "UPPER", "WHERE", <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>, <IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].
0
 
kmapperAuthor Commented:
Here is an update:
This query throws an exception:

String queryString = "update Instance model set model.excluded = :value where upper(model.name) in (:instances)";
While this one does not:

String queryString = "update Instance model set model.excluded = :value where upper(model.name) = :instances";
It seems the parser crashes when using transformation function with an IN CLAUSE :/ Could that be a bug?!
0
 
sujith80Commented:
I dont think binding an array could work with an IN list.

You can achieve the same results by this.

Form a string by concatenating the Collection with a delimiter say ':', your string will look something like below

SquareYard:Meter:Grain:Strong:Static:Year

Now change the query as

String queryString = "update Instance model set model.excluded = :value where upper(:instances_str) like '%'||upper(model.name)||'%' ";

(Bind variable :instances_str has to be bound with the string you formed from the collection.)
0
 
kmapperAuthor Commented:
Hi everyone, here is the last update, it was a bug from OpenJPA.
http://n2.nabble.com/ParseException-when-using-UPPER-Keyword-with-an-IN-CLAUSE-td1480819.html
Thanks all for your help!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 7
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now