CODEWITHSUNDEEP
javaandroidsqlite
Matt
Matt

Reputation: 3912

onUpgrade() not being invoked - Android

I added a column (SUM) to my table. I thought all I needed to do to rewrite/recreate my table was increate my variable for DATABASE_VERSION but that is not working. Do I need something inside my onUpgrade() method?

Version variable:

private static final int DATABASE_VERSION = 3;

Constructor:

public DatabaseHelper(Context context) { 
    super(context, DB_NAME, null, DATABASE_VERSION); 
}

onUpgrade() and onCreate() methods:

 public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + TABLE + " ("
            + RANK + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + SCORE + " LONG,"
            + PERCENTAGE + " INTEGER,"
            + SUM + " LONG"
            + ");");
}

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}

LogCat output

01-04 02:00:24.635: E/AndroidRuntime(6224): FATAL EXCEPTION: main
01-04 02:00:24.635: E/AndroidRuntime(6224): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.test/com.example.test.Results}: android.database.sqlite.SQLiteException: no such column: sum (code 1): , while compiling: SELECT sum FROM HighscoresList
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2180)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2230)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.app.ActivityThread.access$600(ActivityThread.java:141)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1234)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.os.Handler.dispatchMessage(Handler.java:99)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.os.Looper.loop(Looper.java:137)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.app.ActivityThread.main(ActivityThread.java:5039)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at java.lang.reflect.Method.invokeNative(Native Method)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at java.lang.reflect.Method.invoke(Method.java:511)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at dalvik.system.NativeStart.main(Native Method)
01-04 02:00:24.635: E/AndroidRuntime(6224): Caused by: android.database.sqlite.SQLiteException: no such column: sum (code 1): , while compiling: SELECT sum FROM HighscoresList
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at com.example.test.DatabaseHelper.check(DatabaseHelper.java:82)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at com.example.test.Results.showResults(Results.java:111)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at com.example.test.Results.onCreate(Results.java:60)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.app.Activity.performCreate(Activity.java:5104)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080)
01-04 02:00:24.635: E/AndroidRuntime(6224):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2144)
01-04 02:00:24.635: E/AndroidRuntime(6224):     ... 11 more

Upvotes: 0

Views: 1295

Answers (5)

kabuko
kabuko

Reputation: 36302

Yes, in onUpgrade you need to actually update the table, i.e. add the column:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 3) {
        db.execSQL("ALTER TABLE " + TABLE + " ADD COLUMN " + SUM + " LONG");
    }
}

You might also want to go back through and populate valid "sum" values for each row if required/desired.

Upvotes: 4

Brian Butterfield
Brian Butterfield

Reputation: 161

You need to implement the onUpgrade method and perform necessary table changes.

If you need to preserve the existing data (or want to take the "right" approach), you should perform an ALTER TABLE statement to add your new column and possibly identify a default value or update the existing rows with the right value. Same as "Tomas" stated.

Here is an example:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
    Log.d(TAG, "Upgrading database from version " + oldVersion + " to version  " + newVersion);

    if (newVersion == 3)
    {
        //Moved IO configuration data into flat file, so removing servicedata table.
        db.execSQL(DB_DROP_SERVICEDATA_V3);
    }
    else if (newVersion == 2)
    {
        db.execSQL(DB_ALTER_MESSAGE_V2);
    }
    else
    {
        db.execSQL("drop table if exists " + TABLE_ACCOUNT);
        db.execSQL("drop table if exists " + TABLE_MESSAGE);
        //Old table just checking.
        db.execSQL("drop table if exists servicedata");

        onCreate(db);
    }
}

Upvotes: 2

user219882
user219882

Reputation: 15844

Yes, that's what onUpgrade() is for.

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    if (oldVersion < 3) {
        upgradeToVersion3();
    }

    // good pattern is to make these incremental updates to ensure that everyone
    // will end up with the same version no matter what version he/she has now
    if (oldVersion < 4) {
        // upgradeToVersion4()
    }

}

private void upgradeToVersion3() {
    db.execSQL("alter table " + TABLE + " add column SUM LONG");
}

Upvotes: 1

Ted Hopp
Ted Hopp

Reputation: 234847

You need to recreate your table in onUpgrade:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}
    db.execSQL("DROP TABLE IF EXISTS " + TABLE);
    onCreate(db);
}

or else add the column:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}
    db.execSQL("ALTER TABLE " + TABLE + " ADD COLUMN `SUM` LONG");
}

Upvotes: 4

Sam
Sam

Reputation: 86948

Your onUpgrade() method is empty:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}

At the most basic level you need to add the code to drop the existing table and call onCreate(), then increment DATABASE_VERSION again.

private static final int DATABASE_VERSION = 4; 
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.exec("DROP TABLE IF EXISTS " + TABLE);
    onCreate(db);
}

Upvotes: 4

Related Questions