Re: h2 with Eclipselink and sequences

[Christoph Läubrich]

Do you use annotations? Schema generation?

I use H2 with eclipse link and the following annotation for id:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)

without any problem (schema is generated by eclipse link)

[Noel Grandin]

On 2013-02-22 15:55, Dries wrote:

I'm able to make that statement work on the correct tables and fields in h2, but h2 doesn't allow changes to meta tables, so that doesn't solve the problem either.

login.getPlatform().setSequenceCounterFieldName("CURRENT_VALUE");

login.getPlatform().setSequenceNameFieldName("SEQUENCE_NAME");

login.getPlatform().setSequenceTableName("INFORMATION_SCHEMA.SEQUENCES");

login.getPlatform().setSequencePreallocationSize(1);

[EL Fine]: 2013-02-22 14:52:15.602--ClientSession(1205789719)--Connection(1834774659)--Thread(Thread[main,5,main])--UPDATE INFORMATION_SCHEMA.SEQUENCES SET CURRENT_VALUE = CURRENT_VALUE + ? WHERE SEQUENCE_NAME = ?

bind => [1, SYSTEM_SEQUENCE_1E71BBBC_0444_4E1D_A3A5_DD4483BD30EE]

That statement is not allowed because it updates meta tables.

Why are you trying to update meta tables instead of using standard SQL to modify the sequence?

http://h2database.com/html/grammar.html#alter_sequence

[Dries]

I don't use Schema Generation by eclipselink, the tables are created in h2 directly.  That I think is the reason h2 isn't able to link the sequence name to the auto_increment field.

[Dries]

Because, once I enable my sessionCustomizer class, eclipselink no longer uses the correct statement (CALL NEXT VALUE FOR), but instead uses : 

[EL Fine]: 2013-02-22 14:50:01.291--ClientSession(2052114141)--Connection(1340006540)--Thread(Thread[main,5,main])--UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?

bind => [50, SYSTEM_SEQUENCE_1E71BBBC_0444_4E1D_A3A5_DD4483BD30EE]

All I changed in the session Customizer class was setSequenceNumberName.

I came up with a workaround where I now, instead of using auto increment when creating the tables, I createmy own sequences and set the default value of the column to :

"DEFAULT (NEXT VALUE FOR PUBLIC.MY_SEQUENCE_TABNAME ) NOT NULL NULL_TO_DEFAULT SEQUENCE PUBLIC.MY_SEQUENCE_TABNAME";

Then in my persistence entity classes, I set the sequence name.

@GeneratedValue(strategy = GenerationType.AUTO, generator="GEN_TABNAME")

@SequenceGenerator(name = "GEN_TABNAME", sequenceName = "MY_SEQUENCE_TABNAME", allocationSize = 1)

It's not ideal, I would still prefer a way where eclipselink figures out the sequence name of an auto_increment field and uses that correctly, but it works.

Thanks,

Droes

[Noel Grandin]

Yeah, at this point you're probably better off asking on the Eclipselink mailing list.

Weird, I would have thought H2 would be well supported because Eclipse uses it internally.