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

Exception or error:
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:

How to solve:

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.

###

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

Leave a Reply

Your email address will not be published. Required fields are marked *