Re: How to enforce Delete before Insert?

From: Andrus Adamchik (andru..bjectstyle.org)
Date: Fri Apr 28 2006 - 17:51:05 EDT

  • Next message: Bill Fan: "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: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:51:30 EDT