Issue in Sqlite database data clear while update database version in android

Question

Hello friends I am making SQLite database in my application by programmatically.

Here is my code:

    public class DatabaseHandler extends SQLiteOpenHelper {

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION =3;

    // Database Name
    private static final String DATABASE_NAME = "contactsManager";

    // Contacts table name
    private static final String TABLE_CONTACTS = "contacts";

    // Contacts Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_PH_NO = "phone_number";
    private static final String KEY_EMAIL = "email";
    private static final String KEY_ADD = "address";

    private final ArrayList<Contact> contact_list = new ArrayList<Contact>();

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

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
    String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
        + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
        + KEY_PH_NO + " TEXT," + KEY_EMAIL + " TEXT,"+ KEY_ADD + " TEXT  )";
    db.execSQL(CREATE_CONTACTS_TABLE);
        System.out.println("Create");
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);

        // If you need to add a column
        if (newVersion > oldVersion) {

            onCreate(db);
            db.execSQL("ALTER TABLE contacts ADD COLUMN gender TEXT;");

        }
    // Create tables again

    }

    /**
     * All CRUD(Create, Read, Update, Delete) Operations
     */

    // Adding new contact
    public void Add_Contact(Contact contact) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(KEY_NAME, contact.getName()); // Contact Name
    values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone
    values.put(KEY_EMAIL, contact.getEmail()); // Contact Email
    // Inserting Row
    db.insert(TABLE_CONTACTS, null, values);
    db.close(); // Closing database connection
    }

    // Getting single contact
    Contact Get_Contact(int id) {
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,
        KEY_NAME, KEY_PH_NO, KEY_EMAIL }, KEY_ID + "=?",
        new String[] { String.valueOf(id) }, null, null, null, null);
    if (cursor != null)
        cursor.moveToFirst();

    Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
        cursor.getString(1), cursor.getString(2), cursor.getString(3));
    // return contact
    cursor.close();
    db.close();

    return contact;
    }

    // Getting All Contacts
    public ArrayList<Contact> Get_Contacts() {
    try {
        contact_list.clear();

        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_CONTACTS;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
        do {
            Contact contact = new Contact();
            contact.setID(Integer.parseInt(cursor.getString(0)));
            contact.setName(cursor.getString(1));
            contact.setPhoneNumber(cursor.getString(2));
            contact.setEmail(cursor.getString(3));
            // Adding contact to list
            contact_list.add(contact);
        } while (cursor.moveToNext());
        }

        // return contact list
        cursor.close();
        db.close();
        return contact_list;
    } catch (Exception e) {
        // TODO: handle exception
        Log.e("all_contact", "" + e);
    }

    return contact_list;
    }

    // Updating single contact
    public int Update_Contact(Contact contact) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_NAME, contact.getName());
    values.put(KEY_PH_NO, contact.getPhoneNumber());
    values.put(KEY_EMAIL, contact.getEmail());

    // updating row
    return db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",
        new String[] { String.valueOf(contact.getID()) });
    }

    // Deleting single contact
    public void Delete_Contact(int id) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
        new String[] { String.valueOf(id) });
    db.close();
    }

    // Getting contacts Count
    public int Get_Total_Contacts() {
    String countQuery = "SELECT  * FROM " + TABLE_CONTACTS;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    cursor.close();

    // return count
    return cursor.getCount();
    }

   }

Issue is when I upgrade my database version from 1 to 2 at that time new column is added in my database but my old data is deleted so how can I solve this problem? your all suggestion are appreciable.

EDIT

Ya working by solution for both Rajesh Jadav and Amy but when i change version form 2 to 3 it give me error like duplicate column gender. my function is as follows

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
//db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);

    // If you need to add a column
    if (newVersion > oldVersion) {


        db.execSQL("ALTER TABLE contacts ADD COLUMN gender TEXT;");
        db.execSQL("ALTER TABLE contacts ADD COLUMN dob TEXT;");

    }


}

but when i comment line db.execSQL("ALTER TABLE contacts ADD COLUMN gender TEXT;"); then it is working fine so is there are any right way whenever i upgrade database version older changes make comment at that place?

remove `db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);` this line — Amy, Aug 20 '15 at 05:00

Rajesh Jadav · Accepted Answer · 2015-08-20T06:02:24.330

Try 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 contacts ADD COLUMN gender TEXT;");

    }
// Create tables again

}

You have called db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS); in onUpgrade. so it will clear all existed data.

if you have to keep old data remove that query.

EDIT:

There are two solution for duplicate column problem.

First:

You can surround by try catch statement.

try{
      db.execSQL("ALTER TABLE contacts ADD COLUMN gender TEXT;");
       db.execSQL("ALTER TABLE contacts ADD COLUMN dob TEXT;");
   }catch(Exception e){
 }

Second:

You can use this method to check whether Column already exist in table or not.

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

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

    } catch (Exception Exp) {
        // Something went wrong. Missing the database? The table?
        Log.d("... - existsColumnInTable", "When checking whether a column exists in the table, an error occurred: " + Exp.getMessage());
        return false;
    } finally {
        if (mCursor != null) mCursor.close();
    }
}

Simply call:

boolean gender_column_exist = existsColumnInTable(db,"contacts","gender");
if(!gender_column_exist){
db.execSQL("ALTER TABLE contacts ADD COLUMN gender TEXT;");
}
boolean dob_column_exist = existsColumnInTable(db,"contacts","dob");
if(!dob_column_exist){
db.execSQL("ALTER TABLE contacts ADD COLUMN dob TEXT;");
}

hope this helps!

Rajesh Jadav: i did it same but still data remove and it get error like 08-20 05:12:25.219 13718-13718/? E/all_contact﹕ android.database.sqlite.SQLiteException: table contacts already exists (code 1): , while compiling: CREATE TABLE contacts(id INTEGER PRIMARY KEY,name TEXT,phone_number TEXT,email TEXT,address TEXT ) — , Aug 20 '15 at 05:15
remove onCreate(db); from method or see try edited answer method — Rajesh Jadav, Aug 20 '15 at 05:16
@RajeshJadav You're right but it will give same error when he Launch same app again. — Amy, Aug 20 '15 at 05:23

Amy · Answer 2 · 2015-08-20T05:13:03.013

1

Try This:

 @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
   // db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS); //Remove This Line

        // If you need to add a column
        if (newVersion > oldVersion) {

            onCreate(db);
            db.execSQL("ALTER TABLE contacts ADD COLUMN gender TEXT;");

        }
    }

Remove or comment first line from onUpgrade() method. It is deleteing your old table along with the data.

edited Aug 20 '15 at 05:13

answered Aug 20 '15 at 05:01

Amy

4,034
1
20
34

Amy: i did it same but still data remove and it get error like 08-20 05:12:25.219 13718-13718/? E/all_contact﹕ android.database.sqlite.SQLiteException: table contacts already exists (code 1): , while compiling: CREATE TABLE contacts(id INTEGER PRIMARY KEY,name TEXT,phone_number TEXT,email TEXT,address TEXT ) – Aug 20 '15 at 05:15
Change your create Table query with `CREATE TABLE IF NOT EXISTS TableName(..)` – Amy Aug 20 '15 at 05:17
see this http://stackoverflow.com/questions/3604310/alter-table-add-column-if-not-exists-in-sqlite – Amy Aug 20 '15 at 05:53
Amy : i m not getting in pragma statement can you post example with my code? – Aug 20 '15 at 06:06

Issue in Sqlite database data clear while update database version in android

2 Answers2