Problems with use of sequences in prepared statements when in an unwrapped connection

[mebigfatguy]

Using eclipselink 2.7.11 and ojdbc8 21.7.0.0, I have code that basically does

        EntityManager em = getEM();
        EntityTransaction et = em.getTransaction();
        et.begin();
        Connection con = em.unwrap(Connection.class);
        PreparedStatement pps = con.prepareStatement("INSERT INTO FSS_CLOB_VIEW (LOB_ID,FILE_NAME,sd1,sd2) VALUES (FSS_CLOB_VIEW_SEQ.nextval,?,?,?)",  new String[] { "LOB_ID" });

    the prepare statement throws an exception,

     [java] [EL Warning]: 2022-10-18 23:44:05.196--ClientSession(2110462776)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.11.v20220804-52dea2a3c0): org.eclipse.persistence.exceptions.DatabaseException
     [java] Internal Exception: java.sql.SQLRecoverableException: The connection is closed
     [java] Error Code: 31

 The problem apparently is the sequence specification,if instead i  do

    PreparedStatement pps = con.prepareStatement("select FSS_CLOB_VIEW_SEQ.nextval from DUAL");

execute the query, get the new id from the result set and manually pass it in as a parameter it works fine.

Am i doing something stupid?

[dazey3]

@mebigfatguy

What is the usecase for needing to unwrap a Connection.class to create a java.sql.PreparedStatement rather than using JPA to execute the query?

For example, here is a simple Java SE / application managed usecase:

EntityManager em = emf.createEntityManager();
try {
    em.getTransaction().begin();

    Query query = em.createNativeQuery("INSERT INTO NONENTITYTABLE (id, color) VALUES (?, ?)");
    query.setParameter(1, 39);
    query.setParameter(2, "GREEN");
    query.executeUpdate();

    em.getTransaction().commit();
} finally {
    if (em.isOpen()) {
        em.close();
    }
}

Notice that I had to use EntityManager.createNativeQuery to create the query. This is because in my example NONENTITYTABLE is not a JPA Entity table! I assume that in your example, FSS_CLOB_VIEW is not managed by the JPA Entity Model anywhere too.

If the table IS managed by JPA with an Entity class, I would recommend using the JPQL EntityManager.createQuery API instead.

---

Using JPA just to unwrap a java.sql.Connection makes me feel like JPA isn't really needed for what you want and you should either inject a Connection into your application (EE) or just open your own Connection (SE)