Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Problem in using SQL Keyword in JPQL(ORDER BY and SQL are not separated by a comma)
Problem in using SQL Keyword in JPQL [message #894815] Tue, 10 July 2012 15:59 Go to next message
Igor B is currently offline Igor B
Messages: 9
Registered: July 2012
Junior Member
Hi,

I try to use the following Named Query;
@NamedQuery (name="findbyNr",
query="SELECT o FROM MyBean o WHERE o.Nr= :Nr ORDER BY o.year DESC SQL('FETCH FIRST 100 ROWS ONLY')"

When I test my code I get the following exception:
Exception Description: Syntax error parsing [SELECT o FROM MyBean o WHERE o.Nr = :Nr AND ORDER BY o.year DESC SQL('FETCH FIRST 100 ROWS ONLY')].
[java] [124, 125] The ORDER BY clause has 'o.year DESC ' and 'SQL('FETCH FIRST 100 ROWS ONLY')' that are not separated by a comma.
[java] at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.createDeployFailedPersistenceException(EntityManagerSetupImpl.java:616)
[java] at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:596)
[java] at org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.getDatabaseSession(EntityManagerFactoryDelegate.java:186)

If I place a comma between DESC and SQL, so my DB2 driver complains like:

16:58:40 [SELECT - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=FIRST;;ASC DESC, DRIVER=3.57.82

So how can I solve my problem? Or is it a bug?

Any input welcome.

best regards,

Igor
Re: Problem in using SQL Keyword in JPQL [message #895097 is a reply to message #894815] Wed, 11 July 2012 16:43 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1034
Registered: July 2009
Senior Member
SQL is not a key word in JPQL - there is no way to use plain SQL within a JPQL query. You either must use a nativeQuery and pass in the full SQL string you want to execute, or rely on non-JPA specific provider functionality to append your SQL to the JPQL query.

In EclipseLink you might do that by getting the SQL generated for the JPQL query, append to it. Getting the SQL is described here:
http://wiki.eclipse.org/EclipseLink/FAQ/JPA#How_to_get_the_SQL_for_a_Query.3F

and an example here using the underlying native EclipseLink query (TopLink in this case, but its the same api):
https://forums.oracle.com/forums/thread.jspa?threadID=308895

Best Regards,
Chris
Re: Problem in using SQL Keyword in JPQL [message #895099 is a reply to message #895097] Wed, 11 July 2012 16:58 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1034
Registered: July 2009
Senior Member
You could use the query hint "eclipselink.sql.hint" to specify database hints, but these are added immediately after the SELECT in the SQL - you can file an enhancement to get them printed at the end of the SQL for the DB2 platform if that is required.


Re: Problem in using SQL Keyword in JPQL [message #895124 is a reply to message #895099] Wed, 11 July 2012 18:20 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1034
Registered: July 2009
Senior Member
I missed that the SQL key word was added to EclipseLink 2.4:
http://wiki.eclipse.org/EclipseLink/DesignDocs/312146

It is treated though like a function in JPQL processing and native EclipseLink expressions support. Please file a bug to have it excluded from validation and directly added to the generated SQL. My prior posts detail alternate solutions.

Best Regards
Chris
Re: Problem in using SQL Keyword in JPQL [message #899629 is a reply to message #895124] Wed, 01 August 2012 15:53 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

The SQL operator can only be used inside an existing clause in JPQL, it cannot define its own clause.

Try,
SELECT o FROM MyBean o WHERE o.Nr= :Nr ORDER BY SQL('? DESC FETCH FIRST 100 ROWS ONLY', o.year)

You may also consider the JPA maxResults option on Query.


James : Wiki : Book : Blog : Twitter
Re: Problem in using SQL Keyword in JPQL [message #903158 is a reply to message #899629] Wed, 22 August 2012 09:46 Go to previous message
Igor B is currently offline Igor B
Messages: 9
Registered: July 2012
Junior Member
Hi James,

thank you for your solution, works perfect!

best regards,

Igor
Previous Topic:@OneToMany Annotation problem
Next Topic:Force Insert Query when calling persist()?
Goto Forum:
  


Current Time: Sat Oct 25 09:03:30 GMT 2014

Powered by FUDForum. Page generated in 0.17338 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software