2 Replies Latest reply on Apr 20, 2002 3:26 PM by dsundstrom

    Duplicate column name in insert statement when using CMR

    cwbrandon

      Hi there,

      I'm getting the following exception when I try to have two CMR relationships which use the same column:

      19:58:56,714 ERROR [STDERR] javax.ejb.CreateException: Could not create entity:j
      ava.sql.SQLException: [DataDirect][SQLServer JDBC Driver][SQLServer]Column name
      'Owner_ID' appears more than once in the result column list.
      The SQL it executes is:

      ] Executing SQL: INSERT INTO Accounts (Acc_ID, Acc_Name_Code, Acc_Desc, Acc_Balance, Established, Notes, Active, Last_Update, Row_Language, Acc_Type_Code, Owner_ID, Owner_ID, Acc_Owner_Type_Code, Last_User_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)


      This is what I'm trying to do: I have an Account EJB, and I have a Person EJB and Organization EJB. Either the Person or the Organization can be the owner of the Account but not both, so therefore I specify one column name for both relationships in jbosscmp-jdbc.xml called "Owner_ID". Since either the Person or Organization can be the owner of the Account but not both, I don't see a reason to have a column for each relationship. So when I try to add an Account EJB with a Person or Organization as the owner, I get the above exception because it generates the insert statement with the column name inserted twice. Shouldn't I be able to do something like this?

      Here are the snippets from jbosscmp-jdbc.xml for the Person-Accounts and Organization-Accounts relationships:

      Person-Accounts

      <ejb-relation-name>Person-Accounts</ejb-relation-name>
      <foreign-key-mapping/>
      <ejb-relationship-role>
      <ejb-relationship-role-name>many-accounts-may-be-associated-with-one-person</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>personPk</field-name>
      <column-name>Owner_ID</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>one-account-may-be-associated-to-one-person</ejb-relationship-role-name>
      <fk-constraint>false</fk-constraint>
      <key-fields/>
      </ejb-relationship-role>
      </ejb-relation>
      <ejb-relation>

      Organization-Accounts

      <ejb-relation>
      <ejb-relation-name>Organization-Accounts</ejb-relation-name>
      <foreign-key-mapping/>
      <ejb-relationship-role>
      <ejb-relationship-role-name>many-accounts-may-be-associated-with-one-organization</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>organizationPk</field-name>
      <column-name>Owner_ID</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>one-account-may-be-associated-to-one-organization</ejb-relationship-role-name>
      <fk-constraint>false</fk-constraint>
      <key-fields/>
      </ejb-relationship-role>
      </ejb-relation>


      Thanks,

      Chad