Increase the value of a record in android/sqlite database

Question

I try to increase the value of an integer key in a row in my table. However, nothing really seems to happen.

db.rawQuery("UPDATE table SET key = key + 1 WHERE name=?", new String[] {name});

However, this code works fine (just sets the key to a hard-coded value):

    ContentValues values = new ContentValues();
    values.put("key", 2);
    db.update("table", values, "name=?", new String[] {name});

Also tried '?' instead of just ?, but it resulted just in a run-time error.

Did you try "db.rawQuery("UPDATE table SET key = key + 1 WHERE name='name'" and passing null instead of the string[]? because this kind of statement works fine for me... — Sephy, Aug 06 '10 at 20:46
Sanity check: if the "key" is declared as a primary key, are you sure the new key value does not already exist? — MPelletier, Aug 07 '10 at 00:10
When using WHERE name='name' it doesn't seem to do anything. name='?' throws: android.database.sqlite.SQLiteException: bind or column index out of range: handle 0x292660. It is not declared as primary key or anything other strange, just a simple not-null integer, should be set to zero from the beginning. What can I do to try to isolate the problem? — nip3o, Aug 07 '10 at 21:12
I found a working solution, but don't understand the reason to the problem so if someone else does, feel free to submit an answer and it will be accepted if helpful. However, the query works fine if I use execSQL instead of rawQuery. — nip3o, Aug 07 '10 at 21:25
For trying `WHERE name='name'`, Sephy means to hardcode the actual name value instead of `'name'` as a test. Did you also try double quotes? — MPelletier, Aug 08 '10 at 14:40

score 15 · Accepted Answer · edited Jul 02 '14 at 19:02

15

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

Runs the provided SQL and returns a Cursor over the result set. Returns a Cursor object, which is positioned before the first entry

compared to:

public void execSQL (String sql, Object[] bindArgs)

Execute a single SQL statement that is not a query. For example, CREATE TABLE, DELETE, INSERT, etc.

In other words, SQL queries which return a table are to be run with rawQuery, and those that do not return tables are to be run with execSQL.

edited Jul 02 '14 at 19:02

xaizek

answered Aug 08 '10 at 01:48

MPelletier

1

I still don't really get it. If rawQuery really "runs the provided SQL" as stated in the docs, I can't understand why it wouldn't work for any kind of SQL statements. Of course, that's just a theoretical question, but still a bit strange. – nip3o Aug 08 '10 at 13:25
1

It's still a good question. The answer must lie with the where clause. I don't know how android treats this function, but if one function quotes the string argument, and if the other does not, that could explain why the number is not increased. Still, in example code I have seen, the `db.update` appears to be popular. More so than `db.execSQL("UPDATE...` – MPelletier Aug 08 '10 at 14:42

1 Answers1