Cascading Updates not working properly ?
(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 ?
