1

I'm a little bit confused about onUpgrade. I'm developing my app and in the proces of developing I did changed the DATABASE_VERSION five times so it is 6 now.

Everything was going smoothly till I totaly uninstalled application from my phone and builded it again. Since then I'm getting errors that none of my tables that I'm asking to are existing...

I think that now, when my DATABASE_VERSIONin project is set to 6 and newly installed app gets it on 6 at start, onUpgrade() is not called.

Question is how to provide all my queries set in onUpgrade method for new instalations?

I know that onUpgrade() is called whatever the getWritableDatabase() is called. But my app crashes on this lane:

Cursor spinner_cursor = db.rawQuery("SELECT value1 AS _id, value2 FROM tab2", null);

Which is preceded by lane:

SQLiteDatabase db = new DBHelper(this).getWritableDatabase();

I am getting this error in logCat:

no such table: car (code 1): , while compiling: SELECT value1 AS _id, value2 FROM tab2

my DBHelper.class looks like

public class DBHelper extends SQLiteOpenHelper {

private static final String DATABASE_NAME = "DataBase1";
private static final int DATABASE_VERSION = 6; 
private static final String CREATE_QUERY =
        "CREATE TABLE tab1 (id, value1, value2, etc)";

private static final String QUERY_2 =
        "CREATE TABLE tab2 (id, value1, value2, etc)";

private static final String QUERY_3 =
        "CREATE TABLE tab3 (id, value1, value2, etc)";

private static final String QUERY_4 =
        "ALTER TABLE tab2 ADD COLUMN value3 VARCHAR(10) NULL";

private static final String QUERY_5 =
        "ALTER TABLE tab2 ADD COLUMN value4 VARCHAR(10) NULL";

private static final String QUERY_6 =
        "ALTER TABLE tab2 ADD COLUMN value5 VARCHAR(10) NULL";

private static final String QUERY_7 =
        "ALTER TABLE tab3 ADD COLUMN value3 VARCHAR(10) NULL"; 

private static final String QUERY_8 =
        "ALTER TABLE tab3 ADD COLUMN value4 VARCHAR(10) NULL";

private static final String QUERY_9 =
        "ALTER TABLE tab3 ADD COLUMN value5 VARCHAR(10) NULL"; 

private static final String QUERY_10 =
        "ALTER TABLE tab3 ADD COLUMN value6 VARCHAR(10) NULL"; 

private static final String QUERY_11 =
        "ALTER TABLE tab3 ADD COLUMN value7 VARCHAR(10) NULL"; 

private static final String QUERY_12 =
        "DROP TABLE tab3";

private static final String QUERY_13 =
        "CREATE TABLE tab3 (id, value1, value2, etc)";


public DBHelper(Context context){

    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    Log.e("DATABASE OPERATIONS", "Database created / opened");
}

@Override
public void onCreate(SQLiteDatabase db) {

    db.execSQL(CREATE_QUERY);
}

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

    if (oldVersion < 3) {
        db.execSQL(QUERY_2);
        db.execSQL(QUERY_3);
        db.execSQL(QUERY_4);
        db.execSQL(QUERY_5);
        db.execSQL(QUERY_6);
    }

    if (oldVersion < 4) {
        db.execSQL(QUERY_7);
        db.execSQL(QUERY_8);
        db.execSQL(QUERY_9);
        db.execSQL(QUERY_10);
    }

    if (oldVersion < 5){
        db.execSQL(QUERY_11);
    }

    if (oldVersion < 6){
        db.execSQL(QUERY_12);
        db.execSQL(QUERY_13);
    }

Thanks in advance!

relliz
  • 19
  • 7
  • Have you released your app already? If not, then this whole upgrade stuff is unnecessary. – juergen d Sep 21 '16 at 21:49
  • In `onCreate()` you should create all tables required viz. `tab2`, `tab3` – Zubin Kadva Sep 21 '16 at 21:49
  • Thing is that I did uninstalled app an then generate apk and inatalled app from it. That when problems starts. You say I should get rid of all those alter and recreate queries and put it all in one and them in to the onCreate? How does it works after app release? – relliz Sep 21 '16 at 22:13
  • You need to call all the queries in the onCreate() method. – HomeIsWhereThePcIs Sep 21 '16 at 23:25
  • By the way, I think `String[] queries` is more preferred over `String Query_X`. Then, you could loop over a range of strings. – OneCricketeer Sep 21 '16 at 23:32
  • From the "Related" sidebar: http://stackoverflow.com/questions/21881992/when-is-sqliteopenhelper-oncreate-onupgrade-run – laalto Sep 22 '16 at 05:56

1 Answers1

2
@Override
public void onCreate(SQLiteDatabase db) {

     db.execSQL(CREATE_QUERY);
     db.execSQL(QUERY_2);
     db.execSQL(QUERY_3);
     db.execSQL(QUERY_4);
     db.execSQL(QUERY_5);
     db.execSQL(QUERY_6);
     db.execSQL(QUERY_7);
     db.execSQL(QUERY_8);
     db.execSQL(QUERY_9);
     db.execSQL(QUERY_10);
     db.execSQL(QUERY_11);
     db.execSQL(QUERY_12);
     db.execSQL(QUERY_13);

}

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

    if (oldVersion < 3) {
        db.execSQL(QUERY_2);
        db.execSQL(QUERY_3);
        db.execSQL(QUERY_4);
        db.execSQL(QUERY_5);
        db.execSQL(QUERY_6);
    }

    if (oldVersion < 4) {
        db.execSQL(QUERY_7);
        db.execSQL(QUERY_8);
        db.execSQL(QUERY_9);
        db.execSQL(QUERY_10);
    }

    if (oldVersion < 5){
        db.execSQL(QUERY_11);
    }

    if (oldVersion < 6){
        db.execSQL(QUERY_12);
        db.execSQL(QUERY_13);
    }
OneCricketeer
  • 179,855
  • 19
  • 132
  • 245
HomeIsWhereThePcIs
  • 1,273
  • 1
  • 19
  • 37
  • Ok I get it why to put all queries in the onCreate method but then why is 'onUpgrade' even needed? The same problem will be if I release my app and do any changes in db structure. Correct me if I'm wrong. I just dont get it how does it works after release. I'm afraid that people will have same crashes as I have now. – relliz Sep 22 '16 at 05:07
  • There are two scenarios 1. The database hasn't been initialized yet. In this case the onCreate() method is called 2. The database exists on the device with the version x, but you have installed a new version of the application with an updated database with version y where y>x. In this case the onUpgrade() is called – HomeIsWhereThePcIs Sep 22 '16 at 16:01