Sungpah Lee Sungpah Lee - 26 days ago 6
SQL Question

How can I add another table to the database if there are already several tables in Android?

I have an application with Table1 in the database("DATABASE"). It works correctly.

When I try to put another table named "Table2" into the database and use the item in this new table in the application, the alert message goes like this.


08-28 00:05:58.101: E/AndroidRuntime(6317): Caused by:
android.database.sqlite.SQLiteException: no such table: Table2 (code
1): , while compiling: select _id, MissionNumber, NAME1, NUM1, NAME2,
NUM2 from Table2 08-28 00:05:58.101: E/AndroidRuntime(6317): at
android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native
Method)


But if I erase previous program, and put table2, then it works. The way I did was to put code in the "onCreate" part in the DatabaseHelper.

However I guess, people who will download my program are not automatically dedicated to erase the previous program. So I want the situation that when people download application with the new table2 added, it works correctly.

The way I'm inserting the new table is,

private class DatabaseHelper extends SQLiteOpenHelper {
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

public void onCreate(SQLiteDatabase db) {


try {
String DROP_SQL = "DROP TABLE IF EXISTS " + "Table1";
db.execSQL(DROP_SQL);
} catch(Exception ex) {

}

String CREATE_SQL1 = "create table " + "Table1" + "("
+ " _id integer PRIMARY KEY autoincrement, "
+ " MissionNumber text, "
+ " NAME1 text, "
+ " NUM1 int, "
+ " NAME2 text,"
+ " NUM2 int)";


try {
db.execSQL(CREATE_SQL1);
} catch(Exception ex) {

}
TABLE_NAME = "Table1";

try {

db.execSQL( "insert into " + TABLE_NAME + "(MissionNumber, NAME1, NUM1, NAME2, NUM2) values ('GROUP1', 'lucky_teleport', 0, 'pass', '0');" );
db.execSQL( "insert into " + TABLE_NAME + "(MissionNumber, NAME1, NUM1, NAME2, NUM2) values ('GROUP2', 'no', 0, 'notyet', '0');" );

} catch(Exception ex) {

}


and

try {
String DROP_SQL = "DROP TABLE IF EXISTS " + "Table2";
db.execSQL(DROP_SQL);
} catch(Exception ex) {

}

String CREATE_SQL2 = "create table " + "Table2" + "("
+ " _id integer PRIMARY KEY autoincrement, "
+ " MissionNumber text, "
+ " NAME1 text, "
+ " NUM1 int, "
+ " NAME2 text,"
+ " NUM2 int)";


try {
db.execSQL(CREATE_SQL2);
} catch(Exception ex) {

}
TABLE_NAME = "Table2";

try {

db.execSQL( "insert into " + TABLE_NAME + "(MissionNumber, NAME1, NUM1, NAME2, NUM2) values ('GROUP1', 'lucky_teleport', 0, 'pass', '0');" );
db.execSQL( "insert into " + TABLE_NAME + "(MissionNumber, NAME1, NUM1, NAME2, NUM2) values ('GROUP2', 'no', 0, 'notyet', '0');" );

} catch(Exception ex) {

}


It's not easy for me to fully understand what the database. Please share the good ideas not to erase the previous program to add new table to the database!

Answer

onCreate is called only if the database does not exist and a new database needs to be createdC if you have a previous version of your application installed, a database already exists and the code inside onCreate() is not executed

in order to update your database, you need to override this method from SQLiteOpenHelper:

onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)

for example:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
    String CREATE_SQL2 = "create table " + "Table2" + "("
                         + " _id integer PRIMARY KEY autoincrement, "
                         + " MissionNumber text, "
                         + " NAME1 text, "
                         + " NUM1 int, "
                         + " NAME2 text," 
                         + " NUM2 int)";

    try {
        db.execSQL(CREATE_SQL2);
    } catch(Exception ex) {

    }

    TABLE_NAME = "Table2";

    try {
        db.execSQL( "insert into " + TABLE_NAME + "(MissionNumber, NAME1, NUM1, NAME2, NUM2) values ('GROUP1', 'lucky_teleport', 0, 'pass', '0');" );
        db.execSQL( "insert into " + TABLE_NAME + "(MissionNumber, NAME1, NUM1, NAME2, NUM2) values ('GROUP2', 'no', 0, 'notyet', '0');" )
    } catch(Exception ex) {

    }
}

In order to make your application to call the onUpgrade() method you should pass a DATABASE_VERSION value greater than the one you passed when you first create the database

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

so, if your DATABASE_VERSION constant was equal to 1, just increment it to 2 and you onUpgrade() method will be called.

in case of new installation of your application, the onCreate() method will be called instead of onUpgrade(), so you should implement the table creation code in both methods (or call, in both of them, a method that add your Table2 to the database)