Proper / secure way to receive last_insert_rowid

[b3nn0]

Hi, right now there are multiple ways to retrieve the last inserted rowid of an insert. None of them is very good.

• Query for "SELECT last_insertrowid()": -> Doesn't work in multithreaded applications

• Add the select statement directly behind the INSERT query, e.g. "INSERT INTO a VALUES('x'); SELECT last_insert_rowid()" -> Doesn't work when the insert is a prepared statement. SQLite will only respect the first statement.

• Call stmt.getGeneratedKeys() -> Just as unreliable. It seems to do a lazy call to last_insert_rowid(), which is, in my opinion, a bug.

As SqliteJdbc seems to synchronize everything going to SQLite, it would be quite easy to fix. To do so, the library simply would need to call sqlite3_last_insert_rowid() right after the query. JDBC already provides the API, see Statement.RETURN_GENERATED_KEYS constant.

Implementing it should be quite simple... I could potentially provide a patch, if it has the chance to be accepted.

[gotson]

~~Is this still happening on the latest version?~~

[gotson]

I was recently working to fix #330 and i stumbled upon this.

• Call stmt.getGeneratedKeys() -> Just as unreliable. It seems to do a lazy call to last_insert_rowid(), which is, in my opinion, a bug.

I agree with you on this, the generated keys should be related to the Statement.

As SqliteJdbc seems to synchronize everything going to SQLite, it would be quite easy to fix. To do so, the library simply would need to call sqlite3_last_insert_rowid() right after the query.

This is not as simple though, as you can insert multiple values, and getGeneratedKeys should return all of them, not just the last one. This has been raised in #468

I see a few possible options:

1. disable support for retrieving generated keys entirely. That would imply having DatabaseMetaData#supportsGetGeneratedKeys() returning false.
2. add a RETURNING clause when generated keys are requested, in order to get what we need. I'm not sure what should happen when the application passes a query already containing a RETURNING clause though.

[gotson]

I've been thinking about this, and I think option 2 would require to have a sql parser. We would need to be able to find out whether there is already a RETURNING clause, and also to find out what's the table name so that we can infer the generated keys too.