Good evening group, I have to insert some data into a table…is there a quick way to retrieve the last ID inserted?
Dim RecordTEMPORANEO1 As New DatabaseRow
RecordTEMPORANEO1.Column("NomeDitta") = txtfield1(3).text
db.AddRow("NominativiDitte", RecordTEMPORANEO1)I use, sqllite database.
The much cleaner alternative that they want us to forget is var id as Integer = SQLiteDB.LastRowID
They removed that from the docs mighty quickly compared to their other “deprecations”
Personally I do this:
reg = db.SelectSQL ("insert into mytable (col1, col2) values (?1, ?2) returning id", val1, val2)
id = reg.Column("absid").IntegerValue
.
where id is the integer primary key for that table. So I’m not that bothered about .LastRowID
“returning” is only for postgres ?
And sqlite.
Thanks to all for your attention. I solved it with TIm’s suggestion.
I didn’t know that in sqllite you could use LastIndex. Thanks.
Be aware that LastRowID is deprecated.
Yeah but like everything else, it won’t be removed. And if, out of spite, they do remove it - it’s an easy update, though it will make your code less simple.
How quickly they shuffled it under the rug indicates to me it was, like the docs being nuked, for someone’s vanity.
It’s a significantly more self-documenting design.
I already changed my code to do without it. But I couldn’t see what the point was in deprecating it - it’s clearly useful. And my fix using RETURNING only works, as far as I know, for postgres and SQLite.
Edited to be less snarky: There was no useful reason that I could find either.
Aye - but that’s not a GOOD reason though - IMO.
I was just playing with this. I’ve been using this extends method since it was deprecated and hidden:
Public Function LastRowID(extends db as SQLiteDatabase) As Integer
var rs as RowSet = db.SelectSQL( "SELECT last_insert_rowid();" )
if rs <> nil and rs.AfterLastRow = False then Return rs.ColumnAt(0).IntegerValue
raise new DatabaseException( "Unable to retrieve last inserted row ID." )
Return -1
End Function
It used to work (otherwise I wouldn’t have been able to test it) but now I find that the framework doesn’t execute my extends method. It uses the hidden and deprecated function. Super fun.
If you are not using integer primary keys - I use UUIDs - then none of these options will work. Some database engines such as PostgreSQL and SQLite support the RETURNING clause:
Var Rows As RowSet = Database.SelectSQL("INSERT INTO table (column1, column2) VALUES (?1, ?2) RETURNING pk;", "value1", "value2")
You’ll get back a RowSet with the requested columns.
Prone to errors. The last ID inserted in a transaction must be returned right away in a atomic operation, querying it separately may cause you getting a wrong value due to some background operation in between, e.g. you operate on some record and id, some background/event operation occurs in other id right away, you request of last ID in the foreground thread getting an unexpected value.
The only database plugin where the source code is available to us is MySQL (this thread is about SQLite). You can’t say with any kind of authority that this new function is atomic and the old one is not.
Edit: Additionally, Anthony’s findings seem to suggest what you say isn’t even true. The plugin could very well just be calling LastRowID as a separate call within itself and returning it. All we can do is guess how it works unless a staff member wants to enlighten us.
LastRowId is obviously non-atomic, it’s at Xojo level, after db operations, and prone to user misuses.
I can prove it, observe this forced side effect coming from the background:
lastid.zip (4.6 KB)
This test is YOU breaking the atomicity. You have no way to re-create this test using the new as Integer function. You need to prove that the as Integer function isn’t just calling
db.ExecuteSQL("INSERT INTO id_colapse VALUES (?); ", id) // id 1xxxx created
return db.LastRowId
Which would be exactly the same way any of us use LastRowID.
Well, atomicity is something you don’t break. Or you have it, or not.
The theory says you can have a wrong LastRowId in your 2 line sample too. All that is needed is a preemptive thread coincidentally interrupting in between those 2 lines.
But you can’t prove that the new function isn’t exactly as I described. That is the whole point of this back and forth.
You fathomed, out of nowhere, that the new function was something better than Xojo just hiding LastRowID and returning it.
It would be nice to see functional upgrades. The big point here is that you cannot assert with authority things you’ve made up.