4 Replies Latest reply on Nov 3, 2004 8:24 PM by Joan Horta Tosas

    wierd sql generated by container for cmr-field (1-*)

    Richard Doust Newbie

      I'd appreciate it if anyone could look at this for a minute and tell me if they see any reason why the Region.getZones() method (container generated) should throw an exception due to malformed SQL which looks like the following:

      2004-11-01 16:27:25,615 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.Region] Executing SQL: SELECT REGIONCODE, FROM administrator.REGION_ZONES WHERE (REGIONCODE=?) OR (REGIONCODE=?) OR (REGIONCODE=?)
      2004-11-01 16:27:25,822 ERROR [org.jboss.ejb.plugins.LogInterceptor] TransactionRolledbackLocalException in method: public abstract java.util.Set com.nym.entity.region.RegionLocal.getZones() throws javax.ejb.EJBException, causedBy:
      COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "." was found following "FROM administrator". Expected tokens may include: "FROM". SQLSTATE=42601

      I'm using jboss 3.2.3. I have configurated many one-to-many relationships successfully, but this one is kicking my ass. (I think this is the only one I have where the entity on the many side has a primary key comprised of multiple columns.)

      ejb-jar.xml:
      .
      .
      .

      <ejb-name>Region</ejb-name>
      <local-home>com.foo.entity.region.RegionLocalHome</local-home>
      com.foo.entity.region.RegionLocal
      <ejb-class>com.foo.entity.region.ejb.RegionBean</ejb-class>
      <persistence-type>Container</persistence-type>
      <prim-key-class>java.lang.Integer</prim-key-class>
      False
      <abstract-schema-name>Region</abstract-schema-name>
      <cmp-field>
      <field-name>regionCode</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>officeName</field-name>
      </cmp-field>
      <primkey-field>regionCode</primkey-field>

      Get all regions
      <query-method>
      <method-name>findAll</method-name>
      <method-params/>
      </query-method>
      <ejb-ql>SELECT OBJECT(r) FROM Region AS r</ejb-ql>



      <ejb-name>RegionZone</ejb-name>
      <local-home>com.foo.entity.regionZone.RegionZoneLocalHome</local-home>
      com.foo.entity.regionZone.RegionZoneLocal
      <ejb-class>com.foo.entity.regionZone.ejb.RegionZoneBean</ejb-class>
      <persistence-type>Container</persistence-type>
      <prim-key-class>com.foo.entity.regionZone.RegionZonePK</prim-key-class>
      False
      <abstract-schema-name>RegionZone</abstract-schema-name>
      <cmp-field>
      <field-name>attribute</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>matchExpression</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>accessor</field-name>
      </cmp-field>

      .
      .
      .
      <ejb-relation>
      <ejb-relation-name>Region-RegionZone</ejb-relation-name>
      <ejb-relationship-role>
      <ejb-relationship-role-name>Region-Has-Many-Zones</ejb-relationship-role-name>
      One
      <relationship-role-source>
      <ejb-name>Region</ejb-name>
      </relationship-role-source>
      <cmr-field>
      <cmr-field-name>zones</cmr-field-name>
      <cmr-field-type>java.util.Set</cmr-field-type>
      </cmr-field>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>Zone-in-One-Region</ejb-relationship-role-name>
      Many
      <relationship-role-source>
      <ejb-name>RegionZone</ejb-name>
      </relationship-role-source>
      <cmr-field>
      <cmr-field-name>region</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>
      </ejb-relation>

      jbosscmp-jdbc.xml:


      <ejb-name>Region</ejb-name>
      <table-name>administrator.REGIONS</table-name>
      <cmp-field>
      <field-name>regionCode</field-name>
      <column-name>REGIONCODE</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>officeName</field-name>
      <column-name>OFFICENAME</column-name>
      </cmp-field>


      <ejb-name>RegionZone</ejb-name>
      <table-name>administrator.REGION_ZONES</table-name>
      <cmp-field>
      <field-name>attribute</field-name>
      <column-name>ATTRIBUTE</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>matchExpression</field-name>
      <column-name>MATCHEXPRESSION</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>accessor</field-name>
      <column-name>ACCESSOR</column-name>
      </cmp-field>

      .
      .
      .
      <ejb-relation>
      <ejb-relation-name>Region-RegionZone</ejb-relation-name>
      <foreign-key-mapping/>
      <ejb-relationship-role>
      <ejb-relationship-role-name>Region-Has-Many-Zones</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>regionCode</field-name>
      <column-name>REGIONCODE</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>Zone-in-One-Region</ejb-relationship-role-name>
      <key-fields/>
      </ejb-relationship-role>
      </ejb-relation>
      .
      .
      .


        • 1. Re: wierd sql generated by container for cmr-field (1-*)
          Joan Horta  Tosas Novice

          I think your problem is due to putting the key-field element on the wrong side of the relation mapping... Try this:

          <ejb-relation>
          <ejb-relation-name>Region-RegionZone</ejb-relation-name>
          <foreign-key-mapping/>
          <ejb-relationship-role>
          <ejb-relationship-role-name>Region-Has-Many-Zones</ejb-relationship-role-name>
          <key-fields/>
          </ejb-relationship-role>
          <ejb-relationship-role>
          <ejb-relationship-role-name>Zone-in-One-Region</ejb-relationship-role-name>
          <key-fields>
          <key-field>
          <field-name>regionCode</field-name>
          <column-name>REGIONCODE</column-name>
          </key-field>
          </key-fields>
          </ejb-relationship-role>
          </ejb-relation>
          



          • 2. Re: wierd sql generated by container for cmr-field (1-*)
            Joan Horta  Tosas Novice

            Oooops! Sorry about my last post, but I was wrong about what I said. Your mapping in jbosscmp-jdbc.xml are correct. :-P
            But maybe I know (not sure again ;-)) what could it be... What's the definition of the RegionZone primary key? You said it's a compound PK. Can you post the source code of it? Thanks.

            • 3. Re: wierd sql generated by container for cmr-field (1-*)
              Richard Doust Newbie

              Thank you for your response. It took a couple of minutes of thinking about it after I read your post before I remembered that the PK class has to define its attributes with public access. I had been doing so much JavaBean development recently and had gotten so used to declaring private instance variables and public accessors that I'd forgotten about this requirement of the PK class. The container was unable to find any publicly declared instance variables in the PK class, so it couldn't generate the right SQL.
              Thanks again!

              • 4. Re: wierd sql generated by container for cmr-field (1-*)
                Joan Horta  Tosas Novice

                You're welcome. I didn't wanted to say anything about the PK fields before seeing the source because I didn't wanted to to say another "stupid" resolution before like the one I said before :P, but I thought that maybe your PK fields weren't public or were not declared as being persistent in the bean definition.
                I think that if you've seen it just with my comments will be better for you in the future 'cause I think it will be difficult to do this error again. I say it by my own experience: most of the errors I made in the past with J2EE I found the solution with the help of other people saying things, but not really guessing what was the problem, but now I always remember it and I don't do them again :) (most of them were begginners' ones, but I don't know why these kind of errors are nearly always very difficult to detect, don't you agree? :P).
                See you.