No problem. Thank you for your support!
Cheers, Bill -----Original Message----- From: Andrus Adamchik [mailto:[EMAIL PROTECTED] Sent: Saturday, 29 April 2006 7:51 AM To: [email protected] Subject: Re: How to enforce Delete before Insert? 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 >> >> >> > > > >
