generated keys are "cleared" inside of Session during INSERT (to the table with AUTOINCREMENT column) when there is a trigger (Java code, obviously) that does another INSERT (to different table with again AUTOINCREMENT columns)

[Vitali]

Autoincrement primary key column in H2 causes allocation of new value during execution of INSERT.  Normally these values should be returned from  Statement.getGeneratedKeys().  Somehow this API works fine with H2.196.  In latest code some major refactoring was done  using new class in H2 - GeneratedKeys.  Now there is a possibility that  generated keys collected in GeneratedKeys data structure that is held by Session during INSERT execution  are GONE, if during INSERT there is a trigger that does another INSERT...

    public void setCurrentCommand(Command command, Object generatedKeysRequest) {
        this.currentCommand = command;
        // Preserve generated keys in case of a new query due to possible nested
        // queries in update
        if (command != null && !command.isQuery()) {
            getGeneratedKeys().clear(generatedKeysRequest);
        }

This occurs inside of Session.setCurrentCommand...

I suppose this

        // Preserve generated keys in case of a new query due to possible nested
        // queries in update

is TODO?







I see that it could be solved by moving responsibility to maintain GeneratedKeys data structure directly to Command/CommandContainer  objects..  So, then nesting of queries (in a form of stack) won't clear this state in "shared"  Session.





Each  command (like INSERT) then is able to return its generated keys properly.










[Evgenij Ryazanov]

Hello.

No, it's not a TODO. It's a comment why the conditions below are necessary. Nested DML commands weren't not supported.

However, the current implementation is entirely different and hypothetically it should work in your use case.

You can build H2 from the sources and test it with your application.

Current sources are available on GitHub:

https://github.com/h2database/h2database

Building instructions are here:

https://h2database.com/html/build.html#building

You need a jar target.

[Vitali]

I was working with codebase of few months old where described issue was faced.  As we are maintaining some large projects with H2 as spatial embedded database , we build customized releases from source code from time to time.  I took latest codebase  and tested in the morning.  I should acknowledge that our unit tests  are not issuing anymore problems with generated keys (seems JDBC API in underlying case properly returns them).  Haven't yet checked more deeply (by debugging), but it seems the case is resolved with latest codebase.