CODEWITHSUNDEEP
androiddatabasesqliteupgrade
NIKE PICK
NIKE PICK

Reputation: 91

Android - How to make changes in SQLite table columns.(Add new column)

I m using SQLite database of Android to save text. I earlier had 2 columns and the table was crated like this

db.execSQL("create table Storage(id integer PRIMARY KEY AUTOINCREMENT,title text,body text)");

Afterwards I had a need of adding a new column so i just added a new line in the above code anf then it looked like this

db.execSQL("create table EasyPadStorage(id integer PRIMARY KEY AUTOINCREMENT,title text,body text,color text)"); //Added a column named colors

Now whenever i start the app it throws the crash message saying my app has unfortunately closed. I did some research and found a few soultions but no one helped. I used the following onUpgrade methods

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

    if (oldVersion == 1 && newVersion == 2) {
        db.execSQL("DROP TABLE IF EXISTS" + Tablename);
        db.execSQL("DROP TABLE IF EXISTS" + Tablename2);
        onCreate(db);
    }

}

I do not have any hesitation to use drop the table but if ALTER could work please guide me how to. Either by dropping the table or by using ALTER i just want that my app doesn't crash because of upgrade in tables When i push the update. My database class earlier looked like.

public class SaveN extends SQLiteOpenHelper {

public static final String Databasename = "easypad.db";
public static final String Title = "title";
public static final String Body = "body";
public static final String Tablename = "EasyPadStorage";
public static final String Tablename2 = "details";

Context context;
public SaveN(Context context) {
    super(context, Databasename, null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table Storage(id integer PRIMARY KEY AUTOINCREMENT,title text,body text)");
    db.execSQL("create table details(name text,image blob)");

}

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

    db.execSQL("DROP TABLE IF EXISTS" + Tablename);
    db.execSQL("DROP TABLE IF EXISTS" + Tablename2);



}

And Now after the changes I've made and methods I've tried to not let my App crash(but failed) looks like this

public SaveN(Context context) {
        super(context, Databasename,null,DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table Storage(id integer PRIMARY KEY AUTOINCREMENT,title text,body text,color text)");
        db.execSQL("create table details(name text,image blob)");
}

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 if (oldVersion == 1 && newVersion == 2) {
            db.execSQL("DROP TABLE IF EXISTS" + Tablename);
            db.execSQL("DROP TABLE IF EXISTS" + Tablename2);
            onCreate(db);
        }
}

Upvotes: 2

Views: 3681

Answers (1)

Karakuri
Karakuri

Reputation: 38605

When you need to update the schema of your database you need to do three things:

  1. Change the statements that create the schema in onCreate(). New installs will go through this method call and have their schema created here.
  2. Provide a suitable upgrade step in onUpgrade(). Existing users who already have a database and update the app will go through this method call.
  3. Increase the version number of your database. Without this, Android cannot tell that an upgrade (see #2) is necessary.

It appears you did step #1 already by adding ,color text in your creation statement. It's not clear whether you did step #3 because in one version you use a literal 1 and in another you have a named constant DATABASE_VERSION. As long as this value is greater than 1, then you have completed step #3.

For step #2, you currently have code to drop your tables and go to onCreate() to build them again. This isn't necessary for the upgrade you are doing (adding a column), instead you can do

switch (oldVersion) {
    case 1:
        db.execSQL("ALTER TABLE Storage ADD COLUMN color text");
        // no break, in case user is upgrading multiple versions
}

In general this is preferable because dropping tables will result in losing user data (unless temporarily store that data elsewhere and restore it later).

Aside: Your code is prone to bugs because you have named constants for things like table names and column names, but you are inconsistent between using those constants vs typing them out manually in your SQL statements. For example

public static final String Tablename = "EasyPadStorage";
...
db.execSQL("create table Storage(...)");
...
db.execSQL("DROP TABLE IF EXISTS" + Tablename);

Notice the DROP TABLE statement will actually not do anything because the table was created with the name Storage--typed manually in SQL--but your constant has the value EasyPadStorage. Also, you would need a space after the EXISTS or the SQL will be invalid, which may be the source of the crash you alluded to (but we can't confirm that without a stack trace).

Upvotes: 5

Related Questions