UCanAccess and EclipseLink Persistence Framework

[congjun-yang]

I am not sure who else would use EclipseLink with MS Access. But the following issue seems really odd with such a simple scenario:
1. insert a record
2. update it
3. update it again
The second update fails quietly. The following is the example code.

    EntityManagerFactory emf = Persistence.createEntityManagerFactory("AccessUnit");
    EntityManager em = emf.createEntityManager();
    int id = 100;

    try {
        // 1. INSERT using Native Query
        String insertSQL = "INSERT INTO Contacts (ID, Full_Name, Email) VALUES (?, ?, ?)";
        Query insertQuery = em.createNativeQuery(insertSQL);
        insertQuery.setParameter(1, id);
        insertQuery.setParameter(2, "Jane Smith4");
        insertQuery.setParameter(3, "jane.smith@example.com");

        em.getTransaction().begin();
        int rowsInserted = insertQuery.executeUpdate();
        System.out.println("Inserted: " + rowsInserted);
        em.getTransaction().commit();
    	
        // 2. UPDATE using Native Query
        String updateSQL = "UPDATE Contacts SET Phone = ?, Update_time = ? WHERE ID = ?";
        Query updateQuery = em.createNativeQuery(updateSQL);
        updateQuery.setParameter(1, "555-9975");
        updateQuery.setParameter(2, LocalDateTime.now());
        updateQuery.setParameter(3, id);
        em.getTransaction().begin();
        int rowsUpdated = updateQuery.executeUpdate();
        em.getTransaction().commit();
        System.out.println("Updated once: " + rowsUpdated);

        // 3. UPDATE using Native Query ---------------------Quietly Failing --------------------------
        updateSQL = "UPDATE Contacts SET Phone = ?, Update_time = ? WHERE ID = ?";
        updateQuery = em.createNativeQuery(updateSQL);
        updateQuery.setParameter(1, "555-9985");
        updateQuery.setParameter(2, LocalDateTime.now());
        updateQuery.setParameter(3, id);
        em.getTransaction().begin();
        rowsUpdated = updateQuery.executeUpdate();
        em.getTransaction().commit();
        System.out.println("Updated twice: " + rowsUpdated);
    } catch (Exception e) {
        if (em.getTransaction().isActive()) em.getTransaction().rollback();
        e.printStackTrace();
    } finally {
        em.close();
        emf.close();
    }

Here is the persistence.xml

<persistence-unit name="AccessUnit" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <properties>
        <property name="jakarta.persistence.jdbc.driver" value="net.ucanaccess.jdbc.UcanaccessDriver"/>
		<property name="jakarta.persistence.jdbc.url" value="jdbc:ucanaccess://D:/JDBCTest/test.accdb"/>
		<property name="eclipselink.target-database" value="HSQL"/>
        <property name="eclipselink.logging.level" value="FINEST"/>
        <property name="eclipselink.logging.parameters" value="true"/>
    </properties>
</persistence-unit>

The phone is still '555-9975' after the program ends. The last update was lost. Otherwise, it would be '555-9985'.
If you query the table before the program exits, it returns '555-9985' correctly. So, the last update stayed in memory only.
One thing worth noting is that 'Update_time' is a Date/Time column. Without it, everything works fine.
Same result on a few different versions of EclipseLink. The latest version used was 4.0.8. It behaves the same with UcanAccess 5.0.x and 5.1.x

[spannm]

Thanks for the detailed report. Here's my best guess at what's going on.

The most suspicious part of your configuration is:

<property name="eclipselink.target-database" value="HSQL"/>

UCanAccess is not a plain HSQLDB connection — it uses HSQLDB internally as an in-memory mirror, but exposes its own JDBC layer on top.
Setting target-database=HSQL may cause EclipseLink to apply HSQL-specific type mappings for LocalDateTime, producing a representation that doesn't convert cleanly to the native Access Date/Time type when UCanAccess syncs changes back to the database file via Jackcess on commit.
If that conversion fails silently, it could abort the write-back for the entire row — which would explain why the phone number is lost as well, even though it's a plain string.

The fact that removing Update_time makes the problem go away is consistent with this theory.

Two things worth trying:

1. Remove eclipselink.target-database entirely, or set it to Auto.

2. Pass a java.sql.Timestamp instead of a raw LocalDateTime:

   updateQuery.setParameter(2, Timestamp.valueOf(LocalDateTime.now()));

   This should bypass EclipseLink's HSQL-specific date/time path and passes a standard JDBC type that UCanAccess handles reliably.

Let us know whether either of these makes a difference.

 

Enjoying UCanAccess and Jackcess? Please leave a 🌟 to support the projects! Your stars help to keep open source development visible and going.

[congjun-yang]

I recently added the target-database property because of a warning from EclipseLink, and some people suggested pointing it to HSQL. It actually behaves the same with or without it. To cut a long story short, I have just confirmed that this is an issue with UCanAccess. If you don't mind, I can open an issue to share the details.

[spannm]

Sounds good to me. Please go ahead. Contributions always welcome!

[spannm]

Confirmed to be an issue related to Date/Time precision handling between UCanAccess and Jackcess that leads to a 'quiet fail' during updates because the truncated value in the database no longer matches the high-precision value in the persistence context. Tracking the fix in #39.