How to properly use databases in Android?

Question

I'm building an app that relies heavily on database usage. However, my emulator is behaving erratically and I can't find any pattern to what/when works and what/when it doesn't.

Below you have my Adapter and Helper class taken straight from an example I found on the web. In this form, it functions perfectly, it adds records, deletes them, updates them and displays them in a list flawlessly.

If I try and change ANYTHING (for example the KEY_TITLE value), the app crashes, even if I create a completely new emulator. I'm completely stumped by this and any help would be greatly welcomed...

public class PirelliDBAdapter {
private static final String DATABASE_NAME="data";
private static final String DATABASE_TABLE="reminders";
private static final int DATABASE_VERSION=1;

public static final String KEY_TITLE="title";
public static final String KEY_BODY="body";

public static final String KEY_LOCATION = "location";
public static final String KEY_TYPE = "type";

public static final String KEY_DATE_TIME="date_time_value";
public static final String KEY_ROW_ID="_id";

private DatabaseHelper mDbHelper;
private SQLiteDatabase db;

private static final String DATABASE_CREATE =
        "create table " + DATABASE_TABLE + " ( "+
                KEY_ROW_ID+ " integer primary key autoincrement, "+
                KEY_TITLE+ " text not null, "+
                KEY_BODY+" text not null, "+
                KEY_DATE_TIME+ " text not null);";

private Context mContext;

public PirelliDBAdapter(Context c){
    mContext=c;
}


// database open/close actions
public PirelliDBAdapter open() throws android.database.SQLException{
    mDbHelper=new DatabaseHelper(mContext);
    db=mDbHelper.getWritableDatabase();
    return this;
}

public void close(){
    mDbHelper.close();
}

// database CRUD operation
public long createReminder(String title, String body, String dateTime){
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_TITLE, title);
    initialValues.put(KEY_BODY, body);
    initialValues.put(KEY_DATE_TIME, dateTime);

    return db.insert(DATABASE_TABLE, null, initialValues);

}

public boolean deleteReminder(long rowId){
    return db.delete(DATABASE_TABLE, KEY_ROW_ID+" = "+rowId,null) >0;
}

public Cursor fetchAllReminders(){
    return db.query(DATABASE_TABLE,new String [] {KEY_ROW_ID,KEY_TITLE,KEY_BODY,KEY_DATE_TIME}, null, null, null, null, null);
}

public Cursor fetchReminder(long rowId) throws SQLException{
    Cursor c = db.query(true, DATABASE_TABLE, new String []{KEY_ROW_ID,KEY_TITLE,KEY_BODY,KEY_DATE_TIME}, KEY_ROW_ID+" = "+rowId, null,null,null,null,null);

    if(c!=null){
        c.moveToFirst();
    }
    return c;
}

public boolean updateReminder(long rowId, String title, String body, String dateTime){
    ContentValues args = new ContentValues();
    args.put(KEY_TITLE, title);
    args.put(KEY_BODY, body);
    args.put(KEY_DATE_TIME, dateTime);

    return db.update(DATABASE_TABLE, args, KEY_ROW_ID+" = "+rowId, null)>0;
}

private static class DatabaseHelper extends SQLiteOpenHelper{
    public DatabaseHelper(Context c){
        // create the current database
        super(c,DATABASE_NAME,null,DATABASE_VERSION);
    }

    //  overrides

    @Override
    public void onCreate(SQLiteDatabase db) {
        // create the database table
        db.execSQL(DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table " + DATABASE_TABLE);
        db.execSQL(DATABASE_CREATE);

    }
}

Here's my error log when trying to add another field to the database (AFTER creating an entirely new AVD):

    12-28 22:02:11.806: E/SQLiteDatabase(421):  at  android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:149)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at pirelli.app.dbadapter.PirelliDBAdapter.open(PirelliDBAdapter.java:44)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at pirelli.app.AdminSpinnerScreen.onCreate(AdminSpinnerScreen.java:329)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at android.app.Activity.performCreate(Activity.java:4397)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1048)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1779)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1831)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at android.app.ActivityThread.access$500(ActivityThread.java:122)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1024)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at android.os.Handler.dispatchMessage(Handler.java:99)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at android.os.Looper.loop(Looper.java:132)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at android.app.ActivityThread.main(ActivityThread.java:4123)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at java.lang.reflect.Method.invokeNative(Native Method)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at java.lang.reflect.Method.invoke(Method.java:491)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:841)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:599)
    12-28 22:02:11.806: E/SQLiteDatabase(421):  at dalvik.system.NativeStart.main(Native Method)

Answer 1

I had to change the adapter class so whenever I make a change to the database, I use the commented lines below:

public DatabaseHelper(Context c){
        // create the current database
        super(c,DATABASE_NAME,null,DATABASE_VERSION);
        SQLiteDatabase db = this.getWritableDatabase();
        try{//USE THESE WHEN CHANGING DB STRUCTURE
            //db.execSQL("drop table if exists " + DATABASE_TABLE);
            //db.execSQL(DATABASE_CREATE);
        } catch( Exception E ){
            Log.v("ERRRRRRROR", E.getMessage());
        }
    }

Answer 2

Are you perhaps installing over a previous install on the emulator? The database is only created once. After that, if you change a column name, it would not work.

If you make a change to your database structure, you need to either clear the app from the device/emulator to force a fresh install or increment the version number. Of course, if you update the version number, you need to implement onUpgrade to destroy the database and recreate (or do the appropriate migrations).