I proved the point, I explained why, I explained the possible academic reasons Xojo deprecated it. If with all that said, you still want to think whatever you want, its ok for me. I won’t change that.
CubeSQLServer has a custom command SHOW LASTROWID, which according to the CubeSQL Commands Reference:
SHOW LASTROWID returns the last rowid added to the current database as a RecordSet.
This command is equivalent to the sqlite3_last_insert_rowid API, so it returns the returns
the rowid of the most recent successful INSERT into the database from the current client
connection. If no successful INSERTs have ever occurred on that database connection,
zero is returned.
And I’m pretty sure, Xojo’s SQLiteDatabase does exactly the same with db.LastRowId. Such as @Tim_Parnell said, we’d need to see the source to know for sure.
That’s not proof, but bad design. Obviously this breaks.
You’re only having one instance of the Database / Connection.
And you’re doing things in both Main Thread and a (cooperative) Thread.
So of course they “mix”… that’s expected.
Thread.SleepCurrent(300) // Something busy here
// Thread may insert here, so the LastRowID would be the one inserted by the Thread -> expected!
// Why? Because only 1 (shared) DB instance
Var id10000 As Integer = db.LastRowId
Do this with a .sqlite file, and have two Database Instances. One for the Main Thread, one for the Background Thread. And I’m pretty sure it’ll work as it should.
1 Like
It’s a prove using a bad design as I said in the code. “forced condition”, a change from the background. Users fall in the pitfall because they don’t know the subtle things that occur. I chose an easy way just to demonstrate. The real case would use preemptive code and would be much more random.
They do fall into traps with many things (I don’t say App.DoEvents now 
).
Still no reason to me to just “hide/deprecate” such a functionality.
1 Like
Are there equivalents in other languages with preemptive threads or asyncs? If yes I promise to investigate those languages. If Xojo deprecated it, it is just to force people use a “more proper” way from that point on. The removal, if one comes, is very far away.
One would most likely use the the sqlite3_last_insert_rowid API…
This is SQL! It’s completely out of context. We are talking about the language calling the SQL. At the SQL level it’s ok, we control the atomicity there in a transaction that can do many things at once including returning some Lastid. People above gave examples using the returning clause.
Well, about any other language lets you use the SQLite API.
So where’s the difference in calling it directly, or (since it’s Xojo which doesn’t allow that) have a wrapper method calling it?
It does exactly what it’s API describes (and yes, there are pitfalls).
Otherwise one would probably use the INSERT INTO ... RETURNING way - to get it “right away”.
And Xojo seems to have provided this with that (just a pity if one is not using Integer Primary Keys):
This item was deprecated in version 2024r2. Please use the Database.AddRow method signature that returns a rowid as a replacement.
The .LastRowID does the same (if used correctly), but serves other purpose/situations, too (one might want to know at any time what the last RowID is that one has inserted).
It just needs the proper documentation… and it feels strange to me to have it “invisible/deprecated”.
That’s one usual way. There are others on more complex SQL servers able to run stored procedures.
Other way is using sequencers, you get the ID before using it, and you control its use at your level.
I think that there are design docs (old stuff I don’t remember now) explaining why a design like Xojo exposed is a bad design, and maybe that was the cause of them concealing it.
No it doesn’t. The AddRow (INSERT RETURNING) is atomic. Insert and get the value at once without a chance of a change between the insert and the read of lastRowId() receiving a wrong value
My 2 cents are: learn the best practices used today instead of trying to fight them for no reason.
4 Likes
Ok, so what should I use? With LastRowID it seems to work.
OK.Thanks.
1 Like