SQLite Return ID of record inserted

Databases
1

I thought I read recently that it is possible to get the auto_incremented id of a record inserted, using SQLSelect and adding something like ‘RETURNING ID’. No I can’t find it anywhere. Probably have some detail wrong, or I just dreamed it.

At any rate what is the easiest way to get the ID of the newly inserted record?

I realize I could do,

SELECT max(ID) FROM table

but this could return a record added by another user in the meantime.

2

Ah found it. Sadly it was PostgreSQL only.
https://forum.xojo.com/40775-moving-from-sqlite-to-postgres/p1#p332232

3

For SQLite you can use last_insert_rowid()

Use a transaction for your INSERT:

BEGIN; INSERT INTO (your insert here); SELECT last_insert_rowid(); COMMIT;

Because of the transaction , this will return the ID of the row you inserted, instead of someone elses.

4

The sqlitedatabase class provides that for you automatically in LastRowID.

1 Like