How to enforce Delete before Insert?

From: Bill Fan (bil..anscom.com.au)
Date: Thu Apr 27 2006 - 09:00:45 EDT

  • Next message: M_Smit..hl.com: "Marc Smith is out of the office."

    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 : Thu Apr 27 2006 - 09:01:23 EDT