You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Bill Fan <bi...@fanscom.com.au> on 2006/04/27 15:00:45 UTC
How to enforce Delete before Insert?
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
Re: How to enforce Delete before Insert?
Posted by Juergen Saar <ju...@jsaar.org>.
Just one more comment ...
I can reproduce the same behavior with Infomix,
so it seems no SQLServer specific problem.
2006/4/28, Andrus Adamchik <an...@objectstyle.org>:
>
> 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
>
>
RE: How to enforce Delete before Insert?
Posted by Bill Fan <bi...@fanscom.com.au>.
No problem. Thank you for your support!
Cheers,
Bill
-----Original Message-----
From: Andrus Adamchik [mailto:andrus@objectstyle.org]
Sent: Saturday, 29 April 2006 7:51 AM
To: cayenne-user@incubator.apache.org
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:andrus@objectstyle.org]
> Sent: Friday, 28 April 2006 11:05 PM
> To: cayenne-user@incubator.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
>>
>>
>>
>
>
>
>
Re: How to enforce Delete before Insert?
Posted by Andrus Adamchik <an...@objectstyle.org>.
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:andrus@objectstyle.org]
> Sent: Friday, 28 April 2006 11:05 PM
> To: cayenne-user@incubator.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
>>
>>
>>
>
>
>
>
RE: How to enforce Delete before Insert?
Posted by Bill Fan <bi...@fanscom.com.au>.
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:andrus@objectstyle.org]
Sent: Friday, 28 April 2006 11:05 PM
To: cayenne-user@incubator.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
>
>
>
Re: How to enforce Delete before Insert?
Posted by Andrus Adamchik <an...@objectstyle.org>.
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
>
>
>