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

Question

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);
        }
}

earlier I just used it like super(context, Databasename, null, 1); but then i used super(context, Databasename,null,DATABASE_VERSION); And it's value is = 2 — NIKE PICK
– NIKE PICK, Commented Dec 25, 2015 at 20:34
Would take ages for me to do that I don't have my phone to trace errors and my AVD is still downloading at 25% — NIKE PICK
– NIKE PICK, Commented Dec 25, 2015 at 20:39

Karakuri · Accepted Answer · 2015-12-25 22:52:47Z

5

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

Change the statements that create the schema in onCreate(). New installs will go through this method call and have their schema created here.
Provide a suitable upgrade step in onUpgrade(). Existing users who already have a database and update the app will go through this method call.
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).

edited Dec 25, 2015 at 22:52

answered Dec 25, 2015 at 22:47

Karakuri

38.6k12 gold badges91 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NIKE PICK Over a year ago

Thanks man. You helped me out. Thank you. And yea edited that Storage thing a bit to share it on this site so it wasn't the bug. The actual code to alter table worked perfectly well for med. Although then I had to fiddle with my code a bit to manipulate the null values.ALso I CAN't figure out why DROP table didn't worked.But Thanks be sure to check out my update I made with your help. Download my app from here its a great looking Notepad with hell a lot of customizations,security and sharing functions . [link](play.google.com/store/apps/details?id=com.pickstyleworx.writer )

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related