Duplicate column name in insert statement when using CMR
cwbrandon Apr 19, 2002 10:46 PMHi 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