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.

[gotson]

@b3nn0 in the next major version the driver will not support anymore getGeneratedKeys, as there is no reliable way to retrieve those. The good way would be to use the SQLite RETURNING clause.

[b3nn0]

Alright, makes sense. Closing this then.

[gotson]

This will be closed automatically once merged

[github-actions]

🎉 This issue has been resolved in 3.43.0.0 (Release Notes)

[sproket]

How is removing a feature resolved? Anyway it breaks Persism but I'm doing it the new way for SQLite and it's working so I'll put out an update soon.

[barrycaceres]

If I am reading this correctly, the ability to obtain the generated keys has been removed entirely with no "proper" way to do it?

Those of us who were managing our connections and atomically doing single inserts and then immediately obtaining the generated keys were having no problem with the existing functionality because we understood that using it meant doing a single insert and before letting any other thread get your Connection object you had to get the generated key.

Simply removing this functionality because it did not work for those who did not understand how to do it safely seems like a non-fix but like crippling access to SQLite via JDBC.

[barrycaceres]

How is removing a feature resolved? Anyway it breaks Persism but I'm doing it the new way for SQLite and it's working so I'll put out an update soon.

BINGO! You are so right. Somebody dropped the ball here.