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?

The recommended method to insert new rows is `insert()` which returns the rowid of the row that you insert. If you don't get the correct result for the rowid then the problem is with your code and this is what you should attempt to correct instead of trying to figure out what is wrong with undocumented features of `rawQuery()`. — forpas, Dec 04 '20 at 10:02

laalto · Accepted Answer · 2020-12-04T08:56:47.140

0

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.

edited Dec 04 '20 at 08:56

answered Dec 04 '20 at 08:42

laalto

150,114
66
286
303

I'll take your advice on `execSql` but see my edits please – AppDeveloper Dec 04 '20 at 08:50
so I should use execSQL for DELETE, INSERT and UPDATE? – AppDeveloper Dec 04 '20 at 08:58
Yes if you want to use raw sql strings and not one of the convenience wrappers such as `insert()` – laalto Dec 04 '20 at 08:59
sure I prefer plain vanilla, actually wrappers tend to be verbose, especially the ones with ContentValues – AppDeveloper Dec 04 '20 at 09:00
however, for update and delete I need modified row count so sticking to rawQuery and then using `cursor.count`, ok with that? – AppDeveloper Dec 04 '20 at 09:01
1

Nope. You'll get zero since there are no result rows. Use execSQL for the update and rawQuery for `SELECT changes()` to get the row count in case you need it. – laalto Dec 04 '20 at 09:04
please review my answer, is this ok? – AppDeveloper Dec 04 '20 at 11:48
1

For the `chages()` result you don't want `cursor.count` since it's always 1. Get the first value from the first row instead. – laalto Dec 04 '20 at 14:40
wow, that was subtle, updated snippet, your blessing please. Also will this be thread safe (`last_insert_rowid`) – AppDeveloper Dec 05 '20 at 04:02
question, i'm stuck at this, can you have multiple sql statements within execSQL? following is the my sql – AppDeveloper Dec 05 '20 at 21:06
If you have more questions, please post them as questions and not as comments. Anyway, the answer is no, the sql is processed only until the first `;` – laalto Dec 05 '20 at 21:07
https://stackoverflow.com/questions/65162002/can-execsql-supports-multiple-sql-statements – AppDeveloper Dec 05 '20 at 21:13

AppDeveloper · Answer 2 · 2020-12-05T04:00:17.173

Based on laalto's answer to get a confirmation.

fun save(): Long? {
    val sql = "INSERT INTO user(first, last) VALUES(?, ?)"

    connection.writableDatabase.execSQL(sql, arrayOf("John", "Doe"))

    connection.readableDatabase.rawQuery("SELECT last_insert_rowid()", null).use { cursor ->
        return if (cursor.moveToNext()) cursor.getLong(0) else null
    }
}

fun update(): Int? {
    val sql = "UPDATE user SET first = ?, last = ? WHERE id = ?"
    connection.writableDatabase.execSQL(sql, arrayOf("Jane", "Doe", "1"))

    connection.readableDatabase.rawQuery("SELECT changes()", null).use { cursor ->
        return if (cursor.moveToFirst()) cursor.getInt(0) else null
    }
}

SQLite generated id after insert raw query

2 Answers2

Linked