android.database.sqlite.SQLiteDatabase.rawQuery() is not updating a DATETIME column with a SQLite datetime() function

Question

public Cursor set_datetime_next(Reminder r) {       
    String _newVal = "datetime('now', '+7 days')";
    String[] args = { new Integer(r.getID()).toString() };
    String query =
        "UPDATE " + DBConst.TABLE
      + " SET "   + DBConst.f_DATETIME_NEXT + "=" + _newVal
      + " WHERE " + DBConst.f_ID +"=?";
    Log.i(TAG, query);
    return db.rawQuery(query, args);
}

I have also tried passing in datetime('now', '+7 days') as a bound parameter, that will not work, as the Android documentation says:

The values will be bound as Strings.

References:

is *_newval*: *datetime('now', '+7 days')* or *"datetime('now', '+7 days')"* ? — guido, Aug 08 '11 at 13:28
Hi Guido, I've updated the code sample to answer your question. — JD., Aug 08 '11 at 20:11

score 54 · Accepted Answer · edited Oct 03 '11 at 12:21

54

The cursor was not closed.

public void set_datetime_next(Reminder r, String _newVal) {     
    String[] args = { new Integer(r.getID()).toString() };
    String query =
        "UPDATE " + DBConst.TABLE
      + " SET "   + DBConst.f_DATETIME_NEXT + "=" + _newVal
      + " WHERE " + DBConst.f_ID +"=?";
    Log.i(TAG, query);
    Cursor cu = db.rawQuery(query, args);
    cu.moveToFirst();
    cu.close();     
}

While that makes sense, what really puzzles me is the requirement of calling moveToFirst() (or some other function which would "work with" the cursor in some way).
Without the call to both moveToFirst() and close(), the row was never updated. close() by itself, after the rawQuery(), did nothing.

edited Oct 03 '11 at 12:21

Christian Specht

35,843
15
128
182

answered Aug 09 '11 at 00:07

JD.

3,005
2
26
37

I've changed the return code to void since the caller had no reason to work with the result set, anyway. – JD. Aug 09 '11 at 00:07
3

thanks, this was a life saver! db handling on Android is really lousy implemented if you ask me – slinden77 Jun 29 '12 at 19:12
2

that is brilliant, the key point is moveToFirst() , I had problem after getting id from inserted record.The problem was solved by adding moveToFirst() before cursor begins. Look this; cr.moveToFirst(); int brandID = cr.getInt(cr.getColumnIndex(DatabaseTables.colBrandID)); – Günay Gültekin Jan 06 '13 at 20:23
1

Argh, that stupid moveToFirst has been killing me all day! Thanks for saving my bacon. – b-ryce Nov 04 '13 at 21:56
+1 This is very helpful. Although `moveToFirst()` makes no sense to me but gets the work done. – CodeWarrior Jan 10 '14 at 09:54
Hello @JD. I am getting cursor.getCount and cursor.getColumnCount as 0. Is there any way where we can get the count of updated rows ??? – KK_07k11A0585 Jul 21 '14 at 13:30

score 20 · Answer 2 · edited Oct 03 '11 at 12:20

20

Since it's an UPDATE statement you can use execSQL() rather than rawQuery(). You wouldn't have to bother with cursors (which is kinda silly for an UPDATE statement).
However, you will have to place values in your WHERE statement instead of passing args, as execSQL() only accepts a single String argument for your SQL statement. Also, execSQL() is of type void.

I use execSQL() for just about all SQL statements except SELECT...

edited Oct 03 '11 at 12:20

Christian Specht

35,843
15
128
182

answered Oct 03 '11 at 11:56

Jabari

5,359
3
26
32

This should be the accepted answer, more intuitive than rawQuery for this case, by far. – Aritz Nov 29 '16 at 08:56

android.database.sqlite.SQLiteDatabase.rawQuery() is not updating a DATETIME column with a SQLite datetime() function

2 Answers2

Linked