Description Tom Eugelink 2010-05-17 07:55:05 EDT

Build Identifier: 2.0.2

There is a simple piece of JPA code trying to delete an entity. This entity has in the database a dependency on a table that is maintained using native SQL. If the native SQL is not executed, all is fine and a foreign key constraint exist exception is correctly thrown upon commit. But as soon as ANY native SQL statement is executed in an event method of the entity, the commit no longer is executed.

There is a newsgroup thread about this from 2010-01-12 15:20.


Reproducible: Always

Steps to Reproduce:
This is the testcode:

    public static void main(String[] args)
    {
        // EntityManager
        EntityManagerFactory lEntityManagerFactory = ...
        EntityManager lEntityManager = lEntityManagerFactory.createEntityManager();

        // bind eclipse events to Batchtransferline
        JpaEntityManager lJpaEntityManager = (JpaEntityManager)lEntityManager;
        EclipselinkDescriptorEventListenerRouter lEclipselinkDescriptorEventListenerRouter = new EclipselinkDescriptorEventListenerRouter();
        lJpaEntityManager.getSession().getClassDescriptor(Batchtransferline.class).getEventManager().addListener( lEclipselinkDescriptorEventListenerRouter );

        // delete batchtransfer
        try
        {
            Batchtransfer lBatchtransfer = lEntityManager.find(Batchtransfer.class, BigInteger.valueOf(15));
            lEntityManager.getTransaction().begin();
            Batchtransfer lMergedEntity = lEntityManager.merge(lBatchtransfer);
            lEntityManager.remove( lMergedEntity ); // remove the merged entity
            lEntityManager.getTransaction().commit();
        }
        catch (Throwable t)
        {
            t.printStackTrace(System.out);
            System.out.println(">>> " + t.getMessage());
        }
               
        // close shop
        lEntityManager.close();       
        lEntityManagerFactory.close();
    }

The Batchtransferline then has some (dummy) native SQL:

    @Override
    public void postDelete()
    {
        Query lQuery = EntityManagerFinder.find().createNativeQuery("select sum(amount) from batchcount where to_amount_batchtransferlinenr=19");
        lQuery.setFlushMode(FlushModeType.COMMIT); // make sure the query does not force the entity to be flushed to the DB
        lQuery.getSingleResult();
    }

If the lines in the event method are commented out, I get:
    ...
    [C1173553]: setAutoCommit(  false  )
    ...
    [C1173553]: executeUpdate: DELETE FROM batchtransfer WHERE ((batchtransfernr = ?) AND (lazylock = ?))
    [C1173553]: commit()
    ... stack trace
    >>> Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.DatabaseException
    Internal Exception: java.sql.SQLException: Key value for constraint (informix.u208_344) is still being referenced.
    Error Code: -692
    [EL Info]: 2010-01-12 15:08:41.206--ServerSession(30284778)--reinders_url=jdbc:reinders:com.informix.jdbc.IfxDriver#jdbc:informix-sqli://toeu_reinders:9088/reinders:INFORMIXSERVER=ol_ids_1150_1;DB_LOCALE=en_us.utf8_user=user logout successful

This is correct! The record cannot be deleted, because there are references. This is what is supposed to be solved in the event logic.
Now if the three lines in the event method are active, I get:
    ...
    [C14626104]: setAutoCommit(  false  )
    ...
    [C14626104]: executeUpdate: DELETE FROM batchtransfer WHERE ((batchtransfernr = ?) AND (lazylock = ?))
    [EL Info]: 2010-01-12 15:08:41.206--ServerSession(30284778)--reinders_url=jdbc:reinders:com.informix.jdbc.IfxDriver#jdbc:informix-sqli://toeu_reinders:9088/reinders:INFORMIXSERVER=ol_ids_1150_1;DB_LOCALE=en_us.utf8_user=user logout successful

The delete statement is the last entry in the log; no close, no commit, no rollback... No exception.