Ho is the PK defined in ProductRelation? Looks like all three columns
are a part of PK. If so why would the error be thrown - the rows you
are deleting and inserting are not the same. I wonder if that's a
SQLServer thing? Could you post the error.
Andrus
On Apr 27, 2006, at 9:00 AM, Bill Fan wrote:
Hi,
I have a Product & a ProductRelation tables. When the ProductRelation
records for a product are managed in a web page, I'd like to delete
the
exiting records in the ProductRelation table for this product
first, then
insert the new one.
I'm having trouble to do this with the following code. I got the
"Cannot
insert duplicate key in object ..." db error. The reason appears
that the
DELET statements appear after the INSERT statements from what I can
see in
the QueryLogger output. Could someone please help? I'm using
Cayenne 1.2B2.
The code:
=========
public void saveProductRelation(Product product, String
relatedProductId[], Type relationType)
{
// delete the existing records
log.debug("product.getProductRelationArray().size()=" +
product.getProductRelationArray().size());
threadDataContext().deleteObjects(product.getProductRelationArray());
if (relatedProductId.length > 0)
{
for (int i=0; i<relatedProductId.length; i++)
{
log.debug("relatedProductId[" + i + "]=" +
relatedProductId[i]);
ProductRelation o = new ProductRelation();
threadDataContext().registerNewObject(o);
o.setToProduct(product);
o.setToProduct1((Product) objectForPK(Product.class, new
Integer(relatedProductId[i].trim())));
o.setToType(relationType);
product.addToProductRelationArray(o);
}
}
commitChanges();
}
The QueryLogger output:
=======================
dao.ProductDaoImpl.saveProductRelation(ProductDaoImpl.java:713) -
<relatedProductId[0]=9>
cayenne.access.QueryLogger.logQueryStart(QueryLogger.java:459) -
<--- will
run 2 queries.>
cayenne.access.QueryLogger.logBeginTransaction(QueryLogger.java:
413) - <---
transaction started.>
cayenne.access.QueryLogger.logQuery(QueryLogger.java:336) - <INSERT
INTO
dbo.ProductRelation (ProductId, RelatedProductid, TypeId) VALUES
(?, ?, ?)>
cayenne.access.QueryLogger.logQueryParameters(QueryLogger.java:358) -
<[bind: 242, 9, 16]>
cayenne.access.QueryLogger.logUpdateCount(QueryLogger.java:404) - <===
updated 1 row.>
cayenne.access.QueryLogger.logQuery(QueryLogger.java:336) - <DELETE
FROM
dbo.ProductRelation WHERE ProductId = ? AND RelatedProductid = ?
AND TypeId
= ?>
cayenne.access.QueryLogger.logQueryParameters(QueryLogger.java:358) -
<[bind: 242, 222, 16]>
Many thanks in advace!
Bill
