Hi, I have a hunch - quite possibly misplaced - that this may have something to do the bug I reported in the thhread entitled. "Criteria.setClassPath - the saga continues"
Although you are using an ODMG query, when the query is parsed, it does generate a standard QueryByCriteria. QueryByCriteria was getting the wrong class descriptor when paths of > 1 sement were used. This would lead to the attribute name being passed to the query, as opposed to the column name. Jakob fixed this in CVS last night. This May Be A Red Herring. Cheers, Charles. > -----Original Message----- > From: Steve Clark [mailto:[EMAIL PROTECTED] > Sent: 03 March 2004 19:56 > To: OJB Users List > Subject: Re: Extent problem with ODMG > > > I'm still having this problem, so I'm going to try again. > > Using ODMG, RC5, Oracle 9i. > > Summary: > - Class A has a 1-to-1 relationship 'abs' to an abstract > superclass S > - Class S has concrete subclasses B and C > - Classes B and C share a common relationship 'com' to another > class D; this relationship is defined in the superclass S > - Class D has a property 'p' > - A, B, C, and D map to distinct tables > > I am trying to retrieve all A's which have a given value for 'p' in > the D associated with the related B or C (whichever one it is). So: > > select x from A where A.abs.com.p = ? > > A.abs has type S, the abstract superclass; A.abs.com has type D. This > means that the query needs to generate some sort of interesting join > to check for both possible paths to D (via B or C), knowing that > either B or C will have exactly one row satisfying the join > condition(s). In pseudocode: > > select x from A where > if A.abs instanceof B then ((B) A.abs).com.p = ? > else if A.abs instanceof C then ((C) A.abs).com.p = ? > > Should I expect this to work? The SQL query which is being generated > is not only incorrect but invalid: OJB does not rewrite 'p', and in > fact does not even mention D at all. I assume this has to do with > the fact that the repository doesn't record the presence of the > relationship 'com' in the abstract superclass, but only in the > subclasses. Queries starting from B or C and following the 'com' > relationship work fine. Am I out of luck, or is there some way I can > get a working query out of this? > > thanks for any insights, > Steve Clark > Technology Applications Team > Natural Resources Research Center/USGS > [EMAIL PROTECTED] > (970)226-9291 > > PS: Original message below. Name mappings: > > A = PartnerImpl > B = SubSiteImpl > C = TechAssistImpl > D = SiteImpl > S = AccomplishmentImpl > > abs = accomp > com = site > p = habTypeCode > > sc> I'm having a problem with extents in ODMG. OJB is generating > sc> incorrect (and, in fact, invalid) SQL for my OQL query. I'm > sc> using RC5. > > sc> My data model consists of Sites, which have collections of > sc> each of two kinds of Accomplishments (SubSites and > sc> TechAssists). An Accomplishment has a collection of Partners. > sc> In the reverse direction, each Partner is associated with > sc> exactly one Accomplishment (either a SubSite or a TechAssist), > sc> and an Accomplishment knows about its parent Site. My > sc> repository looks like this: > > sc> <!-- - - - - - - Site - - - - - - --> > > sc> <class-descriptor > sc> class="gov.doi.habits.dataobjects.SiteImpl" > sc> table="SITE_DETAIL" proxy="dynamic"> > > sc> <field-descriptor > sc> name="siteKey" column="SITE_KEY" jdbc-type="INTEGER" > sc> primarykey="true" autoincrement="true"/> > > sc> <field-descriptor > sc> name="habTypeCode" column="HAB_TYPE_CODE" > sc> jdbc-type="INTEGER" /> > > sc> <collection-descriptor > sc> name="subSites" > sc> > element-class-ref="gov.doi.habits.dataobjects.SubSiteImpl" > sc> > collection-class="org.apache.ojb.broker.util.collections.Manag > eableArrayList"> > sc> <inverse-foreignkey field-ref="siteKey" /> > sc> </collection-descriptor> > > sc> <collection-descriptor > sc> name="techAssists" > sc> > element-class-ref="gov.doi.habits.dataobjects.TechAssistImpl" > sc> > collection-class="org.apache.ojb.broker.util.collections.Manag > eableArrayList"> > sc> <inverse-foreignkey field-ref="siteKey" /> > sc> </collection-descriptor> > sc> </class-descriptor> > > sc> <!-- - - - - - - Accomplishment - - - - - - --> > > sc> <class-descriptor > sc> class="gov.doi.habits.dataobjects.AccomplishmentImpl" > > > sc> <extent-class > sc> class-ref="gov.doi.habits.dataobjects.SubSiteImpl" /> > sc> <extent-class > sc> class-ref="gov.doi.habits.dataobjects.AssistImpl" /> > sc> </class-descriptor> > > sc> <!-- - - - - - - SubSite - - - - - - --> > > sc> <class-descriptor > sc> class="gov.doi.habits.dataobjects.SubSiteImpl" > sc> table="SUB_SITE_DETAIL" proxy="dynamic"> > > sc> <field-descriptor > sc> name="accompKey" column="ACCOMP_KEY" jdbc-type="INTEGER" > sc> primarykey="true" autoincrement="true" > sc> sequence-name="SEQ_ACCOMP_DETAIL" /> > > sc> <field-descriptor > sc> name="siteKey" column="SITE_KEY" jdbc-type="INTEGER" /> > > sc> <reference-descriptor > sc> name="site" > sc> class-ref="gov.doi.habits.dataobjects.SiteImpl"> > sc> <foreignkey field-ref="siteKey" /> > sc> </reference-descriptor> > > sc> <collection-descriptor > sc> name="partners" > sc> > element-class-ref="gov.doi.habits.dataobjects.PartnerImpl" > sc> > collection-class="org.apache.ojb.broker.util.collections.Manag > eableArrayList"> > sc> <inverse-foreignkey field-ref="accompKey" /> > sc> </collection-descriptor> > sc> </class-descriptor> > > sc> <!-- - - - - - - TechAssist - - - - - - --> > > sc> <class-descriptor > sc> class="gov.doi.habits.dataobjects.TechAssistImpl" > sc> table="ASSIST_DETAIL" proxy="dynamic"> > > sc> <field-descriptor > sc> name="accompKey" column="ACCOMP_KEY" jdbc-type="INTEGER" > sc> primarykey="true" autoincrement="true" > sc> sequence-name="SEQ_ACCOMP_DETAIL" /> > > sc> <field-descriptor > sc> name="siteKey" column="SITE_KEY" jdbc-type="INTEGER" /> > > sc> <reference-descriptor > sc> name="site" > sc> class-ref="gov.doi.habits.dataobjects.SiteImpl"> > sc> <foreignkey field-ref="siteKey" /> > sc> </reference-descriptor> > > sc> <collection-descriptor > sc> name="partners" > sc> > element-class-ref="gov.doi.habits.dataobjects.PartnerImpl" > sc> > collection-class="org.apache.ojb.broker.util.collections.Manag > eableArrayList"> > sc> <inverse-foreignkey field-ref="accompKey" /> > sc> </collection-descriptor> > sc> </class-descriptor> > > sc> <!-- - - - - - - Partner - - - - - - --> > > sc> <class-descriptor > sc> class="gov.doi.habits.dataobjects.PartnerImpl" > sc> table="PARTNER_DETAIL" proxy="dynamic"> > > sc> <field-descriptor > sc> name="partnerKey" column="PARTNER_KEY" > sc> jdbc-type="INTEGER" primarykey="true" > sc> autoincrement="true"/> > > sc> <field-descriptor > sc> name="accompKey" column="ACCOMP_KEY" > sc> jdbc-type="INTEGER"/> > > sc> <reference-descriptor > sc> name="accomp" > sc> > class-ref="gov.doi.habits.dataobjects.AccomplishmentImpl"> > sc> <foreignkey field-ref="accompKey"/> > sc> </reference-descriptor> > sc> </class-descriptor> > > sc> My query looks like this: > > sc> 15:41:13,896 DEBUG [] accesslayer.JdbcAccessImpl > sc> (JdbcAccessImpl.java:282) - executeQuery : Query from class > sc> gov.doi.habits.dataobjects.PartnerImpl where > sc> [accomp.site.habTypeCode IN [1]] > > sc> Note that partner.accomp is an Accomplishment (the abstract > sc> superclass); both extents (SubSite and TechAssist) have a site > sc> relationship. > > sc> The generated SQL looks like this: > > sc> 15:41:13,901 DEBUG [] sql.SqlGeneratorDefaultImpl > sc> (SqlGeneratorDefaultImpl.java:200) - SQL:SELECT DISTINCT > sc> A0.ACCOMP_KEY,A0.PARTNER_KEY FROM PARTNER_DETAIL > sc> A0,SUB_SITE_DETAIL A1,ASSIST_DETAIL A1E1 WHERE > sc> A0.ACCOMP_KEY=A1.ACCOMP_KEY(+) AND > sc> A0.ACCOMP_KEY=A1E1.ACCOMP_KEY(+) AND ((habTypeCode IN ( ? ) > sc> OR habTypeCode IN ( ? ))) > > sc> Note that SITE_DETAIL is not even included in the query, and > sc> habTypeCode as a result is not rewritten to the appropriate > sc> column name. > > sc> I think the correct query would be more like this: > > sc> SELECT DISTINCT A0.ACCOMP_KEY,A0.PARTNER_KEY > sc> FROM PARTNER_DETAIL A0,SUB_SITE_DETAIL A1,ASSIST_DETAIL > sc> A1E1,SITE_DETAIL A2 WHERE ((A0.ACCOMP_KEY=A1.ACCOMP_KEY(+) > sc> AND A1.SITE_KEY=A2.SITE_KEY) OR > sc> (A0.ACCOMP_KEY=A1E1.ACCOMP_KEY(+) AND > sc> A1E1.SITE_KEY=A2.SITE_KEY)) AND > sc> (A2.HAB_TYPE_CODE IN ( ? )) > > sc> ... though I'm not sure that's exactly right even. > > sc> Is there a way to get working SQL out of this OQL? > > sc> thanks, -- Steve Clark Technology Applications Team Natural > sc> Resources Research Center/USGS [EMAIL PROTECTED] > sc> (970)226-9291 > > sc> > --------------------------------------------------------------------- > sc> To unsubscribe, e-mail: [EMAIL PROTECTED] For > sc> additional commands, e-mail: [EMAIL PROTECTED] > > sc> -- > Steve Clark > Technology Applications Team > Natural Resources Research Center/USGS > [EMAIL PROTECTED] > (970)226-9291 > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > ___________________________________________________________ HPD Software Ltd. - Helping Business Finance Business Email terms and conditions: www.hpdsoftware.com/disclaimer --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
