permaculture playing cards*
The moose likes Object Relational Mapping and the fly likes EclipseLink (JPA 2.0) generates an erronous query for MySQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "EclipseLink (JPA 2.0) generates an erronous query for MySQL" Watch "EclipseLink (JPA 2.0) generates an erronous query for MySQL" New topic
Author

EclipseLink (JPA 2.0) generates an erronous query for MySQL

jaanus kaljuvee
Greenhorn

Joined: Oct 29, 2009
Posts: 3
Hello,

I'm having problems with EclipseLink (JPA 2.0) and MySQL 5.1.37 (MySQL connector 5.1.10)

I have a java code in EJB3:


which produces me a com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'starting, type, _id_usr_to, _id_usr_from FROM task WHERE (starting BETWEEN '2009'

Apparently a following query is being sent to MySQL:


Which MySQL rejects.
I changed the query manually to include the table qualifier and tested it directly on MySQL:


...and MySQL likes it. Now please help me get JPA to automatically use the table qualifier in the generated MySQL query.

Any help would be greatly appreciated.
jaanus kaljuvee
Greenhorn

Joined: Oct 29, 2009
Posts: 3
I found out that starting is a reserved MySQL keyword, hence the MySQL syntax error.
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
Database reserved words need to be quoted if used. i.e. in your @Column set the name to "\"starting\"".

In JPA 2.0 you can also set all columns to be delimited in the orm.xml, using the <delimited-identifiers> element under <persistence-unit-defaults>.

TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
jaanus kaljuvee
Greenhorn

Joined: Oct 29, 2009
Posts: 3
Thanks for the suggestions.
Since I want to generate entity beans automatically in netbeans, I decided to just rename the column. Using quotes looks more like a hack to me and might cause some problems in the future.
 
jQuery in Action, 2nd edition
 
subject: EclipseLink (JPA 2.0) generates an erronous query for MySQL