Hi there.

I am working with a database that has no primary keys. This schema is beyond my control. As recommended I have configured the mappings to have logical keys, despite no keys existing.

Where a table contains data that would break the key constraint (if it had one), the call to session.delete fails with the message "SQL insert, update or delete failed (row not found)". Logging would indicate that Hibernate has deleted multiple records when it expected to delete only one. The attempt to delete the second record with the same primary key results in the exception.

I attempted to work around this by using a raw SQL query, but I received an exception along the lines of "cursor state invalid".

Any advice on how to get around this problem?

Regards
Jem



Hibernate version:
Possibly 2.0. Jar is called hibernate2.jar. No version info in manifest.

Mapping Documents (only included the class that's failing - all are of similar format)
<hibernate-mapping>
<class name="au.com.national.equation.togwml.database.trade.Payment" table="NXMLPAP">
<composite-id>
<key-property name="eventNumber" column="PANUM" type="java.lang.String" length="20"/>
<key-property name="type" column="PATYP" type="java.lang.String" length="10"/>
<key-property name="direction" column="PADIR" type="java.lang.String" length="7"/>
<key-property name="sequenceNumber" column="PASEQ" type="java.lang.String" length="2"/>
</composite-id>
<property name="payerReference" column="PAPAYR" type="java.lang.String" length="20"/>
<property name="receiverReference" column="PARCVR" type="java.lang.String" length="20"/>
<property name="currency" column="PACCY" type="java.lang.String" length="3"/>
<property name="amount" column="PAAMT" type="java.lang.String" length="21"/>
<property name="code" column="PACDE" type="java.lang.String" length="3"/>
<property name="currencyFlag" column="PAMCF" type="java.lang.String" length="1"/>
<property name="effectiveDate" column="PAEFDT" type="java.lang.String" length="10"/>
</class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():


Full stack trace of any exception that occurs:
9469 ERROR [main] net.sf.hibernate.impl.SessionImpl - Could not synchronize database state with session
net.sf.hibernate.HibernateException: SQL insert, update or delete failed (row not found)
at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:25)
at net.sf.hibernate.persister.EntityPersister.delete(EntityPersister.java:599)
at net.sf.hibernate.impl.ScheduledDeletion.execute(ScheduledDeletion.java:29)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2418)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2376)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2240)
at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)

Name and version of the database you are using:
DB2 for AS400
The generated SQL (show_sql=true):
Hibernate: select event0_.EVNUM as EVNUM, event0_.EVSRCI as EVSRCI, event0_.EVDTTM as EVDTTM, event0_.EVCDE as EVCDE, event0_.EVREF as EVREF, event0_.EVAPP as EVAPP, event0_.EVTYP as EVTYP, event0_.EVDESC as EVDESC, event0_.EVSYSRF as EVSYSRF, event0_.EVUMN as EVUMN, event0_.EVCTY as EVCTY, event0_.EVRGN as EVRGN, event0_.EVBSYSN as EVBSYSN, event0_.EVCSYSN as EVCSYSN, event0_.EVSTS as EVSTS from NXMLEVP event0_
Hibernate: select exchangera0_.EXNUM as EXNUM, exchangera0_.EXTYP as EXTYP, exchangera0_.EXEXR as EXEXR, exchangera0_.EXDCCY as EXDCCY, exchangera0_.EXNCCY as EXNCCY, exchangera0_.EXPRCB as EXPRCB from NXMLEXP exchangera0_
Hibernate: select payment0_.PANUM as PANUM, payment0_.PATYP as PATYP, payment0_.PADIR as PADIR, payment0_.PASEQ as PASEQ, payment0_.PAPAYR as PAPAYR, payment0_.PARCVR as PARCVR, payment0_.PACCY as PACCY, payment0_.PAAMT as PAAMT, payment0_.PACDE as PACDE, payment0_.PAMCF as PAMCF, payment0_.PAEFDT as PAEFDT from NXMLPAP payment0_
Hibernate: select productdet0_.PRNUM as PRNUM, productdet0_.PRORRF as PRORRF, productdet0_.PRCNTR as PRCNTR, productdet0_.PRBRNM as PRBRNM, productdet0_.PRPTYP as PRPTYP, productdet0_.PRPCDE as PRPCDE, productdet0_.PRDLP as PRDLP, productdet0_.PRDAST as PRDAST, productdet0_.PRCTDT as PRCTDT, productdet0_.PRSTDT as PRSTDT, productdet0_.PRSMDT as PRSMDT, productdet0_.PRDLTP as PRDLTP, productdet0_.PRDDESC as PRDDESC, productdet0_.PRDLR as PRDLR, productdet0_.PRSDESC as PRSDESC, productdet0_.PRCADJ as PRCADJ, productdet0_.PRCCY as PRCCY, productdet0_.PRBALC as PRBALC, productdet0_.PRORDLR as PRORDLR, productdet0_.PRORAMT as PRORAMT, productdet0_.PRMLTI as PRMLTI, productdet0_.PRACO as PRACO, productdet0_.PRBCPNC as PRBCPNC, productdet0_.PRTDT as PRTDT, productdet0_.PRDIC as PRDIC, productdet0_.PRPFM as PRPFM from NXMLPRP productdet0_
Hibernate: select settlement0_.SSNUM as SSNUM, settlement0_.SSTYP as SSTYP, settlement0_.SSDIR as SSDIR, settlement0_.SSACCID as SSACCID, settlement0_.SSAN as SSAN, settlement0_.SSATP as SSATP, settlement0_.SSSHN as SSSHN, settlement0_.SSBIC as SSBIC, settlement0_.SSBKN as SSBKN, settlement0_.SSMNE as SSMNE, settlement0_.SSTM as SSTM, settlement0_.SSCCY as SSCCY, settlement0_.SSACDTL as SSACDTL from NXMLSSP settlement0_
Hibernate: select settlement0_.STNUM as STNUM, settlement0_.STTYP as STTYP, settlement0_.STPYDTL as STPYDTL, settlement0_.STCHDTL as STCHDTL, settlement0_.STSRDTL as STSRDTL from NXMLSTP settlement0_
Hibernate: delete from NXMLEVP where EVNUM=?
Hibernate: delete from NXMLEXP where EXNUM=? and EXTYP=?
Hibernate: delete from NXMLEXP where EXNUM=? and EXTYP=?
Hibernate: delete from NXMLPAP where PANUM=? and PATYP=? and PADIR=? and PASEQ=?
Hibernate: delete from NXMLPAP where PANUM=? and PATYP=? and PADIR=? and PASEQ=?
Hibernate: delete from NXMLPAP where PANUM=? and PATYP=? and PADIR=? and PASEQ=?

As I think about it, there is a complicating factor that might be causing this problem.

The database is not journalled. This means transactions will not work and I have been forced to set the property hibernate.connection.isolation to zero.

I hope someone can confirm/deny that this is the cause.

Jem[/list]