Add 2 column to sqlite db Android

Question

i can't add 2 column in my existing db. i read any topic on the web but i can't fix my problem. may i have a complete personalized answer to my problem? naturally i don't wanna lose existing record, just alter it! i want to add 2 column (phone (integer), automex (text)) to my db created in this way..

package com.ozzem.mybirthdaylite;



import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class dbMyBirthday {

    SQLiteDatabase db;

    private Context mContext;

    private DbHelper mDbHelper;

     private static final String DB_NAME="dbmyBirthday";
     private static final int DB_VERSION=1;

     //costruttore crea anche il DbHelper
     public dbMyBirthday(Context ctx){
         mContext=ctx;
         mDbHelper=new DbHelper(ctx, DB_NAME, null, DB_VERSION);       
 }


     public void open(){  //il database su cui agiamo è leggibile/scrivibile
         db=mDbHelper.getWritableDatabase();

 }

 public void close(){ //chiudiamo il database su cui agiamo
         db.close();
 }



 public void deletePerson(int idDel){ //metodo per inserire i dati

     String where = "id = " + idDel;
     db.delete(Person.PERSON_TABLE, where,null);
//     Log.d("db_call", "Record of id["+idDel+"] deleted");

}


 public void insertPerson(long l,String name,String birthday){ //metodo per inserire i dati
         ContentValues cv=new ContentValues();
         cv.put(Person.UID, l);
         cv.put(Person.NAME, name);
         cv.put(Person.BIRTHDAY, birthday);

         db.insert(Person.PERSON_TABLE, null, cv);
//         Log.d("db_call", "Record of uid["+uid+"] insered");

 }

 public boolean exist(long uid){
     boolean ex = false;

     Cursor c= db.rawQuery("SELECT COUNT(*) FROM " + Person.PERSON_TABLE + " WHERE uid = "+ "'" + uid + "'", null);
        c.moveToFirst();
      int jcount = c.getInt(0);

      if (jcount >0){
        ex=true;            
    }
     return ex;
 }



 public void updatePersonFromId(int id,String name,String birthday){

     ContentValues cv=new ContentValues();
//   cv.put(Person.UID, id);
     cv.put(Person.NAME, name);
     cv.put(Person.BIRTHDAY, birthday);

     String where = "id = " + "'"+id+"'";
     db.update(Person.PERSON_TABLE, cv, where, null);
//     Log.d("db_call", "Record of id["+id+"] updated");

 }

 public void updatePersonFromUid(long l,String name,String birthday){

     ContentValues cv=new ContentValues();
     cv.put(Person.UID, l);
     cv.put(Person.NAME, name);
     cv.put(Person.BIRTHDAY, birthday);

     String where = "uid = " + "'"+l+"'";
     db.update(Person.PERSON_TABLE, cv, where, null);
//     Log.d("db_call", "Record of uid["+uid+"] updated");

 }

 public Cursor fetchAllBirthday(){ //metodo per fare la query di tutti i dati
//   Log.d("db_call", "Fetching all birthdays");    
     return db.query(Person.PERSON_TABLE, null,null,null,null,null,null);               
 }

 public static class Person {  // i metadati della tabella, accessibili ovunque

        static final String PERSON_TABLE = "person";
         static final String ID = "id";
         static final String UID = "uid";
         static final String NAME = "name";
         public static final String BIRTHDAY = "birthday";

 }

 private static final String PERSON_TABLE_CREATE = "CREATE TABLE IF NOT EXISTS "  //codice sql di creazione della tabella

                + Person.PERSON_TABLE + " (" 
                 + Person.ID + " integer primary key autoincrement, "
                 + Person.NAME + " text not null, "
                 + Person.UID + " integer not null, "
                 + Person.BIRTHDAY + " text not null );";



     private class DbHelper extends SQLiteOpenHelper { //classe che ci aiuta nella creazione del db

         public DbHelper(Context context, String name, CursorFactory factory,int version) {
                 super(context, name, factory, version);
         }

    @Override
    public void onCreate(SQLiteDatabase _db) {
        // TODO Auto-generated method stub

         _db.execSQL(PERSON_TABLE_CREATE);


    }

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

    }

}
}

Kuffs · Accepted Answer · 2011-12-07T12:48:28.977

0

Increase DB_Version to 2 then in your OnUpgrade override of your OpenHelper run your upgrade code

e.g:

    if (oldVersion==1 && newVersion==2) {
      db.execSQL("ALTER TABLE " + TableName + " ADD COLUMN " + ColumnName1);
      db.execSQL("ALTER TABLE " + TableName + " ADD COLUMN " + ColumnName2);
    }

Also modify your inital code so that the new field is created at the same time as the table for those who do not have a version 1 database to upgrade. i.e Those who create a version 2 database first.

edited Dec 07 '11 at 12:48

answered Dec 07 '11 at 12:05

Kuffs

35,581
10
79
92

you say this? public static class Person { // i metadati della tabella, accessibili ovunque static final String PERSON_TABLE = "person"; static final String ID = "id"; static final String UID = "uid"; static final String NAME = "name"; public static final String BIRTHDAY = "birthday"; } – ozzem Dec 07 '11 at 12:12
i'm sorry...i have to modify class Person with new 2 fields? and..wich is the way for add to column? – ozzem Dec 07 '11 at 12:25
You asked how to add columns to your database. This is the answer I gave. The person class is not relevant to your question. – Kuffs Dec 07 '11 at 12:31
@ozzem Why don't you try First to check SELECT col from TABLE ,for Checking that columns exist or no and then go for db.execSQL to execute query to alter table.this Should be done on your First Class in application. – Herry Dec 07 '11 at 12:32
@Herry this is not the way it should be done. The standard SqliteOpenHelper functions provide a framework for upgrading databases. You are suggesting that he ignore the correct\standard way of doing things. http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html – Kuffs Dec 07 '11 at 12:36
@Kuffs yes..but for "2 new columns", i have to duplicate db.execSQL("ALTER TABLE " + TableName + " ADD COLUMN " + ColumnName); ? – ozzem Dec 07 '11 at 12:42
yes you would need 2 update statements. Sorry I assumed you would know this. I only gave an example of how it would be done not an entire code sample. I have updated my answer. – Kuffs Dec 07 '11 at 12:47
@Kuffs so, my way for create a db is wrong, i don't extended openhelper class..there is a way for do an alter table with my managing db class? – ozzem Dec 07 '11 at 14:32
Something that I don't think is considered here is when the new version is greater than 2. What if the user skips a version (goes from 1 to 3 for example). It needs to check to see if the column exists. – Barnesy Nov 08 '13 at 00:41
Version 3 does not need to considered as that wasn't the question that was asked. This answer is covering a one time upgrade from version 1 to 2. Of course if the use case is different then the answer would be different. That did not qualify for a down vote. Every answer on SO would be eligible for down voting if we applied your logic. Edit the answer if you think it could be improved. – Kuffs Nov 08 '13 at 06:46
@Barnesy you simply need to replace the `newVersion == 2` part by a `newVersion >= 2`. This answer doesn't deserve a downvote. – Marc Plano-Lesay Dec 12 '13 at 13:15
Can columnName1 and columnName2 be same. I am getting an error, dublicate column name. Please help. Due to above code or any other reason.@Kuffs – Sumit Kumar Nov 08 '17 at 14:45

score 0 · Answer 2 · edited May 23 '17 at 12:03

0

@ozzem Here are Some Step You need to perform on your application Start Class .

STEP 1. Check that 2 column already exist in database or not ? See here SO Link is here

STEP 2. Make sure that there are not 2 column in database that you want to add after confirm that Run SQL Query Like this

ALTER TABLE PERSON_TABLE ADD phone INTEGER DEFAULT 0
ALTER TABLE PERSON_TABLE ADD automex TEXT

you can also provide default value in this column when they are add in Table.

edited May 23 '17 at 12:03

Community

1
1

answered Dec 07 '11 at 12:06

Herry

7,037
7
50
80

This is not recommended. The SQLiteOpenHelper provides a built in framework for version management of a database. The upgrades should be performed by that. http://goo.gl/WPSiZ – Kuffs Dec 07 '11 at 12:45
@kuffs Here are my Question is He want his data in Table and OnUpgrade override when this method is called. – Herry Dec 07 '11 at 12:58
The onUpgrade method does not alter any data unless you specifically execute SQL statements that cause this. The confusion probably arises because most demos on this subject tend to delete and recreate the database in this method but in real life, we would only upgrade the database NOT replace it. – Kuffs Dec 07 '11 at 13:03
From the docs: Called when the database needs to be upgraded. The implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version. The SQLite ALTER TABLE documentation can be found here. If you add new columns you can use ALTER TABLE to insert them into a live table. If you rename or remove columns you can use ALTER TABLE to rename the old table, then create the new table and then populate the new table with the contents of the old table. – Kuffs Dec 07 '11 at 13:05
@Kuffs when i need upgrade in Database to new schema change i have to manually change DB_Version or increase to get called my Query in onUpgrade method am i right? – Herry Dec 07 '11 at 13:06
Yes. If you use a SqliteOpenHelper the version numbers are compared and either the onUpgrade or onDowngrade method is called depending on whether the new version is higher or lower than the existing database. If the version numbers are the same, onUpgrade is never called. – Kuffs Dec 07 '11 at 13:09
Your onCreate method should always create the current version of your schema as noted by your DB_Version field. – Kuffs Dec 07 '11 at 13:10
@Kuffs i will sure note this Thanks for Help. – Herry Dec 07 '11 at 13:12
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/5659/discussion-between-herry-and-kuffs) – Herry Dec 07 '11 at 13:12

Add 2 column to sqlite db Android

2 Answers2