How about this. I don't have time to look at it right away, but I'd
really want to get to the core of the problem. Would it be possible
for you to open a bug report with a stripped down DataMap file that
only contains the entities involved. I'll try it on SQLServer.
http://objectstyle.org/cayenne/bugs-features.html
Thanks,
Andrus
On Apr 28, 2006, at 5:47 PM, Bill Fan wrote:
Thanks Andrus. And yes, all columns in ProductRelation are PK's and
FK's.
The log message was a successful one as I need to show the DELETE
statement
appears after the INSERT statement.
The below is an example with error. I tried to add a new
ProductRelation
record to a product which already has one in the ProductRelation
table.
dao.ProductDaoImpl.saveProductRelation(ProductDaoImpl.java:714) -
<product.getProductRelationArray().size()=1>
dao.ProductDaoImpl.saveProductRelation(ProductDaoImpl.java:721) -
<relatedProductId[0]=1>
dao.ProductDaoImpl.saveProductRelation(ProductDaoImpl.java:721) -
<relatedProductId[1]=220>
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: 1, 220, 16]>
cayenne.access.QueryLogger.logUpdateCount(QueryLogger.java:404) - <===
updated 1 row.>
cayenne.access.QueryLogger.logQueryParameters(QueryLogger.java:358) -
<[bind: 1, 1, 16]>
cayenne.access.QueryLogger.logQueryError(QueryLogger.java:439) - <***
error.>
on: Violation of PRIMARY KEY constraint 'PK_ProductRelation'.
Cannot insert
duplicate key in object 'dbo.ProductRelation'.
-----Original Message-----
From: Andrus Adamchik [mailto:[EMAIL PROTECTED]
Sent: Friday, 28 April 2006 11:05 PM
To: [email protected]
Subject: Re: How to enforce Delete before Insert?
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
