H2: getGeneratedKeys returns only the last id limitation.

[iJava]

H2 is really very nice DB and I wanted to use it. It does everything I need except one very basic, very important thing - when using multiple insert

getGeneratedKeys returns onlt the last id. To tell the truth I can't understand why such basic feature
is not implemented as it must to be. For my CRUD application is've very serious limitation. 
Can this problem be solved? Do you plan to fix this limitation? If yes, then when?

[Thomas Mueller]

Hi,

Sorry, there is currently no plan to support this.

It does everything I need except one very basic, very important thing - when using multiple insert

I assume multiple inserts would generate incrementing ids, without gaps? If yes, you could calculate the old ids from the latest. If not, how does your insert look like?

> one very basic, very important 

If it would be very basic, I would assume other people reported the problem before you. So I guess it's not very basic and / or not very important :-)

Regards,

Thomas

[iJava]

Thank you very much for your answer. Yes, I insert without gaps. Yes, I can calculate from last ID and and in reverse order to find out all the previous id.

However, this solution will work only with two conditions:
1)When H2 is a server and there are other clients who make inserts but my multiple insert 100% will have ids without gaps.
Example I insert four elements. The following is ok: 4,5,6,7. The following example is not ok:4,6,7,8 - another client inserted with id=5.
2) The order of elements in multiple insert query  = the order sql engine insert them in DB.

Could you comment these two conditions?

[Thomas Mueller]

Hi,

Hm, I think you are right. With autocommit enabled, I think there could be gaps.

Of course there are workarounds, for example using a single statement of the form "insert into ... values (?, ?), (?, ?), (?, ?)". For H2, that would be faster than using batch inserts, and it is one transaction. Or disabling autocommit.

But for batch inserts (addBatch / executeBatch), with autocommit, a change would be needed. Patches are welcome!

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[iJava]

Ok. Will the following code guarantee 100% that there won't be any id gaps when H2 is server (there are other clients)?

            Connection conn= getting connection from pool
            conn.setAutoCommit(false); 
            ps = conn.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);           
            for (Answer answer:list) {
                ps.setInt(1, answer.getTaskId());
                ps.setString(2, answer.getText());
                ps.setInt(3, answer.getOrder());
                if (answer.getGroupKey()!=null){
                    ps.setString(4,answer.getGroupKey());
                }else{
                    ps.setNull(4, java.sql.Types.VARCHAR);
                }
                ps.setInt(5, answer.getScore());
                ps.addBatch();
            }
            int[]temp=ps.executeBatch();
            conn.commit();
            conn.setAutoCommit(true); 
            conn.close();

[Thomas Mueller]

Hi,

With autocommit disabled, I think there should be no gaps, but now thinking about it again I'm not completely sure. It's probably best if you test it.

Regards,

Thomas

On Friday, June 12, 2015, iJava <pavelka...@gmail.com> wrote:

[iJava]

Ok. This feature is really very important for me.  I will try to implement it myself. Could you just point me where I should look at in order not to look through all the code.

[Noel Grandin]

On 2015-06-13 08:57 AM, iJava wrote:
> Ok. This feature is really very important for me. I will try to implement it myself. Could you just point me where I
> should look at in order not to look through all the code.
>

org.h2.jdbc.JdbcConnection#getGeneratedKeys

which calls the SCOPE_IDENTITY function:
http://h2database.com/html/functions.html#scope_identity

which is implemented in org.h2.expression.Function line 907 by calling
org.h2.engine.Session#getLastScopeIdentity

which is where you will have to somehow store an array of values.

You will probably need to implement a new SQL function, because we can't mess with the existing one

[Thomas Mueller]

Hi,

The JdbcPreparedStatement.executeBatch method would need to be changed quite a bit. That means, the client-server protocol needs to be changed, which requires a new Constants.TCP_PROTOCOL_VERSION_16. That way, both the client and the server are backward compatible.

Regards,

Thomas