Difficulty in upgrading SQlite table

Question

I have an application running with a working table called ANIMAL. Upon first creating this table it consisted simply of _id and animal_name columns.

Now I am trying to expand on it, including a animal_biography column, however I am having a little difficulty.At first I thought I was just a case of upgrading my CREATE_TABLE statement to include the animal bio:

private static final String DATABASE_CREATE =       
                        "create table " + ANIMALS_TABLE +
                        " (_id integer primary key autoincrement, " + 
                        "animal_name text not null, " +
                        "biography text not null);";

however, looking at the logcat it tells me the column biography does not exist when trying to insert into it.

Now, I have tried to upgrade the database by using the onUpgrade() and including the code

db.execSQL("ALTER TABLE" + DATABASE_NAME);
db.execSQL(DATABASE_CREATE);

but this is not solving the problem either. Does anyone have any pointers on how to go about fixing this problem?

Ok I have managed to find a solution to the problem. I needed to remove the database from the application so it would create a brand new table on startup with the revised code, I deleted the database as follows: File dbFile = getDatabasePath("Path_Name"); dbFIle.delete(); — Php Pete, Apr 16 '11 at 16:22
You could delete the database file just by using DDMS file browser. — GrAnd, Apr 16 '11 at 17:14

score 53 · Accepted Answer · edited Feb 01 '13 at 07:31

If you are using SQLiteOpenHelper it's easy to upgrade a table. You need to implement methods onCreate and onUpgrade and provide current version of your database in class constructor. When updating your table just increment database version number, specify new table creation query in onCreate method and put ALTER TABLE to onUpgrade method to update previous version of table. When Android detects database version mismatch, it will call onUpgrade method automatically. See the example:

public class OpenHelper extends SQLiteOpenHelper {

    private final static int    DB_VERSION = 2;

    public TracksDB(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        final String CREATE_TBL =
            "create table " + ANIMALS_TABLE +
            " (_id integer primary key autoincrement, " + 
            "animal_name text not null, " +
            "biography text not null);";
             db.execSQL(CREATE_TBL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
            final String ALTER_TBL = 
                "ALTER TABLE " + ANIMALS_TABLE +
                " ADD COLUMN biography text not null;";
            db.execSQL(ALTER_TBL);
        }
    }
}

This method of upgrading is the correct way to modify tables without losing user data, especially if the app had been released to public already.

@GrAnd - thanks for your answer, that helped a lot. However that example won't work, as you can't add "not null" column without specifying default value, like: " ADD COLUMN biography text not null default default_biography" — Koger, Nov 29 '11 at 13:21
I have the same problem and I have already implemented above method, I changed the version number, log/debug also shows that onUpgrade is called, and no exception at all (I assume it successfully executed the queries). but when i try to load items (At the moment there are no records as i have dropped the table and re-created) It still throws exception the new column does not exists. uninstalling application also has no effect. — PHP Avenger, Nov 13 '13 at 23:02
So changing the value for version passed in is what signals the OpenHelper to go to onUpgrade? And it's okay to change the "create tables" string once you've incremented as long as the alter tables method is included in the onUpgrade? Please check to make sure I'm not still mixed up, I just needed to clarify — AlleyOOP, Jan 04 '14 at 18:20

Difficulty in upgrading SQlite table

1 Answers1

Linked