You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@geronimo.apache.org by Milan Unger <mi...@siemens.com> on 2006/03/27 15:45:25 UTC
ejb finder method with "IS NULL"
Hallo,
I use CMR relation to build a tree of categories and implemented a
finder method with EJB QL:
<query>
<query-method>
<method-name>findRootCategories</method-name>
<method-params/>
</query-method>
<ejb-ql>SELECT OBJECT(o)
FROM Category o
WHERE o.parent IS NULL</ejb-ql>
</query>
My DB contains following data:
CATEGORYID TITLE PARENTID
3100 A1
3101 B1
3102 A2 3100
3200 A3 3102
I expect that the output will be collection of {A1, B1} objects, anyway,
it returns {B1,A3}. The CMR itself works fine, I have crosschecked it by
displaying parent name in web application and the data in DB are build
using it. See below excerpts from ejb-jar.xml and openejb-jar.xml.
Any idea what is going wrong?
Regards Milan.
-------------------- ejb-jar.xml ----------------------
<ejb-relation>
<description/>
<ejb-relation-name>Category-Parent</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>parent</ejb-relationship-role-name>
<multiplicity>One</multiplicity>
<relationship-role-source>
<ejb-name>CategoryBean</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>children</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>children</ejb-relationship-role-name>
<multiplicity>Many</multiplicity>
<relationship-role-source>
<ejb-name>CategoryBean</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>parent</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>
--------- openejb-jar.xml ---------------------
<ejb-relation>
<ejb-relationship-role>
<relationship-role-source>
<ejb-name>CategoryBean</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>parent</cmr-field-name>
</cmr-field>
<foreign-key-column-on-source/>
<role-mapping>
<cmr-field-mapping>
<key-column>categoryid</key-column>
<foreign-key-column>parentid</foreign-key-column>
</cmr-field-mapping>
</role-mapping>
</ejb-relationship-role>
<ejb-relationship-role>
<relationship-role-source>
<ejb-name>CategoryBean</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>children</cmr-field-name>
</cmr-field>
<role-mapping>
<cmr-field-mapping>
<key-column>categoryid</key-column>
<foreign-key-column>parentid</foreign-key-column>
</cmr-field-mapping>
</role-mapping>
</ejb-relationship-role>
</ejb-relation>
Re: ejb finder method with "IS NULL"
Posted by Gianny Damour <gi...@optusnet.com.au>.
Hi,
You can easily see all the SELECT statements executed under the cover of
a CMP by putting a breaking in
org.tranql.sql.jdbc.JDBCQueryCommand.execute. Hopefully, you will then
be able to back-track the problem.
It is quite annoying that there are no debug or trace logs available to
help in this debugging; will fix that.
Thanks,
Gianny
Milan Unger wrote:
>Hello,
>
>I assumed similar and have checked code and inspected db data whole
>time, but it is not changing. Originally, I assumed that this is due to
>wrong CMR declarations, but when using business method based on CMR
>fields (loop over all Category ejbs with getParent()==null) instead of
>the finder method it works fine (but not efficiently). The generated
>SELECT returns 3100 and 3101 on my side too.
>
>I can debug something in geronimo code but need to know where it makes a
>sense.
>
>Regards, Milan.
>
>Gianny Damour wrote / napísal(a):
>
>
>>Hello,
>>
>>This is very strange and I cannot explain it.
>>
>>Your query:
>>SELECT OBJECT(o) FROM Category o WHERE o.parent IS NULL
>>
>>is compiled into a SELECT statement like this:
>>SELECT o.categoryid FROM category_table o WHERE (NOT EXISTS (SELECT 0
>>FROM category_table T0 WHERE T0.categoryid = o.parentid))
>>
>>This SELECT definitively returns 3100 and 3101.
>>
>>Is it possible that the PARENTID column is updated somehow, e.g. when
>>the parent relationship is redefined, and that you are not aware of it?
>>
>>Thanks,
>>Gianny
>>
>>Milan Unger wrote:
>>
>>
>>
>>>Hallo,
>>>
>>>I use CMR relation to build a tree of categories and implemented a
>>>finder method with EJB QL:
>>>
>>> <query>
>>> <query-method>
>>> <method-name>findRootCategories</method-name>
>>> <method-params/>
>>> </query-method>
>>> <ejb-ql>SELECT OBJECT(o)
>>>FROM Category o
>>>WHERE o.parent IS NULL</ejb-ql>
>>> </query>
>>>
>>>
>>>My DB contains following data:
>>>
>>>CATEGORYID TITLE PARENTID
>>>3100 A1 3101 B1 3102 A2 3100
>>>3200 A3 3102
>>>
>>>
>>>I expect that the output will be collection of {A1, B1} objects, anyway,
>>>it returns {B1,A3}. The CMR itself works fine, I have crosschecked it by
>>>
>>>
>>>displaying parent name in web application and the data in DB are build
>>>
>>>
>>>using it. See below excerpts from ejb-jar.xml and openejb-jar.xml.
>>>
>>>Any idea what is going wrong?
>>>
>>>Regards Milan.
>>>
>>>-------------------- ejb-jar.xml ----------------------
>>><ejb-relation>
>>><description/>
>>><ejb-relation-name>Category-Parent</ejb-relation-name>
>>><ejb-relationship-role>
>>>
>>><ejb-relationship-role-name>parent</ejb-relationship-role-name>
>>><multiplicity>One</multiplicity>
>>><relationship-role-source>
>>><ejb-name>CategoryBean</ejb-name>
>>></relationship-role-source>
>>><cmr-field>
>>><cmr-field-name>children</cmr-field-name>
>>><cmr-field-type>java.util.Collection</cmr-field-type>
>>></cmr-field>
>>></ejb-relationship-role>
>>><ejb-relationship-role>
>>>
>>><ejb-relationship-role-name>children</ejb-relationship-role-name>
>>><multiplicity>Many</multiplicity>
>>><relationship-role-source>
>>><ejb-name>CategoryBean</ejb-name>
>>></relationship-role-source>
>>><cmr-field>
>>><cmr-field-name>parent</cmr-field-name>
>>></cmr-field>
>>></ejb-relationship-role>
>>></ejb-relation>
>>>
>>>--------- openejb-jar.xml ---------------------
>>>
>>><ejb-relation>
>>><ejb-relationship-role>
>>><relationship-role-source>
>>><ejb-name>CategoryBean</ejb-name>
>>></relationship-role-source>
>>><cmr-field>
>>><cmr-field-name>parent</cmr-field-name>
>>></cmr-field>
>>><foreign-key-column-on-source/>
>>><role-mapping>
>>><cmr-field-mapping>
>>><key-column>categoryid</key-column>
>>>
>>><foreign-key-column>parentid</foreign-key-column>
>>></cmr-field-mapping>
>>></role-mapping>
>>></ejb-relationship-role>
>>><ejb-relationship-role>
>>><relationship-role-source>
>>><ejb-name>CategoryBean</ejb-name>
>>></relationship-role-source>
>>><cmr-field>
>>><cmr-field-name>children</cmr-field-name>
>>></cmr-field>
>>><role-mapping>
>>><cmr-field-mapping>
>>><key-column>categoryid</key-column>
>>>
>>><foreign-key-column>parentid</foreign-key-column>
>>></cmr-field-mapping>
>>></role-mapping>
>>></ejb-relationship-role>
>>></ejb-relation>
>>>
>>>
>>>
>>>
>>
>>
>>
>
>
>
>
>
Re: ejb finder method with "IS NULL"
Posted by Milan Unger <mi...@siemens.com>.
Hello,
I assumed similar and have checked code and inspected db data whole
time, but it is not changing. Originally, I assumed that this is due to
wrong CMR declarations, but when using business method based on CMR
fields (loop over all Category ejbs with getParent()==null) instead of
the finder method it works fine (but not efficiently). The generated
SELECT returns 3100 and 3101 on my side too.
I can debug something in geronimo code but need to know where it makes a
sense.
Regards, Milan.
Gianny Damour wrote / napísal(a):
> Hello,
>
> This is very strange and I cannot explain it.
>
> Your query:
> SELECT OBJECT(o) FROM Category o WHERE o.parent IS NULL
>
> is compiled into a SELECT statement like this:
> SELECT o.categoryid FROM category_table o WHERE (NOT EXISTS (SELECT 0
> FROM category_table T0 WHERE T0.categoryid = o.parentid))
>
> This SELECT definitively returns 3100 and 3101.
>
> Is it possible that the PARENTID column is updated somehow, e.g. when
> the parent relationship is redefined, and that you are not aware of it?
>
> Thanks,
> Gianny
>
> Milan Unger wrote:
>
>> Hallo,
>>
>> I use CMR relation to build a tree of categories and implemented a
>> finder method with EJB QL:
>>
>> <query>
>> <query-method>
>> <method-name>findRootCategories</method-name>
>> <method-params/>
>> </query-method>
>> <ejb-ql>SELECT OBJECT(o)
>> FROM Category o
>> WHERE o.parent IS NULL</ejb-ql>
>> </query>
>>
>>
>> My DB contains following data:
>>
>> CATEGORYID TITLE PARENTID
>> 3100 A1 3101 B1 3102 A2 3100
>> 3200 A3 3102
>>
>>
>> I expect that the output will be collection of {A1, B1} objects, anyway,
>> it returns {B1,A3}. The CMR itself works fine, I have crosschecked it by
>>
>>
>> displaying parent name in web application and the data in DB are build
>>
>>
>> using it. See below excerpts from ejb-jar.xml and openejb-jar.xml.
>>
>> Any idea what is going wrong?
>>
>> Regards Milan.
>>
>> -------------------- ejb-jar.xml ----------------------
>> <ejb-relation>
>> <description/>
>> <ejb-relation-name>Category-Parent</ejb-relation-name>
>> <ejb-relationship-role>
>>
>> <ejb-relationship-role-name>parent</ejb-relationship-role-name>
>> <multiplicity>One</multiplicity>
>> <relationship-role-source>
>> <ejb-name>CategoryBean</ejb-name>
>> </relationship-role-source>
>> <cmr-field>
>> <cmr-field-name>children</cmr-field-name>
>> <cmr-field-type>java.util.Collection</cmr-field-type>
>> </cmr-field>
>> </ejb-relationship-role>
>> <ejb-relationship-role>
>>
>> <ejb-relationship-role-name>children</ejb-relationship-role-name>
>> <multiplicity>Many</multiplicity>
>> <relationship-role-source>
>> <ejb-name>CategoryBean</ejb-name>
>> </relationship-role-source>
>> <cmr-field>
>> <cmr-field-name>parent</cmr-field-name>
>> </cmr-field>
>> </ejb-relationship-role>
>> </ejb-relation>
>>
>> --------- openejb-jar.xml ---------------------
>>
>> <ejb-relation>
>> <ejb-relationship-role>
>> <relationship-role-source>
>> <ejb-name>CategoryBean</ejb-name>
>> </relationship-role-source>
>> <cmr-field>
>> <cmr-field-name>parent</cmr-field-name>
>> </cmr-field>
>> <foreign-key-column-on-source/>
>> <role-mapping>
>> <cmr-field-mapping>
>> <key-column>categoryid</key-column>
>>
>> <foreign-key-column>parentid</foreign-key-column>
>> </cmr-field-mapping>
>> </role-mapping>
>> </ejb-relationship-role>
>> <ejb-relationship-role>
>> <relationship-role-source>
>> <ejb-name>CategoryBean</ejb-name>
>> </relationship-role-source>
>> <cmr-field>
>> <cmr-field-name>children</cmr-field-name>
>> </cmr-field>
>> <role-mapping>
>> <cmr-field-mapping>
>> <key-column>categoryid</key-column>
>>
>> <foreign-key-column>parentid</foreign-key-column>
>> </cmr-field-mapping>
>> </role-mapping>
>> </ejb-relationship-role>
>> </ejb-relation>
>>
>>
>
>
>
Re: ejb finder method with "IS NULL"
Posted by Gianny Damour <gi...@optusnet.com.au>.
Hello,
This is very strange and I cannot explain it.
Your query:
SELECT OBJECT(o) FROM Category o WHERE o.parent IS NULL
is compiled into a SELECT statement like this:
SELECT o.categoryid FROM category_table o WHERE (NOT EXISTS (SELECT 0
FROM category_table T0 WHERE T0.categoryid = o.parentid))
This SELECT definitively returns 3100 and 3101.
Is it possible that the PARENTID column is updated somehow, e.g. when
the parent relationship is redefined, and that you are not aware of it?
Thanks,
Gianny
Milan Unger wrote:
>Hallo,
>
>I use CMR relation to build a tree of categories and implemented a
>finder method with EJB QL:
>
> <query>
> <query-method>
> <method-name>findRootCategories</method-name>
> <method-params/>
> </query-method>
> <ejb-ql>SELECT OBJECT(o)
>FROM Category o
>WHERE o.parent IS NULL</ejb-ql>
> </query>
>
>
>My DB contains following data:
>
>CATEGORYID TITLE PARENTID
>3100 A1
>3101 B1
>3102 A2 3100
>3200 A3 3102
>
>
>I expect that the output will be collection of {A1, B1} objects, anyway,
>it returns {B1,A3}. The CMR itself works fine, I have crosschecked it by
>
>
>displaying parent name in web application and the data in DB are build
>
>
> using it. See below excerpts from ejb-jar.xml and openejb-jar.xml.
>
> Any idea what is going wrong?
>
> Regards Milan.
>
> -------------------- ejb-jar.xml ----------------------
> <ejb-relation>
> <description/>
> <ejb-relation-name>Category-Parent</ejb-relation-name>
> <ejb-relationship-role>
>
> <ejb-relationship-role-name>parent</ejb-relationship-role-name>
> <multiplicity>One</multiplicity>
> <relationship-role-source>
> <ejb-name>CategoryBean</ejb-name>
> </relationship-role-source>
> <cmr-field>
> <cmr-field-name>children</cmr-field-name>
> <cmr-field-type>java.util.Collection</cmr-field-type>
> </cmr-field>
> </ejb-relationship-role>
> <ejb-relationship-role>
>
> <ejb-relationship-role-name>children</ejb-relationship-role-name>
> <multiplicity>Many</multiplicity>
> <relationship-role-source>
> <ejb-name>CategoryBean</ejb-name>
> </relationship-role-source>
> <cmr-field>
> <cmr-field-name>parent</cmr-field-name>
> </cmr-field>
> </ejb-relationship-role>
> </ejb-relation>
>
> --------- openejb-jar.xml ---------------------
>
> <ejb-relation>
> <ejb-relationship-role>
> <relationship-role-source>
> <ejb-name>CategoryBean</ejb-name>
> </relationship-role-source>
> <cmr-field>
> <cmr-field-name>parent</cmr-field-name>
> </cmr-field>
> <foreign-key-column-on-source/>
> <role-mapping>
> <cmr-field-mapping>
> <key-column>categoryid</key-column>
>
> <foreign-key-column>parentid</foreign-key-column>
> </cmr-field-mapping>
> </role-mapping>
> </ejb-relationship-role>
> <ejb-relationship-role>
> <relationship-role-source>
> <ejb-name>CategoryBean</ejb-name>
> </relationship-role-source>
> <cmr-field>
> <cmr-field-name>children</cmr-field-name>
> </cmr-field>
> <role-mapping>
> <cmr-field-mapping>
> <key-column>categoryid</key-column>
>
> <foreign-key-column>parentid</foreign-key-column>
> </cmr-field-mapping>
> </role-mapping>
> </ejb-relationship-role>
> </ejb-relation>
>
>