Android database rawQuery not working as expected

Question

This is the code I'm trying to run:

        open();
        database.beginTransaction();

        Cursor cursor = database.rawQuery("UPDATE message SET is_read=1 WHERE id = 'user1' AND (mid IN ('msg1','msg2'))",null);
        if (cursor != null) {
            if (cursor.moveToFirst()) {
                <... success ...>
            }
            cursor.close();
        }
        database.endTransaction();
        close();

And it doesn't work - the response is successful but my cursor has no updated rows.

The same query against the same database work perfectly fine in pure SQL (I'm using sqlitebrowser). What's happening here?

@EagleEye thanks for the suggestion - tried that before, tried again, same result. @Blackbelt All I expect is for `mUpdateRows` size inside cursor to be != 0 - if you're talking about moveToFirst - that's just something someone suggested in another post. I only need the count — vkislicins, Jul 09 '15 at 12:23

score 3 · Answer 1 · answered Jul 18 '19 at 14:29

3

As this is an update, I recommend you change rawQuery for execSQL.

The difference between one and the other is detailed here: difference between rawquery and execSQL in android sqlite database

answered Jul 18 '19 at 14:29

criscan

91
5

score 1 · Accepted Answer · answered Jul 09 '15 at 12:26

1

Try this, you need to set transaction successful after transaction completed.

Cursor cursor = database.rawQuery("UPDATE message SET is_read=1 WHERE id = 'user1' AND (mid IN ('msg1','msg2'))",null);
  if (cursor != null) {
        if (cursor.moveToFirst()) {
             <... success ...>
         }
         cursor.close();
        }
    database.setTransactionSuccessful();
    database.endTransaction();
    close();

answered Jul 09 '15 at 12:26

Krishna V

1,801
1
14
16

A transaction must be marked as successful. If you don't do this all changes inside a transaction will be rolled back. https://www.sqlite.org/lang_transaction.html – jlhonora Jul 09 '15 at 12:35
if you call transactionSuccessful then only it commited otherwise it will be rollback. – Krishna V Jul 09 '15 at 12:39

Android database rawQuery not working as expected

2 Answers2