Community
Participate
Working Groups
Build Identifier: If you have an entity with a col named order the following SQl will get generated: SELECT ID, ACTIVE, DOKUMENT_ID, NAME, ORDER, PARENT_ID, TEXT, VERSION FROM Abschnitt WHERE (DOKUMENT_ID = ?) This fails because 'ORDER' is a reserved keyword. Adding quotes around colum names fixes the problem: SELECT 'ID', 'ACTIVE', 'DOKUMENT_ID', 'NAME', 'ORDER', 'PARENT_ID', 'TEXT', 'VERSION' FROM Abschnitt WHERE (DOKUMENT_ID = ?); Stacktrace: Internal Exception: 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 'ORDER, PARENT_ID, TEXT, VERSION FROM Abschnitt WHERE (DOKUMENT_ID = 14)' at line 1 Error Code: 1064 Call: SELECT ID, ACTIVE, DOKUMENT_ID, NAME, ORDER, PARENT_ID, TEXT, VERSION FROM Abschnitt WHERE (DOKUMENT_ID = ?) bind => [1 parameter bound] Query: ReadAllQuery(referenceClass=Abschnitt sql="SELECT ID, ACTIVE, DOKUMENT_ID, NAME, ORDER, PARENT_ID, TEXT, VERSION FROM Abschnitt WHERE (DOKUMENT_ID = ?)") at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333) ~[na:na] at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644) ~[na:na] at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535) ~[na:na] at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1702) ~[na:na] at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:566) ~[na:na] at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:207) ~[na:na] at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207) ~[na:na] at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193) ~[na:na] at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:264) ~[na:na] at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:646) ~[na:na] at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2592) ~[na:na] at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2551) ~[na:na] at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:418) ~[na:na] at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1097) ~[na:na] at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:829) ~[na:na] at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1056) ~[na:na] at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:390) ~[na:na] at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1144) ~[na:na] at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2863) ~[na:na] at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1501) ~[na:na] at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1483) ~[na:na] at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1457) ~[na:na] at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:485) ~[na:na] at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getResultList(EJBQueryImpl.java:742) ~[na:na] at javax.persistence.util.JPAUtil$JPAEntityUtil$1.list(JPAUtil.java:186) ~[na:na] at de.laeubisoft.berichtgenerator.storage.db.document.DBDokumentProvider.createDokument(DBDokumentProvider.java:45) ~[na:na] at de.laeubisoft.berichtgenerator.storage.db.DBStorage$4$1.access(DBStorage.java:164) ~[na:na] at de.laeubisoft.berichtgenerator.storage.db.DBStorage$4$1.access(DBStorage.java:1) ~[na:na] at javax.persistence.util.JPAUtil.access(JPAUtil.java:58) ~[na:na] at javax.persistence.util.JPAUtil$1.access(JPAUtil.java:76) ~[na:na] at javax.persistence.util.JPAUtil.access(JPAUtil.java:40) ~[na:na] at javax.persistence.util.JPAUtil.access(JPAUtil.java:72) ~[na:na] at de.laeubisoft.berichtgenerator.storage.db.DBStorage$4.getDocument(DBStorage.java:160) ~[na:na] at de.laeubisoft.berichtgenerator.gui.panels.DocumentOpener$1.doInBackground(DocumentOpener.java:67) ~[na:na] at de.laeubisoft.berichtgenerator.gui.panels.DocumentOpener$1.doInBackground(DocumentOpener.java:1) ~[na:na] at javax.swing.SwingWorker$1.call(SwingWorker.java:277) ~[na:1.6.0_23] at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) ~[na:1.6.0_23] at java.util.concurrent.FutureTask.run(FutureTask.java:138) ~[na:1.6.0_23] at javax.swing.SwingWorker.run(SwingWorker.java:316) ~[na:1.6.0_23] at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) ~[na:1.6.0_23] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) ~[na:1.6.0_23] at java.lang.Thread.run(Thread.java:662) ~[na:1.6.0_23] Caused by: 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 'ORDER, PARENT_ID, TEXT, VERSION FROM Abschnitt WHERE (DOKUMENT_ID = 14)' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.6.0_23] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) ~[na:1.6.0_23] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) ~[na:1.6.0_23] at java.lang.reflect.Constructor.newInstance(Constructor.java:513) ~[na:1.6.0_23] at com.mysql.jdbc.Util.handleNewInstance(Util.java:407) ~[na:na] at com.mysql.jdbc.Util.getInstance(Util.java:382) ~[na:na] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) ~[na:na] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3603) ~[na:na] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535) ~[na:na] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989) ~[na:na] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150) ~[na:na] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626) ~[na:na] at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119) ~[na:na] at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2281) ~[na:na] at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:931) ~[na:na] at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:607) ~[na:na] ... 40 common frames omitted Reproducible: Always Steps to Reproduce: 1. Create an entity with mysql database provider and colum named order 2. try to fetch the entity 3. Execute the generated SQL with mysql query browser for example --> same error occurs 4. Add quotes around the colum names --> query succeeds Workaround: Don't use reserved names as colum identifiers...
Changing to enhancement. Our philosophy regarding this paricular issue has been to have persistence-unit designers deal with this issue themselves either by choosing different column names or by using the delimiting support provided in JPA.
Hi Tom, this is IMO still an (annoying) issue. Especially if one wants to write portable application that runs on different database systems it is very disturbing the the "persistence-unit designer" must think about how to name the fields of the class. I can understand that if someone forces the name (e.g. by @Column) to a particular value there is no way to change it, but for the autogenerated names I think the Persitence Provider should choose compatible names. The most common ones on my daily work are for example: - Have class that imposes some kind of ordering and thus has an integer field named "order", mysql complains always about that names and spits out confusing error messages that order is a keyword and must be quoted. - Mappings of key/value, so the Class "Entry" has a key field and a value field -> same issue since key is a keyword and is not quoted - Having a class referencing some child items via a values field -> again collides with VALUES keyword The most annoying thing is that this even can influence the table generation, so EclipseLink generates invalid ddl statements and silently ignores the error (well as least it logs some kind of warning) and you only get a problem if you try to access the PU item later on, with an "TABEL DOES NOT EXITS" error... Of course I can always rename the field or put an @Colum annotation o it, but that is really distrubing because JPA was meant to make autoconfiguration possible, so it would be a really good idea to add support so that either: - a PU is rejected with a meaningful error message - or even better: either quote the column name or simply add an underscore to the autogenerated name
1 year later and still an issue, hit the same problem with mssql (works fine for h2 database where we want to choose between): > Incorrect syntax near the keyword 'database'. This is really a mess, JPA should abstract things from the raw database details, its completely annoying having to tweak member names so these gets not confused and counteract the idea of an abstraction. Is it really such a big deal to add quotes around either all names or at least around names that match database keywords?
*** Bug 536651 has been marked as a duplicate of this bug. ***
Moved to https://github.com/eclipse-ee4j/eclipselink/issues/842
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
