Thought inc.

The Dynamic O/R Mapping Company
     

CocoBase Technical Support Forums
For access to developer site with software download and doc's, please request CocoBase download and password
will be emailed to you. Response to posts will appear when answered by THOUGHT Support team.
NOTE:You must register separately with forum in order to post your questions, please click on register icon below.
Home   Frequently Asked Questions   Search   Edit your profile   Registration is free!  
Email This Page to Someone!
Show a Printable Version
CocoBase Enterprise O/R Forums > CocoBase Bug Reports
CocoBase JPA + DB2 problem
< Previous Thread  
Author
Thread    Post New Thread     Post A Reply
ucu
Member

Registered: Apr 2007
Posts: 3

hello
got some problem using CocoBase persistence provider with DB2

environment:
J2SE 1.6
IBM DB2 v9.1

the problem comes up when using the following code in an entity:
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

CocoBase EntityManager generates invalid CREATE TABLE statement which causes an SQLException

setting com.thoughtinc.runtime.persistence.ddl.generation to false and creating the table manually solves the problem but then the persist() method fails. It generates invalid statement and an SQLException is thrown again.

?kos Utasi

04-13-2007 04:36 AM
Click Here to See the Profile for ucu    Find more posts by ucu        Edit/Delete Message    Reply w/Quote    IP: Logged
admin
Administrator

Registered: Apr 2001
Posts: 19

Hmm.. It's hard to know what/why this is happening, but it might be related to the use of the identity column type. As we understand it DB2 requires a GenerationType.SEQUENCE instead of identity for most version of that database. The SEQUENCE type is more typically associated with the DB2 and Oracle databases, whereas the IDENTITY type is generally associated with the SQL Server dialects. This use of the IDENTITY field would probably also explain the DDL generation issue as well.

If you generate the debug for the SQL that's being generated, it might help confirm if this is true or not. Here's the property you can add to your persistence.xml

<property name="com.thoughtinc.runtime.persistence.debug" value="sql"/>

or also

<property name="show.sql" value="true">

Does that answer your question?

Please let us know if this is resolved with our suggestion.

THOUGHT Support

04-13-2007 06:23 PM
Click Here to See the Profile for admin    Find more posts by admin        Edit/Delete Message    Reply w/Quote    IP: Logged
ucu
Member

Registered: Apr 2007
Posts: 3

tried all the generation strategies (table, sequence, identity) and had problem with the identity strategy only.

the problem comes up with the geberated statement:
CREATE TABLE PERSON( ID BIGINT PRIMARY KEY , NAME VARCHAR(128), AGE INTEGER, WEIGHT DOUBLE )
it is invalid since primary key cannot be null

in db2 it should be something like this:
CREATE TABLE PERSON (ID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL, NAME VARCHAR(128), AGE INTEGER, WEIGHT FLOAT, PRIMARY KEY (ID))

created this table manually and tried the persist() method.
the generated prepared statement is ok:
insert into PERSON (NAME,AGE,WEIGHT) values ( ? , ? , ? )
but after this the following query is called:
select @@IDENTITY
which is invalid and will cause an sqlexception

04-16-2007 01:10 AM
Click Here to See the Profile for ucu    Find more posts by ucu        Edit/Delete Message    Reply w/Quote    IP: Logged
admin
Administrator

Registered: Apr 2001
Posts: 19

You may need to also add this property to your persistence.xml in order to make sequences work properly.

<persistence-unit name="test">
<provider>com.thoughtinc.javax.persistence.JPAPersistenceProvider</provide
r>

<properties>
...
<property
name="com.thoughtinc.runtime.persistence.sql.syntax"
value="db2"
/>
</properties>

</persistence-unit>

This will let the CocoBase JPA provider know it needs to use DB2 sequence syntax when handling GenerationType.SEQUENCE strategy.

Does that make sense? Please let us know if this addresses your issue properly.

THOUGHT Support

04-16-2007 12:29 PM
Click Here to See the Profile for admin    Find more posts by admin        Edit/Delete Message    Reply w/Quote    IP: Logged
ucu
Member

Registered: Apr 2007
Posts: 3

This property was already set in the xml.
And the sequence strategy works without any problem.

05-02-2007 02:05 PM
Click Here to See the Profile for ucu    Find more posts by ucu        Edit/Delete Message    Reply w/Quote    IP: Logged
admin
Administrator

Registered: Apr 2001
Posts: 19

That's great to hear.

The new raw identity support for DB2 is a very recent addition to some of their databases, so it not supported in all versions of that database/jdbc drivers that customers typically use. It also requires a jdbc3 driver to implement in the recommended fashion - which is also not available in all deployment environments.

Engineering is working on a reliable method to support DB2 (identity) on the widest set of drivers and platforms. We will update CB5 to support the method that appears to most reliably support DB2 Identity facilities in the next one or two dot releases of the product, just in case that's a future requirement for you.

THOUGHT Support

05-02-2007 03:59 PM
Click Here to See the Profile for admin    Find more posts by admin        Edit/Delete Message    Reply w/Quote    IP: Logged
All times are PST (US)    Post New Thread     Post A Reply
Forum Jump:
< Previous Thread  

Forum Rules:
Who Can Read The Forum? Any registered user or guest.
Who Can Post New Topics? Any registered user.
Who Can Post Replies? Any registered user.
Changes: Messages can be edited by their author. Messages can be deleted by their author.
Posts: HTML code is OFF. Smilies are OFF. vB code is OFF. [IMG] code is OFF.

Admin Options:
Open / Close Thread
Move Thread
Delete Thread
Edit Thread

< Contact Us - THOUGHT Inc. >

Copyright © Jelsoft Enterprises Limited 2000.
Copyright 2001 All Rights Reserved, THOUGHT Inc.