problem with many-to-many relation to self

[chumps]

Hello,

I'm trying to deploy an CMP EJB that has a many-to-many bidirectional relationship with itself.

i.e LegislationEJB * <---> * LegislationEJB

here is the relevent part of the LegislationEJB.java

// CMR fields
public abstract Collection getAssociatedLegislations();
public abstract void setAssociatedLegislations(Collection legs);

// methods

public void addAssociatedLegislation(LegislationLocal legislation) {
try {
Collection legs = getAssociatedLegislations();
legs.add(legislation);
}
catch (Exception e) {
throw new javax.ejb.EJBException(e.getMessage());
}
}

public boolean removeAssociatedLegislation(LegislationLocal legislation) {
try {
Collection legs = getAssociatedLegislations();
return legs.remove(legislation);
}
catch (Exception e) {
throw new javax.ejb.EJBException(e.getMessage());
}
}


To manage the relationship I'm using a session bean DatabaseBrokerEJB to create these associations:

public void associateLegislations(LegislationDetails ld, Collection others) {
try {
LegislationLocal legislation = findLegislation(ld);
Iterator iterator = others.iterator();
while(iterator.hasNext()) {
LegislationDetails each = (LegislationDetails) iterator.next();
LegislationLocal associatedLegislation = findLegislation(each);
legislation.addAssociatedLegislation(associatedLegislation);
}
}
catch (Exception e) {
throw new EJBException(e.getMessage());
}
}

here is how the relation is declared in ejb-jar.xml

<ejb-relation>
<ejb-relationship-role>
<ejb-relationship-role-name>LegislationEJB</ejb-relationship-role-name>
Many
<relationship-role-source>
<ejb-name>LegislationEJB</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>associatedLegislations</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>AssociatedLegislationEJB</ejb-relationship-role-name>
Many
<relationship-role-source>
<ejb-name>LegislationEJB</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>associatedLegislations</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>

when I try to associate two legislations I get the following exception:

2002-07-31 17:55:01,859 ERROR [org.jboss.ejb.GlobalTxEntityMap] Store failed on entity: 62aa25c8bfa8038f00566a6c158f1a2a
javax.ejb.EJBException: Could insert relations into LEGISLATIONEJB_ASSOCIATEDLEGISLATIONS_LEGISLATIONEJB_ASSOCIATEDLEGISLATIONS; CausedByException is:
Try to insert null into a non-nullable column in statement [INSERT INTO LEGISLATIONEJB_ASSOCIATEDLEGISLATIONS_LEGISLATIONEJB_ASSOCIATEDLEGISLATIONS (associatedLegislations, associatedLegislations) VALUES ('62aa25c8bfa8038f00566a6c158f1a2a', '62aa24d1bfa8038f00566a6ce4b7106b')]
at org.jboss.ejb.plugins.cmp.jdbc.JDBCInsertRelationsCommand.
execute(JDBCInsertRelationsCommand.java:77)

The problem seems to be that the automatically generated SQL statement (possibly the underlying table schema which is default because I'm not using a jbosscmp-jdbc.xml descriptor) is wrong because it's trying to insert different values in the same column: associatedLegislations. I'm guessing this is happening because the cmr-field is the same for both (actually it's the same EJB) entities.

How can I fix this? Is there a way to define a many-to-mane relationship on one bean ?

I'm using the Hypersonic default datasource. Is there a way I can see the table schemas that are created and the value they contain?

Any help would be appreciated.
Thanks.