Android increment database value by one

Question

I have a method which should upgrade the unreadMessageCount of a given user.

public void increaseUnreadMessageCount(int userID) {
    String query = "UPDATE " + tableName + " SET " + DatabaseHelper.KEY_USER_UNREAD_MESSAGE_COUNT + " = "
            + DatabaseHelper.KEY_USER_UNREAD_MESSAGE_COUNT + " + 1 WHERE " + DatabaseHelper.KEY_USER_USER_ID
            + " = ?";
    Log.i("query", query);
    db.rawQuery(query, new String[] { String.valueOf(userID) });
}

This evaluates to: UPDATE user SET unreadPrivateMessageCount = unreadPrivateMessageCount + 1 WHERE userID = ? which seems correct to me. But somehow it is not working, the row is always 0.

Or is my SELECT statement wrong?

public int getUnreadPrivateMessageCount() {
    String query = "SELECT SUM(" + DatabaseHelper.KEY_USER_UNREAD_MESSAGE_COUNT + ") AS messageCount FROM "
            + tableName + " WHERE " + DatabaseHelper.KEY_USER_UNREAD_MESSAGE_COUNT + " <> ?";
    Log.i("query", query);
    Cursor cursor = db.rawQuery(query, new String[] { "0" });
    int messageCount = 0;
    if (cursor.getCount() > 0) {
        if (cursor.moveToFirst()) {
            messageCount = cursor.getInt(cursor.getColumnIndexOrThrow("messageCount"));
            cursor.close();
        } else {
            Log.e("getUnreadPrivateMessageCount", "moveToFirst failed");
        }

    } else {
        Log.e("getUnreadPrivateMessageCount", "count 0");
    }

    return messageCount;
}

This query evaluates to SELECT SUM(unreadPrivateMessageCount) AS messageCount FROM user WHERE unreadPrivateMessageCount <> ? and no error log is triggered, which also seems correct to me.

CL. · Accepted Answer · 2015-04-18 16:09:38Z

1

rawQuery is used to execute queries, i.e., SELECT statements. To execute other SQL statements, use execSQL instead.

Also check if you really want to treat your IDs as strings.

answered Apr 18, 2015 at 16:09

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chris Over a year ago

Doc of execSQL : "Execute a single SQL statement that is NOT a SELECT/INSERT/UPDATE/DELETE. "

CL. Over a year ago

@Chris The documentation is wrong; this function is not intended to execute those commands because there is no returned cursor (for SELECT) or there already are specialized functions (for the others).

Chris Over a year ago

Thanks, seems to work now. What's the purpose of life when the documentation is wrong :/

Ahmad Ebrahimi · Accepted Answer · 2015-04-18 15:34:42Z

0

If your update syntax is correct then :

public void increaseUnreadMessageCount(int userID) {
    String query = "UPDATE " + tableName + " SET " + DatabaseHelper.KEY_USER_UNREAD_MESSAGE_COUNT + " = "
            + DatabaseHelper.KEY_USER_UNREAD_MESSAGE_COUNT + " + 1 WHERE " + DatabaseHelper.KEY_USER_USER_ID
            + " = ?";
    Log.i("query", query);
    db.rawQuery(query, new String[] { String.valueOf(userID) });
}

where did you use : getUnreadPrivateMessageCount(), no where, so it must be something like this :

public void increaseUnreadMessageCount(int userID) {
    String query = "UPDATE " + tableName + " SET " + DatabaseHelper.KEY_USER_UNREAD_MESSAGE_COUNT + " = "
            + getUnreadPrivateMessageCount() + " WHERE " + DatabaseHelper.KEY_USER_USER_ID
            + " = ?";
    Log.i("query", query);
    db.rawQuery(query, new String[] { String.valueOf(userID) });
}

In this way total unread message is passed to table.

answered Apr 18, 2015 at 15:34

Ahmad Ebrahimi

2466 silver badges26 bronze badges

1 Comment

Chris Over a year ago

The method is obviously called in another place.

Collectives™ on Stack Overflow

Android increment database value by one

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related