jbosscmp-jdbc.xml relationship mapping dbindex problems

[garek]

Hi,

I'm using jboss 3.2.5 with mysql 4.0.18. However, I'm having problems when I try to have Jboss create indexes on foreign keys when it creates tables.

For example, I have two entity beans, ItemEJB and ItemQuantitiesEJB, in a one-to-many relationship. ItemEJB has a primary key field called itemId and ItemQuantitiesEJB has a foreign key column called ItemEJB_foreignKey, which corresponds to the itemId field in ItemEJB.

When I deploy the application, the tables are created, and then Jboss creates an index on the ItemEJB_foreignKey field in the ItemQuantitiesEJB table, which is what I want.

But here is the problem, Jboss then tries to create an index on a field called ItemEJB_foreignKey in the ItemEJB table - but this field does not exist, causing an sql exception to be thrown.

Here's the relationship mapping from the jbosscmp-jdbc.xml file:

<ejb-relation>
 <ejb-relation-name>Item-Quantity</ejb-relation-name>
 <foreign-key-mapping />
 <ejb-relationship-role>
 <ejb-relationship-role-name>
 Item-has-many-Quantity
 </ejb-relationship-role-name>
 <key-fields>
 <key-field>
 <field-name>id</field-name>
 <column-name>ItemEJB_foreignKey</column-name>
 <dbindex />
 </key-field>
 </key-fields>
 </ejb-relationship-role>
 <ejb-relationship-role>
 <ejb-relationship-role-name>
 Quantity-belongs-to-a-Item
 </ejb-relationship-role-name>
 <batch-cascade-delete />
 </ejb-relationship-role>
</ejb-relation>

Is there something I'm missing?

Thanks,
Brian

[triathlon98]

How is your table created?

Would it be possible to try to enable alter-table? This would update your DB structure (careful, it can also remove fields).
If JBoss actually assumes the field to exist, then it would be created.

If you try to access the bean, does that work? Does it also generate SQL code which reads the non-existent field?

Joachim

[garek]

The tables are created by Jboss.

I thought about using the alter-table command, but I wanted to try to use the command so that I wouldn't have to write and maintain the sql statements to create the indexes (there are a lot of EJBs and relationships in this application).

I didn't try to access the bean after the sql exception, I think the deployment was halted after that.

Thanks for the input,
Brian

[triathlon98]

I think there was a fix regarding fk-constraint mapping in MySQL a few days ago. Please try again using the latest version from CVS.

Joachim

[aloubyansky]

Just the index name was removed.

<fk-constraint-template>ALTER TABLE ?1 ADD INDEX (?3), ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5)</fk-constraint-template>