Relationship to CMP bean with compound PK
JBoss is encountering a SQLException when we deploy a bean that we have configured as follows.
<PersonBean.java>
/**
* @ejb:relation
* name="Person-IrsInfo"
* role-name="Person-has-an-IrsInfo"
* cascade-delete="no"
* target-ejb="IrsInfo"
* target-role-name="IrsInfo-belongs-to-a-Person"
* target-cascade-delete="yes"
* target-multiple="no"
*
* @jboss:relation
* fk-constraint="true"
* related-pk-field="socialSecurityPrefix"
* fk-column="ssPfx"
* @jboss:relation
* fk-constraint="true"
* related-pk-field="socialSecurityMiddle"
* fk-column="ssMid"
* @jboss:relation
* fk-constraint="true"
* related-pk-field="socialSecuritySuffix"
* fk-column="ssSfx"
*/
public abstract IrsInfo getIrsInfo();
public abstract void setIrsInfo( IrsInfo irsInfo );
<IrsInfo.java>
/**
* @ejb:persistent-field
* @ejb:pk-field
* @jboss:sql-type
* type="VARCHAR(3)"
* @jboss:jdbc-type
* type="VARCHAR"
*/
public abstract String getSocialSecurityPrefix();
public abstract void setSocialSecurityPrefix( String socialSecurityPrefix );
/**
* @ejb:persistent-field
* @ejb:pk-field
* @jboss:sql-type
* type="VARCHAR(2)"
* @jboss:jdbc-type
* type="VARCHAR"
*/
public abstract String getSocialSecurityMiddle();
public abstract void setSocialSecurityMiddle( String socialSecurityMiddle );
/**
* @ejb:persistent-field
* @ejb:pk-field
* @jboss:sql-type
* type="VARCHAR(4)"
* @jboss:jdbc-type
* type="VARCHAR"
*/
public abstract String getSocialSecuritySuffix();
public abstract void setSocialSecuritySuffix( String socialSecuritySuffix );
As you can see, the IrsInfo bean has a primary key with 3 fields. We would like the Person bean to maintain a foreign key relationship with this bean. We encounter this error when the fk-constraint flags are set to "true", but not when they are all set to "false".
Our environment is:
JDK 1.4
JBoss 3.0.3/Tomcat 4.1.12
MySQL 3.23.52-max-nt
MM MySQL JDBC driver 2.0.14
I have also tried JBoss 3.0.4/Tomcat 4.1.12 and get the same results.
When I deploy these beans, I get the following exception. It seems to indicate that JBoss is forming a bad SQL statement:
org.jboss.deployment.DeploymentException: Error while adding foreign key constraint; - nested throwable: (java.sql.SQLException: Syntax error or access violatio
n: You have an error in your SQL syntax near ' ssSfx, ssMid (ssPfx, ssSfx, ssMid), ADD CONSTRAINT fk_Person_irsInfo FOREIGN KE' at line 1)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.addForeignKeyConstraint(JDBCStartCommand.java:384)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.addForeignKeyConstraint(JDBCStartCommand.java:304)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execute(JDBCStartCommand.java:123)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startStoreManager(JDBCStoreManager.java:457)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:369)
at org.jboss.ejb.plugins.CMPPersistenceManager.start(CMPPersistenceManager.java:198)
at org.jboss.ejb.EntityContainer.start(EntityContainer.java:376)
at org.jboss.ejb.Container.invoke(Container.java:756)
at org.jboss.ejb.EntityContainer.invoke(EntityContainer.java:1058)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
at $Proxy5.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:398)
at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
at $Proxy112.start(Unknown Source)
at org.jboss.ejb.EjbModule.startService(EjbModule.java:430)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:165)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
at $Proxy5.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:398)
at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
at $Proxy9.start(Unknown Source)
at org.jboss.ejb.EJBDeployer.start(EJBDeployer.java:395)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:807)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:621)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:585)
at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
at $Proxy4.deploy(Unknown Source)
at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:435)
at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:561)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:212)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:225)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:202)
Caused by: java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax near ' ssSfx, ssMid (ssPfx, ssSfx, ssMid), ADD CONSTRAINT fk_Person_irsInfo FOREIGN KE' at line 1
at org.gjt.mm.mysql.MysqlIO.sendCommand(Unknown Source)
at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(Unknown Source)
at org.gjt.mm.mysql.MysqlIO.sqlQuery(Unknown Source)
at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
at org.gjt.mm.mysql.Statement.executeUpdate(Unknown Source)
at org.gjt.mm.mysql.jdbc2.Statement.executeUpdate(Unknown Source)
at org.jboss.resource.adapter.jdbc.local.LocalStatement.executeUpdate(LocalStatement.java:231)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.addForeignKeyConstraint(JDBCStartCommand.java:375)
... 53 more