CODEWITHSUNDEEP
androiddatabasesqlite
Aamirkhan
Aamirkhan

Reputation: 5784

How to add new Column to Android SQLite Database?

I have one problem with Android SQLite database.

I have one table which contains one field.StudentFname and that application is working fine with Android 2.3.1 and now if I add another field then my application is not working properly.

Can anyone help me who knows database very well,

Upvotes: 92

Views: 110456

Answers (13)

Daniel Wilson
Daniel Wilson

Reputation: 19824

Noticed no answers look for the existence of a column:

Cursor cursor = database.rawQuery("SELECT * FROM " + MY_TABLE, null);
int columnIndex = cursor.getColumnIndex(MY_NEW_COLUMN);
if (columnIndex < 0) {
   database.execSQL("ALTER TABLE " + MY_TABLE + " ADD COLUMN " + MY_NEW_COLUMN + " TEXT");
}

Upvotes: 1

beginner
beginner

Reputation: 2508

first, increment the database version number. and then in onUpgrade method, you can check if the column exists already if not then only add the column

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   if(!existsColumnInTable(db, TABLE_NAME, COLUMN_NAME)){
      db.execSQL("ALTER TABLE foo ADD COLUMN COLUMN_NAME INTEGER DEFAULT 0");
 } 
 }

private boolean existsColumnInTable(SQLiteDatabase inDatabase, String inTable, String columnToCheck) {
    Cursor cursor = null;
    try {
        // Query 1 row
        cursor = inDatabase.rawQuery("SELECT * FROM " + inTable + " LIMIT 0", null);

        // getColumnIndex() gives us the index (0 to ...) of the column - otherwise we get a -1
        if (cursor.getColumnIndex(columnToCheck) != -1)
            return true;
        else
            return false;

    } catch (Exception Exp) {
        return false;
    } finally {
        if (cursor != null) cursor.close();
    }
}

this method is extracted from this link Checking if a column exists in an application database in Android

Upvotes: 2

Shahzad Ahmad
Shahzad Ahmad

Reputation: 29

Simply change in your code

private final int DATABASE_VERSION = 1;

to

private static final int DATABASE_VERSION =2;

Upvotes: 2

sssvrock
sssvrock

Reputation: 569

I have done the following approach, it is resovled for me

if DB version : 6

Ex : There is a table with 5 columns

When you upgrade to : 7 ( I am adding 1 new column in the 3 tables)

 1. We need to add the columns when creating a table

 2. onUpgrade method:

 if (oldVersion < 7) 
 { 
    db.execSQL(DATABASE_ALTER_ADD_PAPER_PAID);
    db.execSQL(DATABASE_ALTER_LAST_UPLOADED);
    db.execSQL(DATABASE_ALTER_PAPER_LABEL); 
 }

Where : "DATABASE_ALTER_ADD_PAPER_PAID" is query.

EX: public static final String DATABASE_ALTER_ADD_PAPER_PAID = "ALTER TABLE "
                + TableConstants.MY_PAPERS_TABLE + " ADD COLUMN " + COLUMN_PAPER_PAID + " TEXT;";

After above two operation it will works fine for the fresh install user and app upgrade user

Upvotes: 5

Blasanka
Blasanka

Reputation: 22437

To add a new column to the table you need to use ALTER. In android you can add the new column inside the onUpgrade().

You may be wonder, how onUpgrade() will add the new column?

When you implementing a subclass of SQLiteOpenHelper, you need to call superclass constructor: super(context, DB_NAME, null, 1); in your class constructor. There I have passed 1 for version.

When I changed the version 1 to above(2 or greater), onUpgrade() will invoked. And perform the SQL modifications which I intend to do. My class constructor after changed the version:

public DatabaseHelper(Context context) {
    super(context, DB_NAME, null, 2);//version changed from 1 to 2
}

SQL modifications checks like this, superclass constructor compares the version of the stored SQLite db file with the version that I passed to super(). If these(previous and now) version numbers are different onUpgrade() gets invoked.

Code should look like this:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // add new columns to migrate to version 2
    if (oldVersion < 2) { 
        db.execSQL("ALTER TABLE " + TABLE_NAME + "ADD COLUMN school VARCHAR(250)");
    }
    // add new columns to migrate to version 3
    if (oldVersion < 3) { 
        db.execSQL("ALTER TABLE " + TABLE_NAME + "ADD COLUMN age INTEGER");
    }
}

Upvotes: 2

DevYudh
DevYudh

Reputation: 2737

you can use ALTER TABLE function on your onUpgrade() method, like this:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  // If you need to add a column
  if (newVersion > oldVersion) {
     db.execSQL("ALTER TABLE foo ADD COLUMN new_column INTEGER DEFAULT 0");
  }
}

Obviously, the SQLite will differ depending on the column definition.

Upvotes: 201

Digvij Borda
Digvij Borda

Reputation: 1

The easiest way to create a new column on a table is add some SQL to the onUpgrade() method in SQLiteOpenHelper class. Like:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch (oldVersion) {
        case 1:
            db.execSQL(SQL_MY_TABLE);

        case 2:
            db.execSQL("ALTER TABLE myTable ADD COLUMN myNewColumn TEXT");
    }
}

Upvotes: 0

Anand
Anand

Reputation: 1

I came across link while searching for the same issue. It explains how to use upgrade. https://thebhwgroup.com/blog/how-android-sqlite-onupgrade

it explains why to use this below code

 @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
             db.execSQL(DATABASE_ALTER_TEAM_1);
        }
        if (oldVersion < 3) {
             db.execSQL(DATABASE_ALTER_TEAM_2);
        }
    }

Upvotes: 0

Himanshu arora
Himanshu arora

Reputation: 161

I think we should not check condition like that

 if (newVersion > oldVersion) {
 }

because if we use this , it means every time when we increase the database version then onUpdrade() will call and condition will be true , so we should check like that 

if(oldVersion==1) 
{

}

so in this case if old version is 2 then condition will be false and user with old version 2 will not update the database(which user wants only for version 1), because for those users database had already updated .

Upvotes: 3

E. A. Bagby
E. A. Bagby

Reputation: 930

Perhaps a slightly more elegant approach using switch instead of if/then that will upgrade from any schema to the most recent schema...

Here's also a decent page on the syntax for altering a table: http://alvinalexander.com/android/sqlite-alter-table-syntax-examples

public static final String TABLE_TEAM = "team";
public static final String COLUMN_COACH = "coach";
public static final String COLUMN_STADIUM = "stadium";


private static final String DATABASE_ALTER_TEAM_TO_V2 = "ALTER TABLE "
    + TABLE_TEAM + " ADD COLUMN " + COLUMN_COACH + " TEXT;";

private static final String DATABASE_ALTER_TEAM_TO_V3 = "ALTER TABLE "
    + TABLE_TEAM + " ADD COLUMN " + COLUMN_STADIUM + " TEXT;";


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

    switch (oldVersion)
    {
        case 1:
            //upgrade from version 1 to 2
            db.execSQL(DATABASE_ALTER_TEAM_TO_V2);
        case 2:
            //upgrade from version 2 to 3
            db.execSQL(DATABASE_ALTER_TEAM_TO_V3);

        //and so on.. do not add breaks so that switch will
        //start at oldVersion, and run straight through to the latest

    }

}

Upvotes: 16

PFranchise
PFranchise

Reputation: 6752

I came across this thread when needing help on my own app, but saw issues with many of the answers. I would recommend doing the following:

private static final String DATABASE_ALTER_TEAM_1 = "ALTER TABLE "
    + TABLE_TEAM + " ADD COLUMN " + COLUMN_COACH + " string;";

private static final String DATABASE_ALTER_TEAM_2 = "ALTER TABLE "
    + TABLE_TEAM + " ADD COLUMN " + COLUMN_STADIUM + " string;";

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 2) {
         db.execSQL(DATABASE_ALTER_TEAM_1);
    }
    if (oldVersion < 3) {
         db.execSQL(DATABASE_ALTER_TEAM_2);
    }
}

You want to make sure the code will work when users upgrade more than 1 version and that the update statement only runs the one upgrade it is needed. For a bit more on this, check out this blog.

Upvotes: 33

dora
dora

Reputation: 2077

@Aamirkhan.i think you would have solved the problem you mentioned in the comments long back ago.i think you didn't increase the data base version. or else the answers here are straight forward.i am writing this because it might help anyone who hasn't increased or changed their data base version number when they are altering a table.

Upvotes: 3

user370305
user370305

Reputation: 109237

The easiest way to do this is to add some SQL to the onUpgrade() method in your SQLiteOpenHelper class. Something like:

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

    // If you need to add a new column
    if (newVersion > oldVersion) {
        db.execSQL("ALTER TABLE student ADD COLUMN student_rollno INTEGER DEFAULT 0");
    }
}

Upvotes: 23

Related Questions