SQLite Database, column doesn't exist

Question

I'm trying to create this database and then retrieve the values from it, but I just can't figure out the syntax. I've been searching google all weekend and I'm going crazy. Please help me understand why this isn't working. Here is the SQLiteHelper:

public class DBHelper extends SQLiteOpenHelper {

public static final String DATABASE_NAME = "DBName.db";
public static final String RESULTS_TABLE_NAME = "results";
public static final String RESULTS_COLUMN_ID = "id";
private static final String KEY_WEATHER = "weather";
private static final String KEY_WEEKEND = "weekend";
private static final String KEY_HOBBIES = "hobbies";
private static final String KEY_MUSIC = "music";
private static final String KEY_FAMILY = "family";

private static final String[] columnArray = {KEY_WEATHER, KEY_WEEKEND, KEY_HOBBIES, KEY_MUSIC, KEY_FAMILY};

private HashMap hp;

public DBHelper(Context context){
    super(context, DATABASE_NAME, null, 1);

}
@Override
public void onCreate(SQLiteDatabase db) {
    Log.d("create db", "created database");
    db.execSQL("CREATE TABLE results "+
    "(id INTEGER PRIMARY KEY AUTOINCREMENT, " +
    "weather0 INTEGER, weather1 INTEGER, weather2 INTEGER, weather3 INTEGER, weather4 INTEGER, " +
    "weekend0 INTEGER, weekend1 INTEGER, weekend2 INTEGER, weekend3 INTEGER, weekend4 INTEGER, " +
    "hobbies0 INTEGER, hobbies1 INTEGER, hobbies2 INTEGER, hobbies3 INTEGER, hobbies4 INTEGER, " +
    "music0 INTEGER, music1 INTEGER, music2 INTEGER, music3 INTEGER, music4 INTEGER, " +
    "family0 INTEGER, family1 INTEGER, family2 INTEGER, family3 INTEGER, family4 INTEGER );");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLES IF EXISTS results");
    this.onCreate(db);
}

public boolean insertResult (String[] topics){

    //String topicUnitId = column+unitId;
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    /*for(int topicLoop = 0; topicLoop < topics.length; topicLoop++){
        for(int unitLoop = 0; unitLoop < 5; unitLoop++){
            contentValues.put(topics[topicLoop].toLowerCase(), -1);
            Log.d("CREATING TABLE", topics[topicLoop]+unitLoop);
        }
    }*/
    values.put("weather0", -1);
    values.put("weather1", -1);
    values.put("weather2", -1);
    values.put("weather3", -1);
    values.put("weather4", -1);
    db.insert(RESULTS_TABLE_NAME, null, values);
    db.close();
    return true;
}

public ArrayList getAllResults(int i){

    String arrayId = columnArray[i];
    ArrayList arrayList = new ArrayList();
    SQLiteDatabase db = this.getReadableDatabase();
    for(int n = 0; n <= 4; n++) {
        Cursor res = db.rawQuery("SELECT * FROM results WHERE " + arrayId + n +" IS NOT NULL;", null);
        res.moveToFirst();
        while (res.isAfterLast() == false) {
            arrayList.add(res.getString(res.getColumnIndex(arrayId+n)));
            res.moveToNext();
        }
    res.close();
    }

    db.close();
    return arrayList;
}
}

I get this error:

android.database.sqlite.SQLiteException: no such column: weather0 (code 1): , while compiling: SELECT * FROM results WHERE weather0 IS NOT NULL;

When I try to run "getAllResults()" from another activity in my app.

Thanks for any help.

Likely you already have the database but with different schema. Uninstall your app to get rid of the old version. http://stackoverflow.com/questions/21881992/when-is-sqliteopenhelper-oncreate-onupgrade-run — laalto, Feb 01 '15 at 20:06
I've tried, oh my God I've tried restarting the emulator, cleaning the project, oh my god — amh, Feb 01 '15 at 20:13
Emulator restart or project cleaning do not remove the old database file. — laalto, Feb 01 '15 at 20:22

score 1 · Answer 1 · answered Feb 01 '15 at 21:10

It is likely you are seeing this error because you have created the database with one schema, then modified the schema and run the app again. The initial database file will persist on the device and will not be re-created with the new schema unless you increment the database version number.

The database version number is set in the call to SQLiteOpenHelper constructor.

super(context, DATABASE_NAME, null, 1);

Alternatively you could just uninstall the app from the emulator or select a different database file name and the database will be re-created on next execution.

score 0 · Answer 2 · answered Feb 01 '15 at 21:21

0

Probably you have already created database and then tried to change it.

Solution: Delete your app from device or change database version from 1 to other in:

super(context, DATABASE_NAME, null, DATABASE_VERSION);

answered Feb 01 '15 at 21:21

Guntars Ļuta

61
2
10

SQLite Database, column doesn't exist

2 Answers2