unknown pk beans - db2

[jornik]

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?

[robisz]

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

Have you done it?

[jornik]

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...

[robisz]

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

Please post your jbosscmp-jbc.xml

[jornik]

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)

[jornik]

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.