2 Replies Latest reply on Feb 15, 2005 11:20 AM by Terry Paterson

    Cascading Updates not working properly ?

    Terry Paterson Newbie

      (using JBoss 3.2.4 & 3.2.5, CMP, CMT, and Optimistic Locking using a lockVersion column)

      We have noticed that if we have two tables - lets call them cartitem and item, and we have a fkey reference from cartitem (cartitem.item_id) into item (item.item_id) and CartItem has a CMR method getItem(), then when we remove the Item entity -- the fkey reference in CartItem is set to null.

      which makes perfect sense as it no longer exists in the item table.

      however -- we now have a similar situation, where we have a table named pendinginventory which has an inventory_id column which is a fkey into a table named inventory (pk = inventory_id), and PendingInventory has a getInventory() CMR method.

      my session bean attempts to remove the inventory entity, like so :

      inventoryEntity.remove()


      but it does'nt work, and gives an exception :

      javax.ejb.EJBException: Update failed. Expected one affected row: rowsAffected=0, id=722
      


      after turning up the org.jboss.plugins.cmp debugging in log4j.xml I can see it's trying to do the following update :

      15:35:39,734 DEBUG [PendingInventory] Executing SQL: UPDATE pendinginventory SET inventory_id=?, lockversion=? WHERE pendinginventory_id=? AND lockversion IS NULL


      however -- the lockversion column for this entity is not null (I can see in the DB that it's non null - and there is a not-null constraint on that column) !! so the SQL is wrong !

      in order to double check this I added some code to the session bean - to lookup the pendingInventory entity immediately before trying to delete the inventory entity, and display the lockVersion


      // start of hack
       // DON'T REMOVE!
       // this code seems to make deletion work -- not sure why !
       Collection c = PendingInventoryUtil.getLocalHome().findByInventoryId(invEntity.getInventoryId());
       if(c.size() > 0) {
       PendingInventoryLocal pendInvLocal = (PendingInventoryLocal) c.iterator().next();
       logger.debug("PID and LOCKVERSION: " + pendInvLocal.getPrimaryKey() + "::" + pendInvLocal.getLockVersion());
       }
       // end of hack
      
       if(logger.isDebugEnabled()) logger.debug("");
       invEntity.remove();
       if(logger.isDebugEnabled()) logger.debug("removed item!");
      


      after adding the additional code to lookup the pendingInventory details before deleting the inventory entity -- the code works, and the SQL is as follows :

      16:50:44,475 DEBUG [YardManagerSession] PID and LOCKVERSION: 724::2
      16:50:44,604 DEBUG [PendingInventory] Executing SQL: UPDATE pendinginventory SET inventory_id=?, lockversion=? WHERE pendinginventory_id=? AND lockversion=?
      16:50:44,607 DEBUG [Inventory] Executing SQL: DELETE FROM inventory WHERE inventory_id=?
      16:50:44,623 DEBUG [YardManagerSession] removed item!
      


      if I comment out the "hack" -- it does'nt work again -- and I get the same mis-constructed SQL again ! (I've run each version over 100 times and the results are 100% consistent)


      I can't seem to figure this one out and seems like a bug ?,
      like because the PendingInventory details are'nt already loaded it gets confused ?

      anyone got any ideas ?