Last Row ID sql function

Getting Started
1

[code] // get next record id

dim lastrowrs as recordset

lastrowrs = db.SQLSelect(“SELECT LAST_ROWID (‘Subject’)”)

//msgbox(lastrowrs.Field(“SubjectID”).StringValue) // nil object error

If DB.Error Then
MsgBox("DB Error: " + DB.ErrorMessage) // DB error no such function LAST_ROWID
Return
End If

While Not lastrowrs.EOF

For i As Integer = 0 To lastrowrs.FieldCount-1
  
  W_debug.run(lastrowrs.IdxField(i+1).Name)
  W_debug.run(lastrowrs.IdxField(i+1).StringValue)
Next

lastrowrs.MoveNext

Wend

// change and update recordset
[/code]

I am using rs 2012 r2, the db is realsqldatabase. DB error no such function LAST_ROWID … How do I get the last row id of a specific table?

Thank you

2

Use the last_insert_rowid() function of SQLite.

3

If you can/want to use the SQLiteDatabase class directly, you can use db.LastRowId: http://documentation.xojo.com/index.php/REALSQLdatabase.LastRowID

4

Or do you mean the highest id? That would be SELECT MAX(_id) FROM table name;

5

Select max will work. The problem with REALSQLdatabase.LastRowID, it does not specify what table you want to get the highest id so you can use that value, +1 to it to get the next record number.

Thanks!

6

Just curious. Why do you want to know the possible next record id ? This usually is considered a not good design.

7

The usual approach for those DBs who decides for you the next rowid value, is letting it decide, and collecting the value afterwards. Not trying to guess beforehand.

8

with SQLite, I would define a column/field to be Primary Key, and then SQLite will use that as the rowid. Also with SQLIte do not expect the rowids to be the same every time. they can switch/modify themselves on you with no notification. Now this won’t happen if you have defined a PK as the rowid will always match whatever the PK is.

9

I am not following your statement very well, but… the design case I was pointing out is that, if you upgrade your system someday, for a future multiuser environment, where you can’t guarantee you are alone accessing the DB, between the time you read the last record of a table, add +1 to the Id, doing some more processing and inserting a new record, someone can be doing something like that and both will likely have the same id and have a collision with the same id. For databases that have features like auto-incremental ids, we can just insert the record (the DB engine will guarantee the atomicity and uniqueness of the id) and we get the new id inserted just after, then we can use it, like storing it’s value in another table referencing this new record.

10

Of course you can guarantee that. Start a transaction. Commit when your atomic sequence is finished. That’s the point of a transaction.

11

Nope. There is an error in your assumption.
If you try a BEGIN; SELECT MAX(rowid) AS LASTID FROM TAB; COMMIT; in simultaneous clients, all then will obtain the same erroneous last ID.

12

Read up on table locking.

13

Sorry. Locking will not affect the bad result that will occur in the example I cited.

14

A few points that should be emphasized:

  1. DO NOT use rowid as a foreign key value to link tables
  2. DO NOT fetch the last rowid, add one, and use it for anything
  3. DO NOT fetch the max rowid, add one, and use it for anything
  4. Use the built-in Xojo functions for retrieving the last rowid/PK value
  5. Always add your own PK column to a table (INTEGER PRIMARY KEY AUTOINCREMENT)
  6. Let the database assign PK/rowid values
15

Because your example does it wrong. Tim’s points are great advice, and should be adhered to unless you have a really good reason for doing things otherwise. If you find yourself not liking AUTOINCREMENT for some really good reason, and want to set primary keys yourself from the client, start a transaction, select the max primary key, add at least one to it, insert your record, end the transaction. That will block another client from doing the same thing until the transaction is finished. You’ll have a couple round-trips to do that, so it’s not as efficient as relying on AUTOINCREMENT. But it is very doable.

16

You statement shows that you did not read the previous conversation.

17

Are you kidding me, Ricardo? There are two posts in the “conversation” before everyone starts assuming what this guy is trying to do!

18

How can you agree with Tim and don’t agree with me if we both say the same?

19

Because you did not say the same thing, Ricardo. In fact, you said something that was very incorrect, as I pointed out in my response. Is Tim’s advice the best advice for most cases? Absolutely. But if AUTOINCREMENT is not appropriate for your primary keys, you can, in fact, assign such keys manually without collisions if you use transactions correctly. And you call me out for not reading the thread?

[Addition] AUTOINCREMENT is a convenience feature. It’s not magic.

20

I feel I can’t express myself very well in English as I thought. I am not being clear here, so, for the sake of the clarity of what I badly wished to say, and for finishing this thread, forget all my statements, and consider just my “like” to Tim’s statement. His statement is enough for me for the case.