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


kmapperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
SujithData ArchitectCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
SujithData ArchitectCommented:
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
awking00Information Technology SpecialistCommented:
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
SujithData ArchitectCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java App Servers

From novice to tech pro — start learning today.