Android SQLite add new columns to table

Question

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

        }

Community · Accepted Answer · 2017-05-23 12:22:28Z

3

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.

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Oct 4, 2013 at 20:12

DArkO

16.2k12 gold badges66 silver badges94 bronze badges

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

Comments

nakov · Accepted Answer · 2013-10-04 22:05:45Z

0

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.

answered Oct 4, 2013 at 22:05

nakov

14.4k14 gold badges74 silver badges129 bronze badges

1 Comment

belosand Over a year ago

Thanks, I already solved the problem by dropping and recreating my table - the app is still in development so there is no problem in doing like that..

sssvrock · Accepted Answer · 2017-09-26 09:57:27Z

0

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

When you upgrade to : 7

We need to add the columns when creating a table
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

answered Sep 26, 2017 at 9:57

sssvrock

5797 silver badges9 bronze badges

Comments

Noor Hossain · Accepted Answer · 2022-05-31 00:37:52Z

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.

Collectives™ on Stack Overflow

Android SQLite add new columns to table

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related