Could not synchronize database state with session

shinoyvv2002 · **Joined:** Mon Nov 06, 2006 9:46 am **Posts:** 10

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

3.1.2

hi,

when i tried this code to delete records from database, this code throws Exceptions as below.

O Hibernate: delete from GDMW.CUSTOMER_CONTACT where CUST_CONT_SKEY=?
[12/20/06 17:05:56:328 IST] 00000030 JDBCException W SQL Error: -532, SQLState: 23001
[12/20/06 17:05:56:328 IST] 00000030 JDBCException E DB2 SQL error: SQLCODE: -532, SQLSTATE: 23001, SQLERRMC: GDMW.CUST_CONT_CMNT.FK_CCNTCMT_CCNT
[12/20/06 17:05:56:343 IST] 00000030 AbstractFlush E Could not synchronize database state with session[12/20/06 17:05:56:406 IST] 00000030 AbstractFlush E TRAS0014I: The following exception was logged org.hibernate.exception.ConstraintViolationException: could not delete: [fit.gdms.schemadao.CustomerContact#4831]

Actually it is working ramdomly.I mean it worked earlier but now it is not working.if we force DB2 then it will work for some time .after that it will again perform like this . i am giving the code below.

private void deleteContact(CustomerContactDeleteForm customerContactDeleteForm,GDMSUserDTO roleUser)
{

Timestamp CurrentTS = new Timestamp(System.currentTimeMillis());
SessionFactory sf = null;
Session hibSession = null;
StringBuffer cusQuery=new StringBuffer();
StringBuffer cusQueryCmnt=new StringBuffer();
StringBuffer cusQueryChgLog=new StringBuffer();
StringBuffer cusQueryContDoc=new StringBuffer();
Long custContSkey=customerContactDeleteForm.getCustContSkey();
hibSession = HibernateUtil.getSessionFactory().openSession();
Transaction tx = hibSession.beginTransaction();

try {

List custContCmntList = new ArrayList();
System.out.println("here custContact2");
cusQueryCmnt.append("from fit.gdms.schemadao.CustContCmnt cmnt ");
cusQueryCmnt.append("where cmnt.customerContact.custContSkey ="+custContSkey);
custContCmntList = hibSession.createQuery(new String(cusQueryCmnt)).list();
System.out.println("here custContCmnt000"+custContCmntList);
if (custContCmntList.size()> 0)
{
CustContCmnt custContCmnt = (CustContCmnt)custContCmntList.get(0);
hibSession.delete(custContCmnt);
System.out.println("here custContCmnt");
}

List custCntChgLogList = new ArrayList();
cusQueryChgLog.append("from fit.gdms.schemadao.CustCntChgLog cccl ");
cusQueryChgLog.append("where cccl.customerContact.custContSkey ="+custContSkey);//:custContSkey");
custCntChgLogList = hibSession.createQuery(new String(cusQueryChgLog)).list();
System.out.println("here custCntChgLog0");
if (custCntChgLogList.size()> 0)
{
CustCntChgLog custCntChgLog = (CustCntChgLog)custCntChgLogList.get(0);
hibSession.delete(custCntChgLog);
System.out.println("here custCntChgLog");
}
List custContDocList = new ArrayList();
cusQueryContDoc.append("from fit.gdms.schemadao.CustContDoc ccd ");
cusQueryContDoc.append("where ccd.customerContact.custContSkey ="+custContSkey);//:custContSkey");
custContDocList = hibSession.createQuery(new String(cusQueryContDoc)).list();
if (custContDocList.size()> 0)
{

for(int i=0;i<custContDocList.size();i++)
{
CustContDoc custContDoc = (CustContDoc)custContDocList.get(i);
hibSession.delete(custContDoc);
hibSession.flush();
}

}

List custCont = new ArrayList();
cusQuery.append("from fit.gdms.schemadao.CustomerContact con ");
cusQuery.append("where con.custContSkey ="+custContSkey); //:custContSkey
custCont = hibSession.createQuery(new String(cusQuery)).list();

if (custCont.size()> 0)
{
CustomerContact custContact = (CustomerContact)custCont.get(0);
//hibSession.flush();
hibSession.delete(custContact);
}

CustomerComment custCmnt =new CustomerComment();
Customer customer=new Customer();
customer.setCustSkey(customerContactDeleteForm.getCustSkey());
custCmnt.setCustomer(customer);
custCmnt.setCmtLogText(customerContactDeleteForm.getCmtLogText());
custCmnt.setLastUpdId(roleUser.getUserCNUM());
custCmnt.setLastUpdTs(CurrentTS);
//hibSession.flush();
hibSession.save(custCmnt);
ChangeLogInsertion.log("CUSTOMER",customerContactDeleteForm.getCustSkey(),"Contact Deleted",customerContactDeleteForm.getCustContName(),"",roleUser.getUserCNUM(),hibSession);

tx.commit();
//hibSession.close();
}catch (HibernateException e) {

tx.rollback();
e.getStackTrace();
}catch (Exception e) {
e.printStackTrace();

} finally {

hibSession.close();
}
return;

}

database DB2 8.1

I am expecting your reply soon ....(I don't know is it DB2 problem or Hibernate problem?)

shinoy.v.v.

lester · **Posted:** Wed Dec 20, 2006 8:38 am

You have the constraint GDMW.CUST_CONT_CMNT.FK_CCNTCMT_CCNT (defined as on delete restrict) which restrict deletion of GDMW.CUSTOMER_CONTACT object (record in terms of SQL), because there're obects (records in terms of SQL) that refers to it. One way to resolve this problem is to define this constraint as on delete cascade if it accords your business rules, or if this association mapped in Hibernate, let Hibernate automatically delete all related records with cascade="delete" attribute in your mapping.

Look what DB2 documentation says:

Quote:

SQL0532N A parent row cannot be deleted because the relationship constraint-name restricts the deletion.

Explanation: An operation attempted to delete a specified row of the parent table but the parent key in the specified row has dependent rows in
the referential constraint constraint-name and the delete rule of NO ACTION or RESTRICT is specified for the relationship.

User Response: Examine the delete rule for all descendent tables to determine and correct the problem. The specific tables involved can be
determined from the relationship constraint-name.

sqlcode: -532
sqlstate: 23001, 23504