1 Reply Latest reply on Oct 14, 2002 3:04 AM by Alexey Loubyansky

    Strange Relation SQL

    Paul O'Connor Newbie

      I have a 1-n relation between an entity bean called 'ProductionPart' and one called ProductionPartNotes. ProductionPartNotes has a FK of 'pp_id' and a primary key of 'pkey'. ProductionPart (the 1-side of the relation) has a String PK called 'partNumber'. Here is the realtion excerpt from the jbosscmp-jdbc.xml:

      <ejb-relation>
      <ejb-relation-name>ProductionPart-Notes</ejb-relation-name>
      <foreign-key-mapping/>
      <ejb-relationship-role>
      <ejb-relationship-role-name>ProductionPart-Has-Many-Notes</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>partNumber</field-name>
      <column-name>pp_id</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>Note-Has-One-ProductionPart</ejb-relationship-role-name>
      <key-fields/>
      </ejb-relationship-role>
      </ejb-relation>

      When I invoke 'getNotes' on the parent bean, the CMP container tries to invoke some very strange SQL:

      SELECT pp_id, key FROM ProductionPartNote WHERE (pp_id=?) OR (pp_id=?) OR (pp_id=?) OR (pp_id=?) OR (pp_id=?)

      which causes a SQLException (of course).

      I would have expected to see this for the SQL:

      SELECT pkey from ProductionPartNote WHERE (pp_id=?)

      Anyone run into this before?

      Many Thanks in Advance