Android Sqlite get last insert row id

Question

Possible Duplicate:
Get generated id after insert

I want to get the last inserted row id in my android application using this code :

String query = "SELECT * from SQLITE_SEQUENCE";
int createdUserId = Integer.parseInt(dbHelper.executeSQLQuery(query).toString());

but the problem is that it's throws an exception that cannot convert dbHelper.executeSQLQuery(query).toString() to integer. I'm not really good at sqlite ,but i think that this should return the last row id which was inserted...which will definitely will be int (at least I think this way). So if this is not the right way, can someone please guide me how to get the last row id in android application.

Thanks!!!

Why would a `SELECT` statement *insert* anything? If you would actually *insert* something with, say `SQLiteDatabase.insert(...)` you'd get back "the row ID of the newly inserted row, or -1 if an error occurred". — Philipp Reichart, Sep 27 '11 at 20:28
@Bombastic: Without seeing the code for your helper class and the `executeSQLQuery(...)` method, it's impossible to say. Philipp is probably correct though - a `SELECT` operation won't return a last inserted row id and you need to be checking that when you actually insert something. — Squonk, Sep 27 '11 at 20:37
Here you can see my helper class : http://stackoverflow.com/questions/7444327/how-to-initialize-sqlite-database-once-from-a-helper-class-in-android — Android-Droid, Sep 27 '11 at 20:38
@Bombastic After looking at your code: Don't make `SQLiteOpenHelper` do things it's not meant for -- its task are creating, upgrading and returning references to the database. You won't get happy with SQLite on Android when you don't call the real query/insert/update/delete methods on a `SQLiteDatabase` instance obtained from your helper. — Philipp Reichart, Sep 27 '11 at 21:23
@Bombastic: `dbHelper.executeSQLQuery(query)` returns an instance of a `Cursor` therefore you are doing `Integer.parseInt(Cursor.toString())` which is never going to work. Assuming the `id` column of your table is a `long` with `AUTOINCREMENT` then FloatingCoder's method will work although you'll still get a `Cursor` back from the query and you'll have to use `moveFirst()` to be able to access the id's value. — Squonk, Sep 27 '11 at 21:30

FloatingCoder · Accepted Answer · 2011-09-27T22:13:21.933

Your SQL statrment will return all the row ids, not just the latest. Try something like this...

SELECT ROWID from SQL_LITE_SEQUENCE order by ROWID DESC limit 1

Also note that I believe selecting from SQL_LITE_SEQUENCE will get the latest ID from ANY table, you can also access the SQL_LITE_SEQUENCE by selecting ROWID on any table, and getting just the IDs for that table. IE

SELECT ROWID from MYTABLE order by ROWID DESC limit 1

And thanks to MisterSquonk for pointing out the next step in the comments, adding it here for ease of reference later...

The query statement will then return a Cursor object containing the results, so to access the integer value you would do something like this (I'll substitute more common methods for your helper method, just for others sake)

String query = "SELECT ROWID from MYTABLE order by ROWID DESC limit 1";
Cursor c = db.rawQuery(query);
if (c != null && c.moveToFirst()) {
    lastId = c.getLong(0); //The 0 is the column index, we only have 1 column, so the index is 0
}

(Note that although the SQL Lite docs call ROWID and Integer, it is a 64 bit integer, so in Java it should be retrieved as a long.)

Not atomic, unsecure. EDIT: Removed downvote, I thought that was asking another thing. — Jorge Fuentes González, Jun 11 '13 at 22:20
There is a theoretical possibility that this may not give the correct answer, because if rows are deleted from the database and then new rows added, after a massive amount of inserts the rowid may reach it's limit at which point sqlite will try to pick random rowid slots to use for a while. Should this happen then obviously the last inserted row does not have the largest numeric value and thus simply sorting the row ids will not suffice. [Autoincrement In SQLite](https://www.sqlite.org/autoinc.html) — Timo, May 04 '16 at 12:01

JimmyB · Answer 2 · 2013-05-14T08:54:44.393

12

Care to use "selectlast_insert_rowid()"? :)

edited May 14 '13 at 08:54

answered Oct 30 '11 at 19:57

JimmyB

12,101
2
28
44

Hey can you explain it a bit please like , how to execute this? i am trying by the Floating Coder's answer, but it is not executing :( – Vikas Gupta Apr 11 '13 at 12:59
Sorry, but I'm not sure what your problem is. Maybe you should open new question where you can give more details of what the problem is. – JimmyB Apr 12 '13 at 06:50
1

This is the correct query: SELECT ROWID from MYTABLE order by ROWID DESC limit 1 – Marek Bar May 10 '13 at 07:09
I think that will give you the last id from any table, not just the last in your table, correct? – weberc2 May 30 '13 at 21:56
1

@weberc2, `last_insert_rowid()` will return the ID which resulted from the *most recent* insert statement executed in the current session, on whatever table that may have been. – JimmyB Jun 08 '13 at 10:23
1

@Marek Bar NOT (if you are working with transactions)... – Beep.exe Aug 21 '14 at 08:47
@HannoBinder That seems to be what the question is asking: what's the most recent rowid "I want to get the last inserted row id in my android application using this code." – Christopher Oct 26 '14 at 12:53

Android Sqlite get last insert row id

2 Answers2

Linked