How to upgrade an SQLite table in app without SQLiteOpenHelper

I have created a table in my app:

public  static SQLiteDatabase attendancedb = this.openOrCreateDatabase("Attendance", Context.MODE_PRIVATE, null);

attendancedb.execSQL("CREATE TABLE IF NOT EXISTS attendance ( subName VARCHAR , attend VARCHAR, total VARCHAR, PRIMARY KEY (subName))");

How can I add one more column (notes) in the table?

If I do this:

attendancedb.execSQL("CREATE TABLE IF NOT EXISTS attendance ( subName VARCHAR , attend VARCHAR, total VARCHAR,notes VARCHAR, PRIMARY KEY (subName))");

the updated app is crashing on accessing the notes data, but working if I reinstall the app.

Or will it be efficient to use Alter query for now and future pupose?

I have not used the SQLiteOpenHelper class.

---

Here's the Solution

---

public class DatabaseHelper extends SQLiteOpenHelper {

public DatabaseHelper(Context context) {

    super(context, "Attendance", null, 1);

}

@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {

    if(checktable(sqLiteDatabase)==0){ //for old user
        try {
            sqLiteDatabase.execSQL("ALTER TABLE attendance ADD notes VARCHAR;");
        }catch (Exception e){  //for new user

            sqLiteDatabase.execSQL("CREATE TABLE  attendance ( subName VARCHAR , attend VARCHAR, total VARCHAR,notes VARCHAR, PRIMARY KEY (subName))");
        }
    }
    }

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int old, int newVersion) {


}



int checktable(SQLiteDatabase db){

    try{
    Cursor c= db.rawQuery("SELECT notes FROM  attendance", null);


        return 1;
    }catch (Exception e){

        return 0;
    }

    }
  }

You should have used SQLiteOpenHelper in the first place. Anyway, I recommend that you start using SQLiteOpenHelper now.

Since your old app did not set a version (with PRAGMA user_version), the existing database file will have version 0, which is interpreted by SQLiteOpenHelper as empty, so it will call onCreate.

So your onCreate method must check if the attendance table already exists. If not, create the database from scratch; if yes, upgrade the table from version 0 to version 1. (To add a column, use ALTER TABLE.)

All future upgrades can then use the normal onUpgrade mechanism.