Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Named Query error
Named Query error [message #646809] Fri, 31 December 2010 13:27 Go to next message
rudibravo is currently offline rudibravo
Messages: 17
Registered: December 2010
Junior Member
Hi guys

I have a problem here with my NamedQuery that I'm struggling for hours and still didn't made it work

this works fine:

SELECT COUNT(m.id) from MyClass m WHERE LOWER(m.name) =: name

but if I try this query instead:

SELECT COUNT(m.id) from MyClass m WHERE REGEXP_REPLACE(m.name,'[^a-zA-Z0-9]',NULL) =: name

It throws this exception during deploy:
Caused by: Exception [EclipseLink-8025] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [checkExistsProfileByName: SELECT COUNT(m.id) from MyClass m WHERE REGEXP_REPLACE(m.name,'[^a-zA-Z0-9]',NULL) =: name], line 1, column 61: unexpected token [(].
Internal Exception: NoViableAltException(81!=[652:1: simpleConditionalExpressionRemainder[Object left] returns [Object node] : (n= comparisonExpression[left] | (n1= NOT )? n= conditionWithNotExpression[(n1!=null), left] | IS (n2= NOT )? n= isExpression[(n2!=null), left] );])

I just want to strip every character that isn't alpha-numeric from this column in my query, if I'm doing something wrong please tell me, because the query works just fine in SQL.

Thanks
Re: Named Query error [message #647081 is a reply to message #646809] Tue, 04 January 2011 14:19 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1034
Registered: July 2009
Senior Member
Hello,

By named query, I assume you are using JPQL? If so, you will want to check the grammar as it does not include the REGEXP_REPLACE function. If using EclipseLink 2.1, it includes support for native functions via the 'FUNC' JPQL key word as described here:
http://wiki.eclipse.org/EclipseLink/Release/2.1.0/JPAQueryEn hancements

Otherwise, you will need to edit the query object to append the function with EclipseLink native api, or use a native SQL query.

Best Regards,
Chris

Re: Named Query error [message #647233 is a reply to message #647081] Wed, 05 January 2011 09:04 Go to previous message
rudibravo is currently offline rudibravo
Messages: 17
Registered: December 2010
Junior Member
Yes, you are right. I figure out about that FUNC but I'm still using EclipseLink 2.0.
I switch it to native query and it's workign now.

Thanks for the help.
Previous Topic:IN clause in @NamedNativeQuery
Next Topic:Tricky OneToOne mapping
Goto Forum:
  


Current Time: Sat Oct 25 14:45:34 GMT 2014

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

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