android - SQLite generated id after insert raw query

Question

This question was asked in here before and I took one of the answers but its not working, I'm getting generated id as 0. This question is based on the last given answer.

The accepted answer used long id = db.insert(...); provided by the android but I'm wondering why can't we get the generated id from raw queries?

fun save(): Long {
    val sql = "INSERT INTO user(first, last) VALUES(?, ?)"
    connection.writableDatabase.rawQuery(sql, arrayOf("John", "Doe")).use {
        connection.writableDatabase.rawQuery("SELECT last_insert_rowid()", null).use {
            return if (it.moveToNext()) it.getLong(0) else 0
        }
    }
}

---

EDIT

I realized something, the issue is with this statement, this always returns 0 even if moveToNext is true, anyone expert in kotlin can advise?

return if (it.moveToNext()) it.getLong(0) else 0

I was supposed to use the ternary operator but whats the problem in here to begin with?

Answer 1

rawQuery() by itself does not execute the query until you move the resulting Cursor.

Use execSQL() instead for SQL that does not return a result, such as INSERT.