SQLite Cursor values changing

Question

EDIT: Found solution (at bottom of post), still not sure what the problem was though

Im having a really strange error in my SQLite database (or maybe I just didn't get enough sleep)

Im inputting a users bodyweight, and the date is was entered. If the user inputs twice or more in the same day, the last entry is overwritten. It seems to work out fine, but when I pull out the data, the date String is changed for some reason.

Creating table:

String CREATE_BODYWEIGHT_TABLE = "CREATE TABLE " + TABLE_BODYWEIGHT + "(" + KEY_DATE + " TEXT PRIMARY KEY," + KEY_WEIGHT + " REAL)";

Inputting and retrieving:

public void insertWeight(String date, float weight){
        SQLiteDatabase db = this.getWritableDatabase();
        Log.d("DBHandler date", date);
        db.execSQL("INSERT OR REPLACE INTO " + TABLE_BODYWEIGHT + "(date, weight) VALUES (" + date + ", " + weight + ")");
    }

    public HashMap<String, Float> getAllWeightEntries(){
        HashMap<String, Float> dateAndWeight = new HashMap<String, Float>();
        // Select All Query
        String selectQuery = "SELECT * FROM " + TABLE_BODYWEIGHT;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Log.d("Return date", cursor.getString(0));
                dateAndWeight.put(cursor.getString(0), cursor.getFloat(1));
            } while (cursor.moveToNext());
        }

        // return map
        return dateAndWeight;
    }

This is the output from logCat:

07-16   12:03:42.758:   DBHandler date:     2014-07-16
07-16   12:03:42.798:   Return date:        1991
07-16   12:03:42.798:   Date:               1991
07-16   12:03:42.798:   Weight:             123.0

EDIT: I changed my insertWeight to the following, and it works now:

public void insertWeight(String date, float weight){
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_DATE, date);
        values.put(KEY_WEIGHT, weight);

        db.insertWithOnConflict(TABLE_BODYWEIGHT, null, values, SQLiteDatabase.CONFLICT_REPLACE);
        db.close();
    }

Try to pull out your database from your device and look into it. What does it stores? Use one of these solutions how to pull out the database: http://stackoverflow.com/questions/14744496/extract-database-of-an-application-from-android-device-through-adb or http://stackoverflow.com/questions/9997976/android-pulling-sqlite-database-android-device. — Robertas Setkus, Jul 16 '14 at 10:51

score 1 · Accepted Answer · answered Jul 16 '14 at 12:09

1

The problem was you didn't quote your date string literal and it was treated as an arithmetic expression. 2014-7-16 equals 1991.

Using ContentValues binds the args as strings. You could also have used e.g. 'single quotes' in the SQL.

answered Jul 16 '14 at 12:09

laalto

150,114
66
286
303

SQLite Cursor values changing

1 Answers1