Set Sqlite Db Properly In Android

I have an sqlite db containing different columns and I was experimenting with the db and happen to change back the version to 1 from 2. I have now reversed all the changes but the

Solution 1:

In the readPos method, instead of :-

Cursor cursor = db.rawQuery("SELECT " + LAST_BTN + " FROM "
                +TABLE_NAME+" WHERE " + _ID +" =? " });

You should have :-

Cursor cursor = db.rawQuery("SELECT " + LAST_BTN + " FROM "
            +TABLE_NAME+" WHERE " + _ID +" =? ",new String[]{_id});

The above code was working perfectly fine before the changes. Now the updatePos gives the correct value but the readPos always returns zero ....

Using cursor.getInt(cursor.getColumnIndex(LAST_BTN)) will, unless the value in the LAST_BTN column is numeric, return 0 (unable to change string into a number so returns 0). From your description of the issue, then it seems likely that the values stored in the LAST_BTN column are not fully numeric.

• If you want the get a value that uniquely identifies the row then return the primary key id the id column.

You also don't need to pass last_btn to the readPos method, so could use public int readPos(String _id) instead of public int readPos(String _id, int last_btn).

Additionally, you are leaving the cursor open, too many open Cursors and the App will crash. I'd suggest considering the following:-

public int readPos(String _id) {
    int rv = 0;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("SELECT " + LAST_BTN + " FROM "
            +TABLE_NAME+" WHERE " + _ID +" =? ",new String[]{_id});
    if(cursor.moveToFirst()) {
        rv = cursor.getInt(cursor.getColumnIndex(LAST_BTN));
    }
    cursor.close();
    return rv;
}

However, the above changes WILL NOT resolve the issue that readPos will return 0 if the value stored in the LAST_BTN column is non-numeric e.g. if it is "A1234" then result will be 0, if it is "1234" then 1234 will be returned.

Example

Using your code (but with the suggested readPos method) then using the following:-

    DBHelper dbHelper = new DBHelper(this);
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    db.execSQL("INSERT INTO " + DBHelper.TABLE_NAME
            + "(_id,LAST_BTN,button_no)"
            + "VALUES "
            + "('test1','last_button1','button1')"
            +  ",('test2','last_button2','button2')"
            + ",('test3','last_button3','button3')"
            + ",('test4','199','button4')"
            + ";"
    );

    Log.d("DBINFO","Result of readPos for test1 is " + dbHelper.readPos("test1")); // 0 as last_button1 is not a number
    Log.d("DBINFO","Result of readPos for test2 is " + dbHelper.readPos("test2")); // 0 as last_button2 is not a number
    Log.d("DBINFO","Result of readPos for test3 is " + dbHelper.readPos("test3")); // 0 as last_button3 is not a number
    Log.d("DBINFO","Result of readPos for test4 is " + dbHelper.readPos("test4")); // 199 as 199 is a number
    Log.d("DBINFO","Result of readPos for test5 is " + dbHelper.readPos("test5")); // 0 as no row found

Results in :-

D/DBINFO: Result of readPos for test1 is 0
D/DBINFO: Result of readPos for test2 is 0
D/DBINFO: Result of readPos for test3 is 0
D/DBINFO: Result of readPos for test4 is 199
D/DBINFO: Result of readPos for test5 is 0

i.e. as per the comments test1-test3 return 0 not because a row wasn't found but because the string stored in the LAST_BTN column cannot be converted to a number so instead of crashing the SQLite API converts it 0. test4 is extracted and a non 0 value is returned because the value stored in the LAST_BTN can be converted(represents) to a number. test5 doesn't exist in the database so 0 is returned because the row wasn't found.