perform tests on DB2 database gives "UPDATE SEQUENCE" error

[ramo102]

Hi all,

I'm trying to build a Roo project using a DB2 database.

I successfully launched the following commands:

roo> project --topLevelPackage com.example
roo> persistence setup --provider HIBERNATE --database DB2_EXPRESS_C --hostName <host> --databaseName <db> --userName db2admin --password <pwd>
roo> reverse engineer --schema <my_schema> --includeTables <single_table> --includeNonPortableAttributes --testAutomatically

But it gives an error when performing tests:

roo> perform tests
...
Tests run: 7, Failures: 0, Errors: 6, Skipped: 0
roo> 
[INFO] ------------------------------------------------------------------------
[ERROR] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] There are test failures.
...

Log:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.1.v20110722-r9776): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=MY_SCHEMA.SEQUENCE, DRIVER=4.12.5
5
Error Code: -204
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
        bind => [2 parameters bound]
Query: DataModifyQuery(name="SEQUENCE" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
        at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:798)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:864)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:583)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:526)
        at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1729)
        at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:234)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelectCall(DatasourceCallQueryMechanism.java:236)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelect(DatasourceCallQueryMechanism.java:216)
        at org.eclipse.persistence.queries.DataModifyQuery.executeDatabaseQuery(DataModifyQuery.java:85)
        at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:810)

The primary key definition is:

CREATE TABLE MY_SCHEMA.MY_TABLE (
  MY_ID_ROW	DECIMAL(11, 0)	NOT NULL,

ALTER TABLE MY_SCHEMA.MY_TABLE
  ADD CONSTRAINT MY_ID_ROW_PKEY PRIMARY KEY
    (MY_ID_ROW);

As far as I know, my schema does not have a SEQUENCE table, but SEQUENCE is searched by the <MY_TABLE>IntegrationTest.java test.

I obtain the same error when, after having created the web tier, I run the application (mvn tomcat:run) and I try to list the records of my table.

Thanks in advance for your tips.

[jlic]

That's interesting, from the hibernate source there doesn't seems to be anything mapped directly to db2 express c version. I am curious what if u just set the provider to db2 instead? Derby is a light weight DB2-ish clone in java by the way so it might be a good alternative for you, since it has a direct Hibernate dialect mapping.

[Alan Stewart]

Hibernate creates the sequence table, however it is probably prevented by doing so by the persistence.xml setting hibernate.hbm2ddl.auto which is set to validate after a database reverse engineer command. This is so to protect production data from being dropped. Change it to create and it should work.

[ramo102]

Hibernate creates the sequence table, however it is probably prevented by doing so by the persistence.xml setting hibernate.hbm2ddl.auto which is set to validate after a database reverse engineer command. This is so to protect production data from being dropped. Change it to create and it should work.

Hi Alan,

it gives me the same error using ECLIPSELINK as the database provider.
However, as you said, tests succeed changing the "eclipselink.ddl-generation" property to "create" within the persistence.xml file:

<persistence-unit name="persistenceUnit" transaction-type="RESOURCE_LOCAL">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <properties>
            <property name="eclipselink.target-database" value="org.eclipse.persistence.platform.database.DB2Platform"/>
            <!-- value="drop-and-create-tables" to build a new database on each run; value="create-tables" creates new tables if needed; value="none" makes no changes to the database -->
            <property name="eclipselink.ddl-generation" value="create-tables"/>
            <property name="eclipselink.ddl-generation.output-mode" value="database"/>
            <property name="eclipselink.weaving" value="static"/>
        </properties>
    </persistence-unit>

Now I have an error in testPersist(), it gives me "Exception occurred during BigDecimal conversion".
But this is another story.