0 Replies Latest reply on Nov 19, 2002 1:48 PM by Eric McIntyre

    Relationship to CMP bean with compound PK

    Eric McIntyre Newbie

      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