CODEWITHSUNDEEP
androidandroid-sqlite
belosand
belosand

Reputation: 129

Android SQLite add new columns to table

I have a SQLite database in my app, and now I've done some changes to the app, the values etc. and need to rename (or delete and add new) column.

I've first just renamed the column name, but now i get the 

sqlite.sqliteexception no such column error...

Should I use some different method for changing the table (column names) instead of this amateur straight forward approach which obviously returns this error?

UPDATE

The trick is just in changing the database version:

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

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

            Log.w(DatabaseManidzer.class.getName(),"Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");

            db.execSQL("DROP TABLE IF EXISTS " + TABLE_VNOSI);
            onCreate(db);

        }

Upvotes: 1

Views: 8747

Answers (4)

Noor Hossain
Noor Hossain

Reputation: 1831

The following class is a complete example for adding a column within helper class. Assume the old db version was 1. Now you have to increase the db version :

public static class DBhelperDarsul extends SQLiteOpenHelper {

    // TABLE INFORMATTION
    public static final String TABLE_MEMBER = "member";
    public static final String MEMBER_ID = "_id";
    public static final String MEMBER_NAME = "name";
    public  static final String MEMBER_PHOTO= "MEMBER_PHOTO";

    // DATABASE INFORMATION
    //  static final String DB_NAME = "MEMBER.DB";
    static final int DB_VERSION = 3;

    /*
     To  Adding A nuew Column :
     1. add the column in Create Table Statement.
     2. upgrade the db Version. 
     3. in OnUpgrade method : execute the column as follows  :

                 String sql = "ALTER TABLE " + TABLE_MEMBER + " ADD COLUMN " +
                "MEMBER_PHOTO" + " TEXT NOT NULL DEFAULT '' ";
                    db.execSQL(sql);


     */


    // TABLE CREATION STATEMENT
    private static final String CREATE_TABLE = "create table "
            + TABLE_MEMBER + "("
            + MEMBER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + MEMBER_NAME  + " TEXT NOT NULL, "
            + MEMBER_PHOTO + " TEXT NOT NULL);";




    // playebleFilePath = Environment.getExternalStorageDirectory() + "/tilawaat/" + qari_name+"/"+savedFileName + ".mp3";
    public DBhelperDarsul(Context context, String dbName) {

        super(new DatabaseContextForDars(context), dbName, null, DB_VERSION);
        //super(context, DB_NAME, null,DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

        // new Version 3 :

        String sql = "ALTER TABLE " + TABLE_MEMBER + " ADD COLUMN " +
                "MEMBER_PHOTO" + " TEXT NOT NULL DEFAULT '' ";
        db.execSQL(sql);

        
    }
}

So, the actual procedure is you have to do at least three things within the above class. see brief explanation in my another answer.

Upvotes: 0

sssvrock
sssvrock

Reputation: 569

if DB version : 6 Ex : There is a table with 5 columns

When you upgrade to : 7

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

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

Regards Vinod

Upvotes: 0

nakov
nakov

Reputation: 14268

You can simply add an ALTER TABLE query in the onUpgrade method, and then don't forget to increase the DB version in your helper class, that way you will keep the existing data in the table. Or if you are still in development just drop the table and re-create it with the new changes. If you give me an example of what you are trying to succeed I might help you more.

Thanks.

Upvotes: 0

DArkO
DArkO

Reputation: 16110

Yes, if you are using a DatabaseHelper then there is a way to upgrade the database schema.

you can find plenty of tutorials on doing this.

Here is one. Android update SQLite DB schema?

The method you are looking for is onUpgrade

And don't rename the variables which reference the actual table columns unless you change the actual column names first. You will have to use sql queries to do what you want.

Upvotes: 3

Related Questions