onUpgrade() method does not work properly

Question

Im trying to call onUpgrade() to update my Table with few columns. however, when i run the app I get an error on the insert statement saying columns do not exist in this table (the columns i try to add in the onUpgrade(). I would really appreciate your help. Here's my SQLite helper class: //UPDATED AND WORKING

public class TasksSQLiteOpenHelper extends SQLiteOpenHelper {
public static final int VERSION = 3;
public static final String DB_NAME = "tasks_db.sqlite";
public static final String TASKS_TABLE = "tasks";
public static final String TASK_ID = "id";
public static final String TASK_NAME = "name";
public static final String TASK_RESPONSIBLE = "responsible";
public static final String TASK_PRIORITY = "priority";
public static final String TASK_DATETIME = "taskdatetime";


public TasksSQLiteOpenHelper(Context context) {
    super(context, DB_NAME, null, VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    createTables(db);
}

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

    Log.v("test",Integer.toString(oldVersion));
    Log.v("test", Integer.toString(newVersion));
switch(oldVersion){

case 1: db.execSQL("ALTER TABLE "+ TASKS_TABLE +" ADD COLUMN "+ TASK_RESPONSIBLE +" TEXT");
        db.execSQL("ALTER TABLE "+ TASKS_TABLE +" ADD COLUMN "+ TASK_PRIORITY +" TEXT");
        Log.v("test","res + pri created");
        break;

case 2: db.execSQL("ALTER TABLE "+ TASKS_TABLE +" ADD COLUMN "+ TASK_DATETIME +" TEXT");
        Log.v("test","date created");
        break;
    }
}

    protected void createTables(SQLiteDatabase db) {
        db.execSQL(
        "create table " + TASKS_TABLE+" (" + 
        TASK_ID + " integer primary key,"+ 
        TASK_NAME + " text"+ 
        ");"
        );
}

} and here's my Application class //UPDATED AND WORKING SOLUTION

    public class TaskManagerApplication extends Application {

private ArrayList<Task> currentTasks;
public SQLiteDatabase database;

@Override
public void onCreate() {
    super.onCreate();
    TasksSQLiteOpenHelper helper = new TasksSQLiteOpenHelper(this);
    database = helper.getWritableDatabase();
    if (null == currentTasks) {
        loadTasks();
    }
}
private void loadTasks() {
    currentTasks = new ArrayList<Task>();
    Cursor tasksCursor = database.query(TASKS_TABLE, new String[] {TASK_ID, TASK_NAME,TASK_RESPONSIBLE,TASK_PRIORITY,TASK_DATETIME}, null, null, null, null, null);
    tasksCursor.moveToFirst();
    Task t; 
    if (! tasksCursor.isAfterLast()) {
        do {
            long id = tasksCursor.getLong(0);
            String name  = tasksCursor.getString(1);
            String responsible  = tasksCursor.getString(2);
            String priority  = tasksCursor.getString(3);
            String datetime = tasksCursor.getString(4);
            t = new Task(name, priority, responsible, datetime);
            t.setId(id);
            currentTasks.add(t);
        } while (tasksCursor.moveToNext());
    }
    tasksCursor.close();
}

public void setCurrentTask(ArrayList<Task> currentTask) {
    this.currentTasks = currentTask; // set tasks in array
}

public ArrayList<Task> getCurrentTasks() {
    return currentTasks; //call back tasks
}

public void addTask(Task t) { // add new t of type Task
    assert(null != t); //check for null entries - avoid crashing
    ContentValues values = new ContentValues();
    values.put(TASK_NAME, t.getName());
    values.put(TASK_PRIORITY, t.getPriority());
    values.put(TASK_RESPONSIBLE, t.getResponsible());
    values.put(TASK_DATETIME, t.getDatetime());
    //Log.v("test", TASK_DATETIME);
    t.setId(database.insert(TASKS_TABLE, null, values));
    currentTasks.add(t);
}

public void remove(long id){
    //remove statement from table using where statement id
    database.delete(TASKS_TABLE, TASK_ID +" ="+ id, null);
}

}

also here's my logcat output

    08-28 16:30:41.170: I/Database(8931): sqlite returned: error code = 1, msg = table      tasks has no column named datetime
    08-28 16:30:41.190: E/Database(8931): Error inserting datetime=Aug 28, 2012 4:30:41    PM responsible=Me priority=Medium name=go chi
    08-28 16:30:41.190: E/Database(8931): android.database.sqlite.SQLiteException: table   tasks has no column named datetime: , while compiling: INSERT INTO tasks(datetime,     responsible, priority, name) VALUES(?, ?, ?, ?);
    08-28 16:30:41.190: E/Database(8931):   at     android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
    08-28 16:30:41.190: E/Database(8931):   at  android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
    08-28 16:30:41.190: E/Database(8931):   at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:65)
    08-28 16:30:41.190: E/Database(8931):   at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:83)
    08-28 16:30:41.190: E/Database(8931):   at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:41)
    08-28 16:30:41.190: E/Database(8931):   at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1290)
    08-28 16:30:41.190: E/Database(8931):   at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1710)
  08-28 16:30:41.190: E/Database(8931):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1567)
  08-28 16:30:41.190: E/Database(8931):     at com.id11078995.ghnaim.exercise1.TaskManagerApplication.addTask(TaskManagerApplication.java:64)
  08-28 16:30:41.190: E/Database(8931):     at com.id11078995.ghnaim.exercise1.AddTaskActivity.addTask(AddTaskActivity.java:59)
  08-28 16:30:41.190: E/Database(8931):     at com.id11078995.ghnaim.exercise1.AddTaskActivity.onOptionsItemSelected(AddTaskActivity.java:134)
  08-28 16:30:41.190: E/Database(8931):     at and roid.app.Activity.onMenuItemSelected(Activity.java:2211)
  08-28 16:30:41.190: E/Database(8931):     at com.android.internal.policy.impl.PhoneWindow.onMenuItemSelected(PhoneWindow.java:775)
  08-28 16:30:41.190: E/Database(8931):     at com.android.internal.view.menu.MenuItemImpl.invoke(MenuItemImpl.java:143)
  08-28 16:30:41.190: E/Database(8931):     at com.android.internal.view.menu.MenuBuilder.performItemAction(MenuBuilder.java:860)
  08-28 16:30:41.190: E/Database(8931):     at com.android.internal.view.menu.IconMenuView.invokeItem(IconMenuView.java:532)
  08-28 16:30:41.190: E/Database(8931):     at com.android.internal.view.menu.IconMenuItemView.performClick(IconMenuItemView.java:122)
  08-28 16:30:41.190: E/Database(8931):     at android.view.View$PerformClick.run(View.java:9152)
  08-28 16:30:41.190: E/Database(8931):     at android.os.Handler.handleCallback(Handler.java:587)
  08-28 16:30:41.190: E/Database(8931):     at android.os.Handler.dispatchMessage(Handler.java:92)
  08-28 16:30:41.190: E/Database(8931):     at android.os.Looper.loop(Looper.java:130)
  08-28 16:30:41.190: E/Database(8931):     at android.app.ActivityThread.main(ActivityThread.java:3691)
  08-28 16:30:41.190: E/Database(8931):     at java.lang.reflect.Method.invokeNative(Native Method)
  08-28 16:30:41.190: E/Database(8931):     at java.lang.reflect.Method.invoke(Method.java:507)
  08-28 16:30:41.190: E/Database(8931):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:907)
  08-28 16:30:41.190: E/Database(8931):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:665)
  08-28 16:30:41.190: E/Database(8931):     at dalvik.system.NativeStart.main(Native Method)

Answer 1

I guess that this is too obvious, but did you changed the database version? if not the onUpgrade() method would not work

Answer 2

If there is no need to save the old data, I think that is a much easier way to update your database:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  Log.w(MySQLiteHelper.class.getName(),
    "Upgrading database from version " + oldVersion + " to "
        + newVersion + ", which will destroy all old data");
  db.execSQL("DROP TABLE IF EXISTS " + TABLE_TASKS);
  onCreate(db);
}

@Override
public void onCreate(SQLiteDatabase database) {
  database.execSQL(DATABASE_CREATE);
}

Your final String DATABASE_CREATE includes all fields. Remove the "NOT NULL" and "autoincrement" in the primary key definition.

• clean your project
• uninstall your application
• reinstall it again.

For further information: Vogella

Try this solution without losing the data:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  Log.w(MySQLiteHelper.class.getName(),
    "Upgrading database from version " + oldVersion + " to "
        + newVersion);
  boolean lDateTimeExists = isColumnExisting(TASK_DATETIME);
  boolean lResponsible = isColumnExisting(TASK_RESPONSIBLE);
  boolean lPrio = isColumnExisting(TASK_PRIORITY);
  if (!lDateTime){
    db.execSQL("ALTER TABLE "+ TASKS_TABLE +" ADD COLUMN "+ TASK_DATETIME +" TEXT"); 
  }
  if (!lResponsible){
    db.execSQL("ALTER TABLE "+ TASKS_TABLE +" ADD COLUMN "+ TASK_RESPONSIBLE);       
  }
  if (!lPrio){
    db.execSQL("ALTER TABLE "+ TASKS_TABLE +" ADD COLUMN "+ TASK_PRIORITY +"   TEXT");       
  }
}

private boolean isColumnExisting(String pColumnName){
  try{
    db.execSQL("SELECT "+pColumnName+" from "+TABLE_TASKS);
  }catch(Exception e){
    return false;
  }
  return true;
}

Answer 3

just uninstall and reinstall the application hopefully it will work.