5 Replies Latest reply on Oct 28, 2004 4:08 AM by Jörgen N

    unknown pk beans - db2

    Jörgen N Newbie

      Hi!


      I have a bean which is defined with an unknown primary key.
      The bean has a jbosscmp-jdbc.xml file associated where the pk is specified.

      It all works fine in the hypersonic db and jboss 3.2.5, but when bringing it to our real db environment, db2 7.2, I get the following problems:



      At deployment:
      create table error:

      10:07:19,053 ERROR [EntityContainer] Starting failed jboss.j2ee:jndiName=local/TestT,service=EJB org.jboss.deployment.DeploymentException: Error while creating table IMAGE; - nest
      ed throwable: (COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL0104
      N An unexpected token "," was found following "ER NOT NULL IDENTITY". Expected t
      okens may include: "<references_spec>". SQLSTATE=42601
      )
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCo
      ...




      This is not a show stopper problem. I could create the tables myself, and so I did...


      At runtime: (after I created the tables myself...)
      primary key generation error


      -----------------------------------------------------------------------------------
      09:58:55,850 ERROR [ImageCollection] Could not create entity
      COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL0798N A value cann
      ot be specified for column "ID" which is defined as GENERATED ALWAYS. SQLSTATE=42
      8C9

      at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExcept
      ionGenerator.java:267)
      at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExcept
      ionGenerator.java:209)
      at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExcepti
      onGenerator.java:455)
      at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(DB2PreparedStatement
      ----------------------------------------------------------------------------



      This I get when I rely on the default entity command. (none specified)
      What <entity-command> should be used for db2? (I have seen posts on one called DB2IdentityValLocalCreateCommand which seem to be the right one, but when adding an entity command myself for this class, the classfile is not found...)

      For the working hypersonic sql, I use <entity-command name="hsqldb-fetch-key"/>


      This is my pk definition in the jboss file

      <unknown-pk>
      <unknown-pk-class>java.lang.Integer</unknown-pk-class>
      <column-name>ID</column-name>
      <jdbc-type>INTEGER</jdbc-type>
      <sql-type>INTEGER</sql-type>
      <auto-increment/>
      </unknown-pk>

      When I created the tables myself, I made an ID column as follows
      ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +1 , INCREMENT BY +1 , CACHE 20 )


      Any clues on this?

        • 1. Re: unknown pk beans - db2
          Robert Szalai Newbie

          Probably you need to specify an entity-command in your jbosscmp-jdbc.xml

          Have you done it?

          • 2. Re: unknown pk beans - db2
            Jörgen N Newbie

            I took away the hypersonic one and tried with

            default
            key-generator

            without success


            I also added one myself into the standardjbosscmp-jdbc.xml which mapped to org.jboss.ejb.plugins.cmp.jdbc.DB2IdentityValLocalCreateCommand,
            which was mentioned in some posts about it being developed... (This was a guess from my side and I have not seen it defined really.)
            All this without success either...




            • 3. Re: unknown pk beans - db2
              Robert Szalai Newbie

              You should try with get-generated-keys entity-command.

              Please post your jbosscmp-jbc.xml

              • 4. Re: unknown pk beans - db2
                Jörgen N Newbie



                Here is my jbosscmp-jdbc.xml

                <?xml version="1.0" encoding="UTF-8"?>

                <jbosscmp-jdbc>


                java:/test
                <type-mapping>DB2</type-mapping>


                false
                <create-table>false</create-table>
                <remove-table>false</remove-table>
                <read-only>false</read-only>
                <time-out>300</time-out>
                <select-for-update>false</select-for-update>
                <pk-constraint>true</pk-constraint>
                <relation-mapping-style>foreign-key</relation-mapping-style>


                <enterprise-beans>




                <ejb-name>Image</ejb-name>


                <pk-constraint>false</pk-constraint>
                <unknown-pk>
                <unknown-pk-class>java.lang.Integer</unknown-pkclass>
                <column-name>ID</column-name>
                <jdbc-type>INTEGER</jdbc-type>
                <sql-type>INTEGER</sql-type>
                <auto-increment/>
                </unknown-pk>

                <entity-command name="get-generated-keys"/>




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




                As you see, I tried your suggstion, but then I got an error from the jdbc driver. To me it looks like a "linking" error. What jdbc level is required? I use the driver that is shipped with the db2 database. I have the latest patchlevel.


                13:03:12,358 ERROR [LogInterceptor] TransactionRolledbackLocalException in method:
                public abstract test.ImageLocal test.ImageLocalHome.create() throws javax.ejb.CreateException, causedBy:
                javax.ejb.EJBException: Unexpected Error
                java.lang.AbstractMethodError: COM.ibm.db2.jdbc.app.DB2Connection.prepareStatement
                (Ljava/lang/String;I)Ljava/sql/PreparedStatement;
                at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(Wrap
                pedConnection.java:280)
                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.ja
                va:39)
                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccesso
                rImpl.java:25)
                at java.lang.reflect.Method.invoke(Method.java:324)
                at org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBC30GeneratedKeysCreateCommand.
                prepareStatement(JDBC30GeneratedKeysCreateCommand.java:65)
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.performInsert(
                JDBCAbstractCreateCommand.java:276)

                • 5. Re: unknown pk beans - db2
                  Jörgen N Newbie

                  Solved the runtime problem by getting the class
                  DB2IdentityValLocalCreateCommand from SF and then compiled it and stuffed into a jar of my own. Then I created a new entity-command in my standardjbosscmp-jdbc.xml pointing to the class and then used that new entity command in my cmp.

                  I found out about the class in this post
                  http://www.jboss.com/index.html?module=bb&op=viewtopic&t=44716


                  I have also seen posts about something similar for intsance this post.
                  http://www.jboss.com/index.html?module=bb&op=viewtopic&t=46698
                  (a somewhat different name and a different package though)

                  Does anyone know why it is not in the regular distribution? Is it coming in future releases? I use 3.2.5, so maybe it is in newer distributions...

                  The code seem to work fine, but I am curious to know if there are problems with the code that I have not seen yet.