SQLite sqlite3_last_insert_rowid()

Databases
1

Hello all,
Does anyone know how to implement the SQLite command to get the last inserted row id ?

sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*);

Thanks
Tim

2

Why?
there is SQLiteDatabase.LastRowID:
http://documentation.xojo.com/index.php/SQLiteDatabase.LastRowID

3

Hi Christian,
Thank you for the reply!

I had not seen that before reviewing your post. However is there a way to do that for a specific table? Or must it be obtained, then saved on a table by table basis, only when an insert occurs?

Tim

4

Simply query right after each insert.

5

does the LastRowID work if the database is not SQLite but a CubeSQLServer?

6

This is the way I do it:

Dim reqSQL As String
Dim recSet As RecordSet
Dim theCounter As int64
reqSQL = “SELECT seq FROM sqlite_sequence WHERE name = ‘aTypeOfData’;”
recSet = bdGeo.SQLSelect(reqSQL)
if recSet <> Nil then ’ recSet.MoveFirst (useless; only one row can be return)
theCounter = recSet.IdxField(1).IntegerValue ’ other way: theCounter = recSet.Field(“seq”).IntegerValue
recSet.Close
end if

Be aware that the you must declare aTypeOfData like this in SQLite:
aTypeOfData INTEGER PRIMARY KEY AUTOINCREMENT
the AUTOINCREMENT clause is mandatory or SQLite wont add a row with name ‘aTypeOfData’ in the table sqlite_sequence.
…
you’re welcome :wink:
jjc_Mtl

7

I know this is about SQLite, but I just want to mention that PostgreSQL implements a RETURNING extension, so you can do something like this:

sql = "INSERT INTO table (cols) VALUES (values) RETURNING id"
dim rs as RecordSet = db.SQLSelect( sql )
dim id as integer = rs.IdxField( 1 )

If you insert multiple records, you can cycle through the RecordSet for all the IDs that were inserted. It will work for DELETE and UPDATE too, and you can return “*” to get the entire record instead of just one column.

8

Witch wise is not save.

9

Why?
In your Sqlite session, after the insert, the LastRowID for the insert is stored in your session, so if you query it, you get the correct one.

10

Sorry but I disagree (SQLite disagree should I say)
rowID send by LastRowID is not safe with joints. See
http://www.sqlite.org/foreignkeys.html
“The parent key is the column or set of columns in the parent table that the foreign key constraint refers to. This is normally, but not always, the primary key of the parent table. The parent key must be a named column or columns in the parent table, not the rowid.”

That’s why I use the method posted sooner.
jjc_Mtl