Android SQLite won't execute onUpgrade()

Question

I'm trying to add some columns to my SQLite database. I keep having the following error:
no such column: n_livello (eg. the new column I'm adding)
and the app installation fails with timeout.
It seems that onUpgrade() method is never called.
This is my code:

public class Main extends Activity {

    Context context;
    DBHelper db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {        

    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    context = this.getApplicationContext(); 
    db = new DBHelper(context);

    }

}

public class DBHelper extends SQLiteOpenHelper {

   public static final String DATABASE_NAME = "geko";
   static final int DATABASE_VERSION = 2;

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

   @Override
   public void onCreate(SQLiteDatabase db) {

      String CREATE_FRA_TABLE = "CREATE TABLE IF NOT EXISTS "+ FRA_TABLE_NAME + " ("
              + FRA_COLUMN_ID + " INTEGER PRIMARY KEY, "
              + FRA_COLUMN_USERNAME + " TEXT, "
              + FRA_COLUMN_NUM_LIVELLO + "INTEGER, "
              + FRA_COLUMN_LIVELLO + " TEXT, "
              + FRA_COLUMN_TEMPO + " TEXT, "
              + FRA_COLUMN_DATA_PARTITA + " TEXT,"
              + FRA_COLUMN_INVIATO + " INTEGER)";

              db.execSQL(CREATE_FRA_TABLE);

      String CREATE_ING_TABLE = "CREATE TABLE IF NOT EXISTS "+ ING_TABLE_NAME + " ("
              + ING_COLUMN_ID + " INTEGER PRIMARY KEY, "
              + ING_COLUMN_USERNAME + " TEXT, "
              + ING_COLUMN_NUM_LIVELLO + "INTEGER, "
              + ING_COLUMN_LIVELLO + " TEXT, "
              + ING_COLUMN_TEMPO + " TEXT, "
              + ING_COLUMN_DATA_PARTITA + " TEXT,"
              + ING_COLUMN_INVIATO + " INTEGER)";

              db.execSQL(CREATE_ING_TABLE);
   }

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

Thanks for the help.

[EDIT] I checked and onUpgrade() is not called. Obviously I get the error when I try to make a query that concernes the new fields I'm trying to add. Maybe I am declaring the DBHelper class the wrong way...
I'm adding the code in the main activity where I instance the DBHelper.

Also: what an extreme way of performing database upgrades. Isn't it more appropriate to use `ALTER TABLE` instead of removing table with all the data? — Alex Semeniuk, Apr 23 '15 at 13:08
After editing the table structure you increased your Version huh? — micky, Apr 23 '15 at 13:11
@AlexSemeniuk usually yes, but in this case I made a lot of changes to fields and datatypes and was more simple to drop the existing database. — esseara, Apr 23 '15 at 13:12
"It seems that onUpgrade() method is never called." Is this an assumption or have you put a breakpoint there and debugged it (or logged some debug message or ...)? — Ridcully, Apr 23 '15 at 13:12

score 2 · Answer 1 · edited May 23 '17 at 10:24

2

Add a space between the column name and datatype here:

+ ING_COLUMN_NUM_LIVELLO + "INTEGER, "

Repeat for both tables.

Finally either increment the database version or uninstall your app so that onCreate() gets run again, either via onUpgrade() or directly. When is SQLiteOpenHelper onCreate() / onUpgrade() run?

edited May 23 '17 at 10:24

Community

1
1

answered Apr 23 '15 at 14:50

laalto

150,114
66
286
303

Thank you, I didn't notice that! – esseara Apr 23 '15 at 14:56

score 1 · Answer 2 · answered Apr 23 '15 at 13:27

In the constructor of your class extending SQLiteOpenHelper increase the version of your DB for instance:

public MyDBHelper(Context context) {
    super(context,DATABASE_NAME,null,DATABASE_VERSION);
}

Where DATABASE_VERSION should be an integer with a value greater than the one you used in the previous version of your DB schema

score 1 · Accepted Answer · edited May 23 '17 at 12:34

1

Try putting @Override before your onUpgrade

Here's a link to an SO question that covers when and why calling @Override is important.

edited May 23 '17 at 12:34

Community

1
1

answered Apr 23 '15 at 14:50

Cai

5,063
3
18
23

Um, this could not have been the problem. – laalto Apr 23 '15 at 14:58
But OP was missing an @Override call before his `onUpgrade`. Wouldn't that have constituted to his `onUpgrade` not getting called and invoked properly? – Cai Apr 23 '15 at 15:06
@Astatine0936 If there was a problem with the overriding, there would have been a compile-time error rather than a runtime one. `@Override` annotations are generally good but not strictly required. – laalto Apr 23 '15 at 15:09
1

I see, in which case why did it suddenly work then ? What could have cause `onUpgrade` to not be called properly? Also just to be clear I'm not trying to argue with you, in case that's how it's coming across ( relaying intent through text isn't easy ). I want to understand just as much as the OP what the problem was, it's cause and solution as I am just learning myself. – Cai Apr 23 '15 at 15:13
See my answer and the linked question for OP's problem, solution and further information. – laalto Apr 23 '15 at 15:23

Android SQLite won't execute onUpgrade()

3 Answers3