Exception when generating sequence using Eclipselink and h2 in test environment

Question

I'm using h2 to setup an integration test for an application using Eclipselink, Arquillian and Weblogic 12.1.1

This is my entity:

@Entity
@Table(name = "AFIS_REQUEST")
public class Request implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "requestSEQ")
    @SequenceGenerator(name = "requestSEQ", sequenceName = "REQUEST_ID_SEQ", allocationSize = 1)
    @Column(name = "ID")
    private Long id;
...
}

And this is my persistence.xml

<persistence-unit name="myPU" transaction-type="JTA">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <jta-data-source>jdbc/arquillian</jta-data-source>
        <class>com.my.Request</class>
        <exclude-unlisted-classes>true</exclude-unlisted-classes>
        <validation-mode>NONE</validation-mode>
        <properties>
            <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/>            
            <property name="eclipselink.logging.level.sql" value="FINEST"/>
            <property name="eclipselink.logging.parameters" value="true"/>
            <property name="eclipselink.target-database" value="org.eclipse.persistence.platform.database.H2Platform"/>
        </properties>
    </persistence-unit>

And here is my JDBC URL and Driver class specified in Weblogic data source:

• jdbc:h2:file:target/databases/h2/db
• org.h2.jdbcx.JdbcDataSource

But when I start my test I get following exception:

<Dec 3, 2012 3:58:23 PM IRST> <Warning> <EclipseLink> <BEA-2005000> <2012-12-03 15:58:23.217--ServerSession(16406343)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLException: Sequence "REQUEST_ID_SEQ" not found; SQL statement:
CALL NEXT VALUE FOR REQUEST_ID_SEQ [90036-166]
Error Code: 90036
Call: CALL NEXT VALUE FOR REQUEST_ID_SEQ
Query: ValueReadQuery(sql="CALL NEXT VALUE FOR REQUEST_ID_SEQ")> 

Because I want the table to be created when starting the tests and to be dropped when finishing the tests I have assigned drop-and-create-tables to eclipselink.ddl-generation property. But it seems that Table is generated but it's sequence is not created by Eclipselink.

Does h2 support generating sequences? or am i missing any configuration?

EDIT: I checked the generated DDL and it shows that the create sequence statement is generated.

CREATE TABLE REQUEST (ID BIGINT NOT NULL, ... , PRIMARY KEY (ID))
CREATE SEQUENCE REQUEST_ID_SEQ START WITH 1

Then I connected to generated database using H2 web console and saw that sequences are generated and I could execute CALL NEXT VALUE FOR REQUEST_ID_SEQ successfully there!

So why Eclipselink is complaining about it!? Sequence "REQUEST_ID_SEQ" not found;

Answer 1

I had the same problem and i managed to get this working, just add this line to your persistence file :

<property name="eclipselink.target-database" value="org.eclipse.persistence.platform.database.H2Platform"/>

It MUST be the first property of <properties>.

Note that you will still have the warning.

I found the solution here