IDENTITY in H2 2.0.202 and later is removed in some cases; H2Platform may need to be changed

[ljnelson]

I just ran across this and didn't want it to get lost. I'm not sure what the proper solution is.

H2 changed certain details about IDENTITY in their grammar.

A quick search shows me it is still used here:

https://github.com/eclipse-ee4j/eclipselink/blob/3e2c7e55986794d23c83c2b0cb9bbf9103af04f7/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/platform/database/H2Platform.java#L143-L155

Maybe that is OK, and maybe it is not but I suspect there will be problems here.

[katzyn]

printFieldIdentityClause() is implemented incorrectly, it should return " GENERATED BY DEFAULT AS IDENTITY" for all versions of H2 unconditionally. This change is trivial.

Situation with buildSelectQueryForIdentity() is complicated. IDENTITY() is obliviously a wrong function. It doesn't actually return a generated identity value from the last INSERT operation executed by application in this session. If table has a trigger and that trigger performs, for example, an another INSERT operation in some different table with identity column, a value of that identity column will be returned instead.

For H2 1.x SCOPE_IDENTITY() can be used, it doesn't return unexpected values from triggers.

H2 2.x doesn't have these functions. There are only two ways to get the inserted values:

1. Statement.getGeneratedKeys() (collection of keys needs to be enabled first by providing names or ordinal number of columns or simply with Statement.RETURN_GENERATED_KEYS).
2. Standard data change delta tables (SELECT ID FROM FINAL TABLE (INSERT INTO TEST(A, B) VALUES (?, ?))).

Hibernate ORM uses the first method with all drivers that support that. From my point of view EclipseLink should do the same instead of tricks with various vendor-specific functions.

We added these weird legacy functions to LEGACY compatibility mode in H2 2.1.210, but this mode should be considered only as a temporary workaround. In this version both IDENTITY() and SCOPE_IDENTITY() work like SCOPE_IDENTITY() in H2 1.x.

[MelleD]

@katzyn is there something similar like in SQL SERVER SELECT @@IDENTITY for H2 2.1.210?

[dazey3]

Seems like the same issue I am attempting to look into for SQLServer: https://bugs.eclipse.org/bugs/show_bug.cgi?id=579409

[MelleD]

Here is another one https://bugs.eclipse.org/bugs/show_bug.cgi?id=578086

@dazey3 i'am currently using SQL Server with SELECT @@IDENTITY. This works for me, but of course the extra select is not very efficiency as returning the Generated Keys directly.

[katzyn]

@katzyn is there something similar like in SQL SERVER SELECT @@IDENTITY for H2 2.1.210?

It depends on compatibility mode of H2, in some modes there are various vendor-specific functions, in others, including the default Regular mode, there is no normal way to figure out what was produced by previous commands. You need to request collection of all values you need in insert command directly with one of methods described in my previous comment.

From my point of view, EclipseLink shoud use generated keys functionality from JDBC for all drivers that support it properly, like Hibernate ORM does.

[dazey3]

@katzyn That's probably the correct strategy, but EclipseLink is rather committed to the 2 query process. It'll take a rewrite to add that functionality.

[MelleD]

@katzyn is there something similar like in SQL SERVER SELECT @@IDENTITY for H2 2.1.210?

It depends on compatibility mode of H2, in some modes there are various vendor-specific functions, in others, including the default Regular mode, there is no normal way to figure out what was produced by previous commands. You need to request collection of all values you need in insert command directly with one of methods described in my previous comment.

I'am currently using SQL Server compatibility mode. Do you know is there something what i can use for workaround, a function or select?

@katzyn is there something similar like in SQL SERVER SELECT @@IDENTITY for H2 2.1.210?

From my point of view, EclipseLink shoud use generated keys functionality from JDBC for all drivers that support it properly, like Hibernate ORM does.

Yes of course totally agree.

[lukasj]

but EclipseLink is rather committed to the 2 query process

not exactly true. There are currently 4 ways (and some with alternatives) to get the sequence number, for identity, NoPreallocation_State looks like the right one either to use or alter. The 2 query process is often for preallocation of a vector of IDs, so in the end there are N+1 queries for N inserts

[dazey3]

@lukasj Unfortunately, the process to obtain the sequence number is decoupled from the process that handles the INSERT. EclipseLink first performs the INSERT, then once that is complete, the process of building the Entity object travels down the org.eclipse.persistence.sequencing API to obtain the ID value (org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.updateObjectAndRowWithSequenceNumber()). That is what I mean by "2 query process". I think the branching occurs here: org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject()

In order to use Statement.RETURN_GENERATED_KEYS, which I agree looks like the correct strategy, we need to have the INSERT java.sql.Statement object be accessible and open (it gets closed once the INSERT is complete) so that we can call java.sql.Statement.getGeneratedKeys() on it. Alternatively, we need to know that the generated key should be obtained and cached for later during the INSERT.

There is some precedence in the code for these processes (cursors and resultset pagination) and that is something I am looking into. I am investigating if org.eclipse.persistence.descriptors.ReturningPolicy may be able to be used here as well (org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.updateObjectAndRowWithReturnRow())

[katzyn]

I'am currently using SQL Server compatibility mode. Do you know is there something what i can use for workaround, a function or select?

SELECT SCOPE_IDENTITY();

(It works with the real MS SQL Server too.)

[MelleD]

I will try that with the new H2, thanks.

EDIT: @katzyn worked, but the LIMIT/OFFSET syntax changed as well and is not working with EclipseLink and new H2