Update table using rawQuery() method does not work

Question

I tried the following SQLite query:

    int idServizo = 150;
    String whereClause = id_servizio+" = '"+idServizio+" ' ";
    ContentValues cv = new ContentValues();
    cv.put("sync", 1);

    int r = dbManager.updateTable("myTable", cv, whereClause);

Where fields sync and id_servizio are both integer. The method updateTable is:

 public int updateTable(String table, ContentValues values, String whereClause){
    int r = mDb.update(table, values, whereClause, null);
    return r;
}

 // mDb is SQLiteDatabase object

All this works good. But if I try this with the rawQuery() method:

 public Cursor RawQuery(String sqlQuery, String[] columns){
    return mDb.rawQuery(sqlQuery, columns);    
}

The table is not updated! even if no error occurs.

 int idServizo = 150;
 String updateQuery ="UPDATE myTable SET sync = 1 WHERE id_servizio = "+idServizio;
 dbManager.RawQuery(updateQuery, null);

Why does this not work?

For those who are still looking, this is the way I made it work for the similar problem. See this [accepted answer](https://stackoverflow.com/a/9798527) by Akhil — royatirek, Oct 21 '18 at 21:12

score 46 · Answer 1 · edited Aug 07 '20 at 18:14

46

This is because when a rawQuery is executed cursor is returned. Without the call to cursor.moveToFirst() and cursor.close() the database won't get updated.

int idServizo = 150;
String updateQuery ="UPDATE myTable SET sync = 1 WHERE id_servizio = "+idServizio;
Cursor c= dbManager.rawQuery(updateQuery, null);

c.moveToFirst();
c.close();

I dont know the need to call moveToFirst() but this works fine and the database gets updated.

edited Aug 07 '20 at 18:14

Ryan M

18,333
31
67
74

answered Dec 15 '14 at 11:27

DUDE_MXP

724
7
24

3

Saved my day . Thanks – ch3tanz Oct 31 '15 at 09:48
1

Works like a charm! – GAMA Jan 16 '17 at 16:28
Spend multiple hours trying to figure out what I was doing wrong. Thanks, this answer saved my day. – Tom Groentjes May 05 '17 at 20:30
Great. Was scratching my head on this one. That explains it. – Ocean Airdrop May 08 '18 at 14:12
3

Use `db.execSQL()` instead of `db.rawQuery()`. – LoMaPh Jul 15 '18 at 06:18
Whaaaat, really?! The db won't get updated unless you do cursor.moveToFirst()? This ought to be in the documentation! Which currently says "Runs the provided SQL", not "Runs the provided SQL if or when you move through the returned cursor." Thank you for this answer! – LarsH Apr 08 '21 at 19:40
@LoMaPh The doc (https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#execSQL(java.lang.String,%20java.lang.Object[])) says not to use `execSQL()` with an UPDATE statement. – LarsH Apr 08 '21 at 19:44

score 8 · Accepted Answer · edited May 23 '17 at 12:34

8

Problem solved.

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

Can't works because rawQuery runs the provided SQL and returns a Cursor over the result set.

If I want to return a table I have to use rawQuery, otherwise no!

Increase the value of a record in android/sqlite database

edited May 23 '17 at 12:34

Community

1
1

answered Oct 24 '12 at 10:37

GVillani82

17,196
30
105
172

score 5 · Answer 3 · answered Sep 26 '17 at 14:10

5

You should use db.execSQL() instead db.rawQuery().

answered Sep 26 '17 at 14:10

ibado

129
1
4

score 4 · Answer 4 · answered Jul 04 '18 at 16:32

Instead of doing this:

Cursor c= dbManager.RawQuery(updateQuery, null);
c.moveToFirst();
c.close();

You just need this:

dbManager.execSQL(updateQuery, null);

----------------------------------------------------------------------------

Posting answer because sometimes many people (like me) not reading comments. Most popular answer is not correct but Yaqub Ahmad's comment is correct.

Answer from CommonsWare explained in this answer:

rawQuery() is for SQL statements that return a result set. Use execSQL() for SQL statements, like INSERT, that do not return a result set.

----------------------------------------------------------------------------

Documentation for execSQL:

public void execSQL (String sql)

Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.

Documentation for rawQuery:

public Cursor rawQuery (String sql, 
                String[] selectionArgs)

Runs the provided SQL and returns a Cursor over the result set.

If you mean that https://stackoverflow.com/a/27483015/423105 is not correct, please cite your source. While I have lots of respect for CommonsWare, `execSQL()` is problematic because the documentation for one of its signatures says "Execute a single SQL statement that is NOT a SELECT/INSERT/UPDATE/DELETE" (https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#execSQL(java.lang.String,%20java.lang.Object[])) — LarsH, Apr 08 '21 at 19:52
There's no clear reason why the aforementioned restriction on `execSQL(String sql, Object[] bindArgs)` wouldn't also apply to `execSQL(String sql)`, and in any case, bindArgs are often needed. — LarsH, Apr 08 '21 at 19:54

score 2 · Answer 5 · answered Oct 24 '12 at 10:29

2

Your update call formats the ID as string, while the rawQuery call formats is as number.

Assuming that the ID in the table indeed is a string, use:

String updateQuery = "UPDATE myTable SET sync = 1 WHERE id_servizio = '" + idServizio + "'";

answered Oct 24 '12 at 10:29

CL.

173,858
17
217
259

This error is not present in my code. I made a mistake asking question. – GVillani82 Oct 24 '12 at 10:31
why the -1 ? the suggested solution seems legit, if not correct – njzk2 Oct 24 '12 at 15:31
7

The REAL issue here is `mDb.rawQuery()` NOT the updateQuery, for UPDATE he need to use `execSQL()` instead of `rawQuery()`. – Yaqub Ahmad Oct 24 '12 at 16:47

Update table using rawQuery() method does not work

5 Answers5

----------------------------------------------------------------------------

----------------------------------------------------------------------------

Linked

Related