Embedded Derby db namedquery parse error with month

[pointertowhat]

Hi,

I've got an embedded Derby DB and I use entity beans with namedqueries.

One of which is:

@NamedQuery(name = "Vehicles.findbymotmonth", query = "SELECT v FROM Vehicles v WHERE MONTH(v.lastmotdate) = :monthnumber")

If I use the IDE to send this command to the database it works fine. However when I try and run my application I get:

...
Exception [TOPLINK-8025] (Oracle TopLink Essentials - 2.0.1 (Build b09d-fcs (12/06/2007))): oracle.toplink.essentials.exceptions.EJBQLException
Exception Description: Syntax error parsing the query [Vehicles.findbymotmonth: SELECT v FROM Vehicles v WHERE MONTH(v.lastmotdate) = :monthnumber], line 1, column 32: unexpected token [MONTH].
Internal Exception: line 1:32: unexpected token: MONTH
        at oracle.toplink.essentials.exceptions.EJBQLException.unexpectedToken(EJBQLException.java:389)
...

Any ideas?

 

[Diancecht]

I'm not sure about your database structure. What is MONTH?

Cheers,
Dian

 

[pointertowhat]

MONTH is the datadase function that returns an int representing the month from a date field.

http://db.apache.org/derby/docs/10.4/ref/ref-single.html#rrefmonthfunc

The db table in question is very simple, no FK's just a few varchar fields and a couple of Date fields.

I think the bug is in the Toplink library - I've spent ages looking for a solution and in the end gave up and switched to the Hibernate persistance lib and it works fine.