getGeneratedKeys() come back

[prrvchr]

Hi all,

It seems that you are determined not to implement the getGeneratedKeys() method.
I don't really understand what prevents this from happening.

If you want to use mass inserts with the command INSERT INTO mytable (Col1, Col2) VALUES (data1, data2), (data1, data2), ... this is the only method that allows you to recover the primary keys generated automatically in a single pass.

Thanks for your help.

[gotson]

there is no such support in SQLite to retrieve multiple generated values. You can use a RETURNING clause if you want to get multiple IDs.

[prrvchr]

Hi gotson,

This is what I understood, but I will have to do this transparently since my end user is LibreOffice Base and he only knows getGeneratedKeys() (in fact it follows the JDBC API)

So as I am obliged to follow the API that Base uses, here is what I am preparing to do:

• when calling executeUpdate() if the option is set to java.statement.RETURN_GENERATED_KEYS I add (if the SQL command starts with INSERT ) the RETURNING * clause. I keep in cache the resultset that this command will return and returns 0 (or the number of lines affected if possible).
• When calling the getGeneratedKeys() method I deliver the cached resultset.

If this works, wouldn't it be smarter to integrate this into sqlite-jdbc to make the use of its API fully compatible?

[gotson]

If this works, wouldn't it be smarter to integrate this into sqlite-jdbc to make the use of its API fully compatible?

feel free to send a PR

[prrvchr]

Here is a possible implementation of getGeneratedKeys().
I was not able to test because I cannot compile, but I am ready to test if I have an archive available.

[prrvchr]

Can anyone give me instructions on how to compile?
Thanks.

[prrvchr]

Well I have a version which integrates the modifications and which remains to be tested. For now everything is working fine...
I understand that editing the SQL commands in the sqlite-jdbc driver may seem a bit messy, but in fact I am convinced that this is the right way to do it because:

• It makes the driver fully compatible with JDBC.
• Normally we are able to obtain the desired columns in response to an insert, I have not yet tested...
• This does not prevent the old way of working (select last_insert_id) which by the way was already doing a bit of the same thing.
• Although this correction seems a bit simplistic, I think this will prove particularly robust, especially if we follow the JDBC specifications to the letter (a single SQL command per call to executeUpdate(), etc...)
• And the icing on the cake: this will allow massive insert (1000 records per two SQL commands is possible while the old implementation required 2000 SQL queries).

The archive if you want to test: sqlite-jdbc-3.45.1.2-SNAPSHOT.jar

[prrvchr]

Well, I produced a version that allows you to follow the JDBC specifications to the letter, but @gotson doesn't seem ready to integrate it.

I believe that it refuses to modify the SQL requests that the driver processes even though this is the only way to follow the JDBC specifications. It's a shame.

For the moment a version is available on my fork of sqlite-jdbc.

[prrvchr]

I have just updated sqlite-jdbc-3.45.1.3-SNAPSHOT.jar to comply with JDBC 4.1, namely:

• The java.sql.DatabaseMetaData.supportsGetGeneratedKeys() method answers true.
• The java.sql.DatabaseMetaData.generatedKeyAlwaysReturned() method answers true.
• The java.sql.Statement.executeUpdate(String sql, int autoGeneratedKeys) method works as follows:
  • If autoGeneratedKeys is equal to java.sql.Statement.RETURN_GENERATED_KEYS and the SQL command begins with INSERT or REPLACE and it only includes one SQL command then the records affected by the SQL command will be accessible by the java.sql.Statement.getGeneratedKeys() method (all columns in the table will be returned).
  • Otherwise the SQL command will be executed without influencing the operation of getGenerateKeys() which allows you to interweave queries before having to retrieve the records using getGeneratedKeys()...
• The java.sql.Statement.executeLargeUpdate(String sql, int autoGeneratedKeys) method works like the previous one...
• The java.sql.Statement.executeUpdate(String sql, String[] columnNames) method allows you to get only the requested columnNames in getGeneratedKeys().
• The java.sql.Statement.executeLargeUpdate(String sql, String[] columnNames) method works like the previous one...
• The java.sql.Statement.executeUpdate(String sql, int[] columnIndex) method is not supported.
• The java.sql.Statement.executeLargeUpdate(String sql, int[] columnIndex) method is not supported.
• The java.sql.Statement.execute(String sql, int autoGeneratedKeys) method is not supported (you need to use executeUpdate() then getGenratedKeys() methods).
• The java.sql.Statement.execute(String sql, String[] columnNames) method is not supported (you need to use executeUpdate() then getGenratedKeys() methods).
• The java.sql.Statement.execute(String sql, int[] columnIndex) method is not supported.
• The java.sql.Connection.prepareStatement(String sql, int autoGeneratedKeys) method work same as executeUpdate(String sql, int autoGeneratedKeys) concerning the use of getGeneratedKeys() which must however be accessed after each call to the executeUpdate() or executeLargeUpdate() method.
• The java.sql.Connection.prepareStatement(String sql, String[] columnNames) method work same as executeUpdate(String sql, String[] columnNames) concerning the use of getGeneratedKeys() which must however be accessed after each call to the executeUpdate() or executeLargeUpdate() method.
• The java.sql.Connection.prepareStatement(String sql, int[] columnIndex) method is not supported.

I would like to point out to you that the new operating mode of this driver is not at all the same as that used in the gotson driver. It probably requires rewriting your programs but it follows JDBC 4.1 specifications.

And finally I hope that @gotson will change his mind about integrating this PR because I don't really want to maintain sqlite-jdbc...