6 Replies Latest reply on Nov 14, 2002 1:58 PM by Craig Hamilton

    sqlserver 2000 cmp create table problem

    Craig Hamilton Newbie

      I get the following error when deploying my ejb.

      First is my jbosscmp-jdbc.xml file, then the error trace.

      Any ideas?

      cheers,

      craig


      <!DOCTYPE jbosscmp-jdbc PUBLIC
      "-//JBoss//DTD JBOSSCMP-JDBC 3.0//EN"
      "http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_3_0.dtd">

      <jbosscmp-jdbc>

      java:/MSSQLDS
      <datasource-mapping>MS SQLSERVER2000</datasource-mapping>
      <create-table>true</create-table>
      <remove-table>false</remove-table>


      <enterprise-beans>

      <ejb-name>Activity</ejb-name>
      <table-name>kcactivity</table-name>
      <cmp-field>
      <field-name>activityID</field-name>
      <column-name>activityid</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>year</field-name>
      <column-name>year</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>description</field-name>
      <column-name>description</column-name>
      </cmp-field>

      </enterprise-beans>
      </jbosscmp-jdbc>


      15:47:38,222 WARN [ServiceController] Problem starting service jboss.j2ee:jndiName=local/Activity,service=EJB
      org.jboss.deployment.DeploymentException: Error while creating table; - nested throwable: (java.sql.SQLException: [Microsoft][SQLServer 2000
      r JDBC][SQLServer]Line 1: Incorrect syntax near ')'.)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCommand.java:175)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execute(JDBCStartCommand.java:84)
      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)

        • 1. Re: sqlserver 2000 cmp create table problem
          Stephen Coy Novice

          Turn on debug output - look in conf/log4j.xml - and have a look at the generated SQL.

          Maybe that will reveal the answer.

          • 2. Re: sqlserver 2000 cmp create table problem
            Craig Hamilton Newbie

            I seem to be having some trouble getting logging to work as expected.

            I uncommented the following lines in the log4j.xml, thinking this would result in the traces being sent to server.log...but no luck.

            Any idea what I am missing here?

            thanks,

            craig








            • 3. Re: sqlserver 2000 cmp create table problem
              Stephen Coy Novice



              in the FILE appender near the top of the file.

              Steve

              • 4. Re: sqlserver 2000 cmp create table problem
                Craig Hamilton Newbie

                The problem with the create table sql was that the fields for the primary key were not getting inserted properly and I was just ending up with constraint primary(). The primary key is a composite of activityid, year. The ejbCreate code within the bean is below. Also, I specify the ActivityPK class as the primary key field in the ejb-jar.xml file.

                here is the create table command taken from the log
                CREATE TABLE kcactivity1 (activityid VARCHAR(256), year INTEGER NOT NULL, description VARCHAR(256), CONSTRAINT pk_kcactivity1 PRIMARY KEY())

                public ActivityPK ejbCreate(String activityID, int year) throws CreateException {
                System.out.println("ejbCreate(String activityID,Integer year)");
                setActivityID(activityID);
                setYear(year);
                return null;
                }

                Any ideas here? I have got around it for now by flipping create primary key to false, but that won't work long term.

                Now when I try and obtain a remote reference to a bean I get the following error.

                Thanks for the help!

                craig


                Client code
                -=-=-=-=-=-=-
                Object obj = ctx.lookup("Activity");

                ActivityHomeRemote home = (ActivityHomeRemote)javax.rmi.PortableRemoteObject.narrow(obj,ActivityHomeRemote.class);

                ActivityRemote activity = home.findByPrimaryKey(new ActivityPK("001",2003));


                23:42:07,142 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Activity.findByPrimaryKey] SQL: SELECT FROM kcactivity WHERE
                2002-11-13 23:42:07,142 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCQueryManager.Activity] Added findByPrimaryKey query command for home interface
                2002-11-13 23:42:07,142 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Activity.findByPrimaryKey] SQL: SELECT FROM kcactivity WHERE
                2002-11-13 23:42:07,143 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCQueryManager.Activity] Added findByPrimaryKey query command for local home interface
                2002-11-13 23:42:07,145 INFO [org.jboss.ejb.EjbModule] Started
                2002-11-13 23:42:07,146 INFO [org.jboss.deployment.MainDeployer] Deployed package: file:/usr/local/jboss-3.0.4_tomcat-4.1.12/server/default/deploy/ejbbudgeting.jar
                2002-11-13 23:42:20,556 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Activity.findByPrimaryKey] Executing SQL: SELECT FROM kcactivity WHERE
                2002-11-13 23:42:20,562 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Activity.findByPrimaryKey] Find failed
                java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Incorrect syntax near the keyword 'FROM'.
                at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
                at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
                at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
                at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
                at com.microsoft.jdbc.sqlserver.tds.TDSExecuteRequest.processReplyToken(Unknown Source)
                at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
                at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
                at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
                at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
                at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
                at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
                at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)
                at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source)
                at org.jboss.resource.adapter.jdbc.local.LocalPreparedStatement.executeQuery(LocalPreparedStatement.java:289)
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:112)
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntityCommand.execute(JDBCFindEntityCommand.java:44)
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntity(JDBCStoreManager.java:541)
                at org.jboss.ejb.plugins.CMPPersistenceManager.findEntity(CMPPersistenceManager.java:336)
                at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.findEntity(CachedConnectionInterceptor.java:301)
                at org.jboss.ejb.EntityContainer.find(EntityContainer.java:690)
                at java.lang.reflect.Method.invoke(Native Method)
                at org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(EntityContainer.java:1119)
                at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:73)
                at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(EntitySynchronizationInterceptor.java:206)
                at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invokeHome(CachedConnectionInterceptor.java:215)
                at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:73)
                at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(EntityInstanceInterceptor.java:90)
                at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:79)
                at org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(EntityCreationInterceptor.java:44)
                at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:111)
                at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:178)
                at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:52)
                at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:105)
                at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:129)
                at org.jboss.ejb.EntityContainer.invokeHome(EntityContainer.java:487)
                at org.jboss.ejb.Container.invoke(Container.java:730)
                at org.jboss.ejb.EntityContainer.invoke(EntityContainer.java:1058)
                at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
                at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:382)
                at java.lang.reflect.Method.invoke(Native Method)
                at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:241)
                at sun.rmi.transport.Transport$1.run(Transport.java:152)
                at java.security.AccessController.doPrivileged(Native Method)
                at sun.rmi.transport.Transport.serviceCall(Transport.java:148)
                at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:465)
                at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:706)
                at java.lang.Thread.run(Thread.java:484)

                • 5. Re: sqlserver 2000 cmp create table problem
                  Stephen Coy Novice

                  I would be checking the implementation of ActivityPK to ensure that it properly satisfies the requirements for compound primary keys.

                  As there is not supposed to be a <primkey-field> element in the deployment descriptor for compound primary keys, JBoss needs to reflect the PK class to get the key field names.

                  Steve

                  • 6. Re: sqlserver 2000 cmp create table problem
                    Craig Hamilton Newbie

                    Steve,

                    The problem was in my PK class. thanks alot for the help.

                    craig