oriharel
oriharel

Reputation: 10468

how to get a row ID from a Cursor

How do I get the row ID from a Cursor?

Upvotes: 17

Views: 24788

Answers (6)

MikeT
MikeT

Reputation: 57103

As long as the TABLE is not defined using WITHOUT ROWID you can get the ROWID (or should that be rowid see below for case) if you specify it as a column to be retrieved.

For example for a table with 3 defined columns (Names, Colour and Age) with no conventional _id column. The following rawQuery works and returns the ROWID :-

Cursor csr = db.rawQuery("SELECT Names, Colour, Age, ROWID FROM " + TABLE_NAME,null);

Note! that the column name in the cursor is lowercase as per :-

enter image description here

Note! ROWID in the SELECT SQL is case independent (e.g. RoWiD works).

Using

Cursor csr = db.rawQuery("SELECT * FROM " + TABLE_NAME,null);

WILL NOT return the ROWID (likewise for null for the columns when using the query method).

Using query (as opposed to rawQuery) works in the same way, that is you need to specifiy ROWID (note alternatives to ROWID below) as a column to be retrieved e.g. :-

Cursor csr = db.query(TABLE_NAME,new String[]{
                "OiD",
                "Names",
                "Colour",
                "Age"
        },null,null,null,null,null);

Alternatives to ROWID

Instead of ROWID, you can also use _ROWID_ or OID (case independent) as the column name in the query noting that the column name in the resultant cursor is rowid i.e. it is lowercase.

Upvotes: 0

Arkadiusz Cieśliński
Arkadiusz Cieśliński

Reputation: 5336

return sqlite_db.query(table, new String[] { "rowid", "*" }, where, args, null, null, null);

In my case I have "rowid" in DataManager.FIELD_ID and this is SQLite identity column (each table in sqlite has this special kind of column), so I don't need any of my own custom unique id column in tables.

Upvotes: 2

Ferran Maylinch
Ferran Maylinch

Reputation: 11539

Cursor cursor = mySQLiteHelper.getReadableDatabase().query(TABLE_NAME, new String[] { "ROWID", "*" }, where, null, null, null, null);

then

long rowId = cursor.getLong(0);

Upvotes: 0

Nic Strong
Nic Strong

Reputation: 6592

I don't think the Cursor exposes this directly.
SQLiteDatabase.insert() returns the row id of the newly inserted row. Or in Android the convention is that there is a column named "_id" that contains the primary autoincrement key of the table. So cursor.getLong(cursor.getColumnIndex("_id")) would retrieve this.

Upvotes: 14

CP Taylor
CP Taylor

Reputation: 119

Concerning the last sentence of Nic Strong's answer,following command didn't work for me. cursor.getColumnIndex("_id") was still -1

 cursor.getLong(cursor.getColumnIndex("_id"))

Maybe there's some other issue in my configuration that's causing the problem?

Personally I've taken to maintaining my own custom unique id column in each table I create; A pain, but it gets around this issue.

Upvotes: 3

Luigi Putanesca
Luigi Putanesca

Reputation: 41

I had this same problem where the column index for the primary key was reported as -1 (meaning it isn't there). The problem was that I forgot to include the _ID column in the initial SELECT clause that created the cursor. Once I made sure it was included, the column was accessible just like any of the others.

Upvotes: 4

Related Questions