You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Mike Kienenberger <mk...@gmail.com> on 2006/04/20 17:55:05 UTC
Creating Oracle constraints across schemas
I notice that the modeler is unable to create a constraint across a
schema, even when the two schemas are in the same database. Is this
to be expected? If so, should the modeler stop generating these kinds
of statements?
[SQL: ALTER TABLE ENG_WORK_MGMT.ENG_WORK ADD FOREIGN KEY (ENG_WORK_ID)
REFERENCES CORE_WORK_MGMT.STATUSABLE_WORK (ID)] - ORA-00942: table or
view does not exist
[SQL: ALTER TABLE ENG_WORK_MGMT.WOMS_WORK ADD FOREIGN KEY
(WOMS_WORK_ID) REFERENCES CORE_WORK_MGMT.STATUSABLE_WORK (ID)] -
ORA-00942: table or view does not exist
[SQL: ALTER TABLE ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT ADD FOREIGN KEY
(RIGHT_OF_WAY_SECROLE_ID) REFERENCES ADMINDB.SEC_ROLE (ID)] -
ORA-00942: table or view does not exist
[SQL: ALTER TABLE ENG_WORK_MGMT.AUTHORIZATION_DOC__WORK ADD FOREIGN
KEY (WORK_ID) REFERENCES CORE_WORK_MGMT.WORK (ID)] - ORA-00942: table
or view does not exist
RE: Can not recognize the object passed in for the toOne relationship?
Posted by Bill Fan <bi...@fanscom.com.au>.
Thanks Andrus!
I've just figured out the problem... It has nothing to do with Cayenne. I
messed up with where the price object should be created with Spring.
The problem was that I created the Price object list in the
UpdatePriceAction class within Struts by using
Price price = new Price();
Price.setPrice(o.getPrice());
...
list.add(price);
, without setting up the product target by using
price.setToProduct(product).
Thought it was just purely java bean to hold the information, no need to
worry about the data relationship yet. Then I passed the price object list
to the save(list, product) function in PriceDaoImpl class and tried to do
new Price() again as indicated in my previous message, plus setting up the
product target, then commitChanges...
Now, I made some changes to only create the Price object in the PriceDaoImpl
class, and it is working now.
Cheers,
Bill
-----Original Message-----
From: Andrus Adamchik [mailto:andrus@objectstyle.org]
Sent: Friday, 21 April 2006 4:36 PM
To: cayenne-user@incubator.apache.org
Subject: Re: Can not recognize the object passed in for the toOne
relationship?
Hi Bill,
Many-to-one relationships usually work without surprises. Can you
check a few more things right before commit:
Log.debug("prices: " + product.getPriceArray());
Log.debug("product: " + price.getToProduct());
Andrus
On Apr 21, 2006, at 8:41 AM, Bill Fan wrote:
> Hi,
>
> I'm trying to do something like the following and it seems the
> productId
> becomes null in the generated sql query.
>
> Price price = new Price();
> threadDataContext().registerNewObject(price);
> price.setToProduct(product);
> price.setPrice(o.getPrice());
> ...
>
> The query generated was as following:
> ------------------------------------
> INSERT INTO dbo.Price (Description, GST, IsUnitPrice, Price, PriceId,
> PriceTypeId, ProductId, Qty) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
> [bind: '1 box', 10, 'true', 100, 280, 1, NULL, 1]
>
>
> I'm using Tomcat 5.0.28 + Spring 1.2.7 + Cayenne 1.1.4 + Struts 1.2.9
>
> I can see and verify the productId value and the product name by
> placing the
> following logging statements before the product object is used,
> Log.debug("product Id: " + product.getProductId());
> Log.debug("product Name: " + product.getProductName());
>
>
>
>
> More details are as follow if it is required:
> =============================================
> In the web app, there are a Product table and a Price table. It is
> a "one to
> many" relation from Product to Price,
>
> The tables:
> -----------
> Product table:
> proudctId int PK
> productName varchar(100)
> ...
>
> Price table:
> priceId int PK,
> productId int FK (to Product.ProductId column)
> price double
> ...
>
> Cayenne data map:
> -----------------
> <db-relationship name="toProduct" source="Price" target="Product"
> toMany="false">
> <db-attribute-pair source="ProductId" target="ProductId"/>
> </db-relationship>
>
> <db-relationship name="priceArray" source="Product" target="Price"
> toMany="true">
> <db-attribute-pair source="ProductId" target="ProductId"/>
> </db-relationship>
>
>
> Many thanks in advance!
> Bill
Re: Can not recognize the object passed in for the toOne relationship?
Posted by Andrus Adamchik <an...@objectstyle.org>.
Hi Bill,
Many-to-one relationships usually work without surprises. Can you
check a few more things right before commit:
Log.debug("prices: " + product.getPriceArray());
Log.debug("product: " + price.getToProduct());
Andrus
On Apr 21, 2006, at 8:41 AM, Bill Fan wrote:
> Hi,
>
> I'm trying to do something like the following and it seems the
> productId
> becomes null in the generated sql query.
>
> Price price = new Price();
> threadDataContext().registerNewObject(price);
> price.setToProduct(product);
> price.setPrice(o.getPrice());
> ...
>
> The query generated was as following:
> ------------------------------------
> INSERT INTO dbo.Price (Description, GST, IsUnitPrice, Price, PriceId,
> PriceTypeId, ProductId, Qty) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
> [bind: '1 box', 10, 'true', 100, 280, 1, NULL, 1]
>
>
> I'm using Tomcat 5.0.28 + Spring 1.2.7 + Cayenne 1.1.4 + Struts 1.2.9
>
> I can see and verify the productId value and the product name by
> placing the
> following logging statements before the product object is used,
> Log.debug("product Id: " + product.getProductId());
> Log.debug("product Name: " + product.getProductName());
>
>
>
>
> More details are as follow if it is required:
> =============================================
> In the web app, there are a Product table and a Price table. It is
> a "one to
> many" relation from Product to Price,
>
> The tables:
> -----------
> Product table:
> proudctId int PK
> productName varchar(100)
> ...
>
> Price table:
> priceId int PK,
> productId int FK (to Product.ProductId column)
> price double
> ...
>
> Cayenne data map:
> -----------------
> <db-relationship name="toProduct" source="Price" target="Product"
> toMany="false">
> <db-attribute-pair source="ProductId" target="ProductId"/>
> </db-relationship>
>
> <db-relationship name="priceArray" source="Product" target="Price"
> toMany="true">
> <db-attribute-pair source="ProductId" target="ProductId"/>
> </db-relationship>
>
>
> Many thanks in advance!
> Bill
Can not recognize the object passed in for the toOne relationship?
Posted by Bill Fan <bi...@fanscom.com.au>.
Hi,
I'm trying to do something like the following and it seems the productId
becomes null in the generated sql query.
Price price = new Price();
threadDataContext().registerNewObject(price);
price.setToProduct(product);
price.setPrice(o.getPrice());
...
The query generated was as following:
------------------------------------
INSERT INTO dbo.Price (Description, GST, IsUnitPrice, Price, PriceId,
PriceTypeId, ProductId, Qty) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
[bind: '1 box', 10, 'true', 100, 280, 1, NULL, 1]
I'm using Tomcat 5.0.28 + Spring 1.2.7 + Cayenne 1.1.4 + Struts 1.2.9
I can see and verify the productId value and the product name by placing the
following logging statements before the product object is used,
Log.debug("product Id: " + product.getProductId());
Log.debug("product Name: " + product.getProductName());
More details are as follow if it is required:
=============================================
In the web app, there are a Product table and a Price table. It is a "one to
many" relation from Product to Price,
The tables:
-----------
Product table:
proudctId int PK
productName varchar(100)
...
Price table:
priceId int PK,
productId int FK (to Product.ProductId column)
price double
...
Cayenne data map:
-----------------
<db-relationship name="toProduct" source="Price" target="Product"
toMany="false">
<db-attribute-pair source="ProductId" target="ProductId"/>
</db-relationship>
<db-relationship name="priceArray" source="Product" target="Price"
toMany="true">
<db-attribute-pair source="ProductId" target="ProductId"/>
</db-relationship>
Many thanks in advance!
Bill
Re: Creating Oracle constraints across schemas
Posted by Mike Kienenberger <mk...@gmail.com>.
On 4/20/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> Per Chris's message, I was wrong in my assumption that a constraint
> across schemas is not possible. This means that current behavior is
> correct. My vote is to close CAY-522 issue and keep things as they
> are (do not add an extra modeler checkbox).
Agreed. Closed as Won't Fix w/Chris's message attached. Thanks, Chris!
Re: Creating Oracle constraints across schemas
Posted by Andrus Adamchik <an...@objectstyle.org>.
Hmm... as usual this becomes a question of how many various DB
options we should support.
Per Chris's message, I was wrong in my assumption that a constraint
across schemas is not possible. This means that current behavior is
correct. My vote is to close CAY-522 issue and keep things as they
are (do not add an extra modeler checkbox).
Andrus
On Apr 20, 2006, at 9:00 PM, Mike Kienenberger wrote:
> On 4/20/06, Chris Poulsen <ma...@nesluop.dk> wrote:
>> Cross schema constraints are available in oracle, however you need to
>> allow access to the object being referenced.
>>
>> Something like:
>> as user core_work_mgmt:
>> grant references on statusable_work to eng_work_mgmt
>>
>> before creating the constraint
>
> Interesting. That makes sense.
> I'll run it by our dev team and see if they want to do it.
>
> Thanks.
> It's still unclear what Cayenne's behavior should be, though.
> Maybe a "Should generate cross-schema constraints" preference item or
> method parameter?
>
Re: Creating Oracle constraints across schemas
Posted by Mike Kienenberger <mk...@gmail.com>.
On 4/20/06, Chris Poulsen <ma...@nesluop.dk> wrote:
> Cross schema constraints are available in oracle, however you need to
> allow access to the object being referenced.
>
> Something like:
> as user core_work_mgmt:
> grant references on statusable_work to eng_work_mgmt
>
> before creating the constraint
Interesting. That makes sense.
I'll run it by our dev team and see if they want to do it.
Thanks.
It's still unclear what Cayenne's behavior should be, though.
Maybe a "Should generate cross-schema constraints" preference item or
method parameter?
Re: Creating Oracle constraints across schemas
Posted by Chris Poulsen <ma...@nesluop.dk>.
Hope i understood this correctly....
Cross schema constraints are available in oracle, however you need to
allow access to the object being referenced.
Something like:
as user core_work_mgmt:
grant references on statusable_work to eng_work_mgmt
before creating the constraint
--
Chris
torsdag 20 april 2006 18:14 skrev Andrus Adamchik:
> On Apr 20, 2006, at 7:55 PM, Mike Kienenberger wrote:
> > I notice that the modeler is unable to create a constraint across a
> > schema, even when the two schemas are in the same database. Is
> > this to be expected?
>
> Yes. I don't think the RDBMS supports that (as the error below
> suggests).
>
> > If so, should the modeler stop generating these kinds
> > of statements?
> >
> > [SQL: ALTER TABLE ENG_WORK_MGMT.ENG_WORK ADD FOREIGN KEY
> > (ENG_WORK_ID) REFERENCES CORE_WORK_MGMT.STATUSABLE_WORK (ID)] -
> > ORA-00942: table or view does not exist
>
> Yeah, it should. There is currently a filter somewhere
> (DbGenerator??) that prevents FKs to be generated across DataNodes.
> Probably need to extend that to also check for different schemas in
> the same DataMap.
>
> Andrus
Re: Creating Oracle constraints across schemas
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Apr 20, 2006, at 7:55 PM, Mike Kienenberger wrote:
> I notice that the modeler is unable to create a constraint across a
> schema, even when the two schemas are in the same database. Is this
> to be expected?
Yes. I don't think the RDBMS supports that (as the error below
suggests).
> If so, should the modeler stop generating these kinds
> of statements?
>
> [SQL: ALTER TABLE ENG_WORK_MGMT.ENG_WORK ADD FOREIGN KEY (ENG_WORK_ID)
> REFERENCES CORE_WORK_MGMT.STATUSABLE_WORK (ID)] - ORA-00942: table or
> view does not exist
Yeah, it should. There is currently a filter somewhere
(DbGenerator??) that prevents FKs to be generated across DataNodes.
Probably need to extend that to also check for different schemas in
the same DataMap.
Andrus