0

I have this SQLite table in my Android application:

CREATE TABLE " + TABLE_NAME + " (" +
        _ID + " INTEGER PRIMARY KEY AUTOINCREMENT" +
        COMMA + COLUMN_NAME_SID + " INTEGER" +
        COMMA + COLUMN_NAME_TITLE + " TEXT NOT NULL" +
        COMMA + COLUMN_NAME_IS_MINE + " BOOLEAN NOT NULL" +
        COMMA + COLUMN_NAME_CREATED_AT + " DATETIME" +
        COMMA + COLUMN_NAME_CODE + " TEXT UNIQUE" +
        COMMA + COLUMN_NAME_MSG + " INTEGER NOT NULL DEFAULT 0" +
        " );";

When I update a record with the same data, the number of changed rows (rows affected) is always 1.

SQLiteDatabase db = m_helper.getWritableDatabase();
String where = COLUMN_NAME_SID + "=" + sid;
ContentValues values = new ContentValues();
values.put(COLUMN_NAME_MSG, msg);
int rowsAffected = db.update(TABLE_NAME, values, where, null);
db.close();
// rowsAffected is always 1!

In other DBMS (like Mysql), when I perform an update with the same data on a specific record, the number of changed rows is 0. In SQLite on Android is different?

Alex
  • 163
  • 11
  • Maybe it just does not recognize if that data has *actually* been changed. Any query is counted as a change, even if the data is the same. – Vucko Jun 19 '16 at 21:17
  • I also think so, but it's strange. Others DBMS (like MySQL) return zero if data don't change. – Alex Jun 19 '16 at 21:30

1 Answers1

1

Maybe it just does not recognize if that data has actually been changed. Any query is counted as a change, even if the data is the same.

Well that's just the way they've implemented it. For instance, if you write something in IntellIJ IDE and delete it, it doesn't show the file as changed, but if you do it in Visual Studio, it does, unless you did CTRL+Z. It's just a different way of implementation.

Vucko
  • 7,371
  • 2
  • 27
  • 45
  • so I would be forced to edit the query as follows: `[...] String where = COLUMN_NAME_SID + "=" + sid + " AND " + COLUMN_NAME_MSG + " <> " + msg; [...]` I have no other alternative ? – Alex Jun 19 '16 at 21:48
  • Hm, probably yes. This way you can track the changes if they really happen only :) Good idea – Vucko Jun 19 '16 at 21:53