Using JPQL for queries to an MS-Access database using UCanAccess driver

Question

When using

JPA 2.1
EclipseLink 2.5.2
UCanAccess 4.0.4

We have the problem that the tables will get named with quotes ". This is because the underlying implementation is not really aware what dialect to use resulting in one incompatible to MSAccess. This then results in e.g. table annotations like @Table(name = "\"EMPLOYEE\"").

To solve this we have to include in orm.xml:

...
    <persistence-unit-defaults>
        <delimited-identifiers />
    </persistence-unit-defaults>
...

Now plain queries will work, but the use of JPQL in named queries will fail because the resulting SQL query will quote all column names:

[EL Warning]: 2019-06-02 21:14:27.818--UnitOfWork(2144282958)--Thread(Thread[main,5,main])--Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.4 unexpected token: Personen
Error Code: -5581
Call: SELECT "PersonId", "EMail", "Ansprechpartner", "Briefanrede", "Fax", "Name", "Ort", "PLZ", "Sortierung", "Straße", "Telefon" FROM "Personen"
Query: ReadAllQuery(name="Personen.findAll" referenceClass=Personen sql="SELECT "PersonId", "EMail", "Ansprechpartner", "Briefanrede", "Fax", "Name", "Ort", "PLZ", "Sortierung", "Straße", "Telefon" FROM "Personen"")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002)
    at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2691)
    at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:495)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1168)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127)
    at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1751)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:469)
    at de.mebrach.jungebuehne.vorverkauf.PersonenService.loadAll(PersonenService.java:20)
    at Main.main(Main.java:20)
Caused by: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.4 unexpected token: Personen

Why are you adding delimited-identifiers to ORM.xml if the delimited identifiers don't actually work on your database? — Chris
– Chris, Commented Jun 3, 2019 at 17:00
@Chris : Otherwise all tables will get quoted / have to be quoted. Which then results in e.g. table annotations like @Table(name = "\"EMPLOYEE\"") — Marvin Emil Brach
– Marvin Emil Brach, Commented Jun 4, 2019 at 10:35

Marvin Emil Brach · Accepted Answer · 2019-06-05 18:09:50Z

1

Include a trget-database property in persistence.xml:

<property name="eclipselink.target-database" value="org.eclipse.persistence.platform.database.AccessPlatformDelimiterConfig"/>

Add a new class to the class path:

package org.eclipse.persistence.platform.database;

public class AccessPlatformDelimiterConfig extends AccessPlatform {

    private static final long serialVersionUID = 7034590043310425678L;

    public AccessPlatformDelimiterConfig() {
        super();
        this.tableQualifier = "";
        this.startDelimiter = "[";
        this.endDelimiter = "]";
    }
}

edited Jun 5, 2019 at 18:09

answered Jun 2, 2019 at 19:21

Marvin Emil Brach

3,9901 gold badge34 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gord Thompson Over a year ago

You might want to try this.startDelimiter = "["; this.endDelimiter = "]"; and see if that works okay. If so, it might save you some grief if you ever have table/column names that are reserved words like user.

Marvin Emil Brach Over a year ago

@GordThompson good hint, not relevant for my particular usecase but I will add it to the answer :)

Collectives™ on Stack Overflow

Using JPQL for queries to an MS-Access database using UCanAccess driver

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related