I have been unable to get CMR Relation Table Mapping to work. The symptons and
relevant xml is below. Can you see something wrong, or can you think of
something I should examine, or can you at least tell me that it works for you?

Systems:
1. RH 8.0 Linux, JBoss_3.0.6_Tomcat-4.1.18, Postgres 7.2 (or)
2. Windows98, JBoss_3.0.6_Tomcat-4.1.18, mysql

Entity Beans: Users, County
Tables: users, counties, usercounty

Clues:
1. Jboss creates users, counties tables correctly.
2. Jboss always claims that USERCOUNTY table is already created, whether it is
present in the db (because I manually created it) or not.

[UserBean] Table 'users' already exists <!-- true -->
[CountyBean] Table 'counties' already exists <!-- true -->
[CountyBean] Table 'USERCOUNTY' already exists <!-- false -->

3. Jboss never actually creates the UserCounty table.
4. If a client tries to set the 'Counties' CMR field in the Users EJB, it gets the
EJBException below.
5. If I run the same sql from psql, it executes OK.

javax.ejb.EJBException: Load relation failed; CausedByException is:
Column not found: COUNTYID in statement [SELECT countyid FROM USERCOUNTY WHERE (userid=0)]
at org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.execute(JDBCLoadRelationCommand.java:187)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.loadRelation(JDBCStoreManager.java:643)
at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMRFieldBridge.load(JDBCCMRFieldBridge.java:839)
at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMRFieldBridge.setInstanceValue(JDBCCMRFieldBridge.java:521)
at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMRFieldBridge.setValue(JDBCCMRFieldBridge.java:483)
at org.jboss.ejb.plugins.cmp.bridge.EntityBridgeInvocationHandler.invoke(EntityBridgeInvocationHandler.java:126)
at org.jboss.proxy.compiler.Runtime.invoke(Runtime.java:59)
at com.gov.ejb.UserBean$Proxy.setCounties()
at com.gov.ejb.UserBean.addCounty(UserBean.java:73)



ejb-jar.xml (relevant portions)
--------------------------------


Models a user
<ejb-name>UserBean</ejb-name>
<local-home>com.gov.ejb.UserHome</local-home>
com.gov.ejb.User
<ejb-class>com.gov.ejb.UserBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Integer</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>User</abstract-schema-name>
<cmp-field>
<field-name>address1</field-name>
</cmp-field>

<!-- !!!! snipped cmp-fields !!!! -->

<cmp-field>
<field-name>userName</field-name>
</cmp-field>
<cmp-field>
<field-name>userId</field-name>
</cmp-field>
<primkey-field>userId</primkey-field>

<query-method>
<method-name>findByUserName</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>SELECT OBJECT(o) FROM User AS o WHERE o.userName = ?1</ejb-ql>




Models a county
<ejb-name>CountyBean</ejb-name>
<local-home>com.gov.ejb.CountyHome</local-home>
com.gov.ejb.County
<ejb-class>com.gov.ejb.CountyBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Integer</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>County</abstract-schema-name>
<cmp-field>
<field-name>countyName</field-name>
</cmp-field>
<cmp-field>
<field-name>stateName</field-name>
</cmp-field>
<cmp-field>
<field-name>countyId</field-name>
</cmp-field>
<cmp-field>
<field-name>entityId</field-name>
</cmp-field>
<primkey-field>countyId</primkey-field>

<query-method>
<method-name>findByNames</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>SELECT OBJECT(o) FROM County AS o WHERE o.stateName = ?1 AND o.countyName = ?2</ejb-ql>




<ejb-relation>
<ejb-relation-name>Users-Regions</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>user-has-regions</ejb-relationship-role-name>
Many
<relationship-role-source>
<ejb-name>UserBean</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>counties</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>region-has-users</ejb-relationship-role-name>
Many
<relationship-role-source>
<ejb-name>CountyBean</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>



jbosscmp-jdbc.xml (relevant portions)
-------------------------------------


<ejb-name>UserBean</ejb-name>
java:/RefNetDS
<datasource-mapping>PostgreSQL</datasource-mapping>
<create-table>true</create-table>
<remove-table>false</remove-table>
<table-name>users</table-name>
<cmp-field>
<field-name>address1</field-name>
<column-name>address1</column-name>
</cmp-field>

<!-- snipped cmp-fields -->

<cmp-field>
<field-name>userName</field-name>
<column-name>username</column-name>
</cmp-field>
<cmp-field>
<field-name>userId</field-name>
<column-name>userid<column-name>
</cmp-field>


<ejb-name>CountyBean</ejb-name>
java:/RefNetDS
<datasource-mapping>PostgreSQL</datasource-mapping>
<create-table>true</create-table>
<remove-table>false</remove-table>
<table-name>counties</table-name>
<cmp-field>
<field-name>countyName</field-name>
<column-name>countyname</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>varchar(40)</sql-type>

</cmp-field>
<cmp-field>
<field-name>stateName</field-name>
<column-name>statename</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>varchar(20)</sql-type>
</cmp-field>
<cmp-field>
<field-name>countyId</field-name>
<column-name>countyid</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>integer</sql-type>
</cmp-field>
<cmp-field>
<field-name>entityId</field-name>
<column-name>entityid</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>serial</sql-type>
</cmp-field>


<ejb-relation>
<ejb-relation-name>Users-Regions</ejb-relation-name>
<relation-table-mapping>
<table-name>usercounty</table-name>
<create-table>true</create-table>
<remove-table>false</remove-table>
</relation-table-mapping>
<ejb-relationship-role>
<ejb-relationship-role-name>user-has-regions</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>userId</field-name>
<column-name>userid</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>region-has-users</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>countyId</field-name>
<column-name>countyid</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
</ejb-relation>