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:andru..bjectstyle.org]
Sent: Friday, 28 April 2006 11:05 PM
To: cayenne-use..ncubator.apache.org
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
>
>
>
This archive was generated by hypermail 2.0.0 : Fri Apr 28 2006 - 17:47:44 EDT