Getting the next AUTO_INCREMENT value of a SQLite database

Question

Using the typical SQLiteDatabase object in Android's API, what can I do to get the next AUTO_INCREMENT value of a particular column (ie. id) without affecting the value itself. Is there a method for that? Or what query should I execute to get that result. Keep in mind that SQLiteDatabase.query() returns a Cursor object, so I'm not too sure how to deal with that directly if I just want to get a value out of it.

Answer 1

You're right. The first answer (still below) only works without an AUTOINCREMENT for id. With AUTOINCREMENT, the values are stored in a separate table and used for the increment. Here's an example of finding the value:

public void printAutoIncrements(){
    String query = "SELECT * FROM SQLITE_SEQUENCE";
    Cursor cursor = mDb.rawQuery(query, null);
    if (cursor.moveToFirst()){
        do{
            System.out.println("tableName: " +cursor.getString(cursor.getColumnIndex("name")));
            System.out.println("autoInc: " + cursor.getString(cursor.getColumnIndex("seq")));

        }while (cursor.moveToNext());
    }

    cursor.close(); 

}

See: http://www.sqlite.org/autoinc.html

First Answer:

You can query for the max of the _id column, such as:

String query = "SELECT MAX(id) AS max_id FROM mytable";
Cursor cursor = db.rawQuery(query, null);

int id = 0;     
if (cursor.moveToFirst())
{
    do
    {           
        id = cursor.getInt(0);                  
    } while(cursor.moveToNext());           
}
return id;

This works for row ids that haven't been specified as "INTEGER PRIMARY KEY AUTOINCREMENT" (all tables have a row id column).