Android SQLite Database Setup error?

Question

i have set up an SQLite database. it seems that when i run a class that need to access it the application crashes. initially it was pointed out to me that the table create statements were the problem but after that error had been resolved it still did not work. the database setup is:-

public class DataBaseHelper
{
    public static final String KEY_ROWID = "_id";
    public static final String KEY_CODE = "code";
    public static final String KEY_DAYS = "days";
    public static final String KEY_BMI = "bmi"; 
    public static final String KEY_ROWID2 = "id2";
    public static final String KEY_DATE = "date";
    public static final String KEY_STEPS = "steps";
    public static final String KEY_CALs = "calories";
    private static final String TAG = "DBAdapter";

    private static final String DATABASE_NAME = "workout";
    private static final String DATABASE_TABLE = "goals";
    private static final String DATATABLE = "acts";

    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE =
        "create table goals (_id integer primary key autoincrement, "
        + "code text not null, days text not null, " 
        + "bmi text not null);";
    private static final String DATABASE_CREATE2 =
        "create table acts (id2 integer primary key autoincrement, "
                + "date text not null, steps text not null, " 
                + "calories text not null);";

    private final Context context; 

    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    public DataBaseHelper(Context ctx) 
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

    private static class DatabaseHelper extends SQLiteOpenHelper 
    {
        DatabaseHelper(Context context) 
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
        @Override
        public void onCreate(SQLiteDatabase db) 
        {
            db.execSQL(DATABASE_CREATE);
            db.execSQL(DATABASE_CREATE2);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, 
        int newVersion) 
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion 
                + " to "
                + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS titles");
            onCreate(db);
        }
    }    

//---opens the database---
    public DataBaseHelper open() throws SQLException 
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }

//---closes the database---    
    public void close() 
    {
        DBHelper.close();
    }

//---insert a title into the database---
    public long insertTitle(String isbn, String title, String publisher) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_CODE, isbn);
        initialValues.put(KEY_DAYS, title);
        initialValues.put(KEY_BMI, publisher);
        return db.insert(DATABASE_TABLE, null, initialValues);
    }
    public long insertActivity(String date, String steps, String Calories) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_DATE, date);
        initialValues.put(KEY_STEPS, steps);
        initialValues.put(KEY_CALs, Calories);
        return db.insert(DATATABLE, null, initialValues);
    }

//---deletes a particular title---
    public boolean deleteTitle(long rowId) 
    {
        return db.delete(DATABASE_TABLE, KEY_ROWID + 
            "=" + rowId, null) > 0;
    }
    public void deleteFirst()
    {
        Cursor cursor = db.query(DATABASE_TABLE, null, null, null, null, null, null); 

        if(cursor.moveToFirst()) {
            long rowId = cursor.getLong(cursor.getColumnIndex(KEY_ROWID)); 

            db.delete(DATABASE_TABLE, KEY_ROWID +  "=" + rowId, null);
        }
     }

    public boolean deleteAct(long rowId) 
    {
        return db.delete(DATATABLE, KEY_ROWID + 
            "=" + rowId, null) > 0;
    }
//---retrieves all the titles---
    public Cursor getAllTitles() 
    {
        return db.query(DATABASE_TABLE, new String[] {
            KEY_ROWID, 
            KEY_CODE,
            KEY_DAYS,
            KEY_BMI}, 
            null, 
            null, 
            null, 
            null, 
            null);
}
public Cursor getAllActs() 
{
    return db.query(DATATABLE, new String[] {
            KEY_ROWID2, 
            KEY_DATE,
            KEY_STEPS,
            KEY_CALs}, 
            null, 
            null, 
            null, 
            null, 
            null);
}

//---retrieves a particular title---
public Cursor getTitle(long rowId) throws SQLException 
{
    Cursor mCursor =
            db.query(true, DATABASE_TABLE, new String[] {
                    KEY_ROWID,
                    KEY_CODE, 
                    KEY_DAYS,
                    KEY_BMI
                    }, 
                    KEY_ROWID + "=" + rowId, 
                    null,
                    null, 
                    null, 
                    null, 
                    null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}
public Cursor getAct(long rowId) throws SQLException 
{
    Cursor mCursor =
            db.query(true, DATATABLE, new String[] {
                    KEY_ROWID2,
                    KEY_DATE, 
                    KEY_STEPS,
                    KEY_CALs
                    }, 
                    KEY_ROWID2 + "=" + rowId, 
                    null,
                    null, 
                    null, 
                    null, 
                    null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}
public boolean updateAct(long rowId, String date, 
        String steps, String cals) 
        {
            ContentValues args = new ContentValues();
            args.put(KEY_DATE, date);
            args.put(KEY_STEPS, steps);
            args.put(KEY_CALs, cals);
            return db.update(DATATABLE, args, 
                             KEY_ROWID2 + "=" + rowId, null) > 0;
        }

//---updates a title---
   public boolean updateTitle(long rowId, String isbn, 
   String title, String publisher) 
   {
       ContentValues args = new ContentValues();
       args.put(KEY_CODE, isbn);
       args.put(KEY_DAYS, title);
       args.put(KEY_BMI, publisher);
       return db.update(DATABASE_TABLE, args, 
                     KEY_ROWID + "=" + rowId, null) > 0;
   } 
}

and the Log cat is:

04-06 17:20:14.561: E/AndroidRuntime(24550): FATAL EXCEPTION: main
04-06 17:20:14.561: E/AndroidRuntime(24550): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.b00348312.workout/com.b00348312.workout.WorkoutProgress}: android.database.sqlite.SQLiteException: no such column: bmi: , while compiling: SELECT _id, code, days, bmi FROM goals
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2787)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2803)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.app.ActivityThread.access$2300(ActivityThread.java:135)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2136)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.os.Handler.dispatchMessage(Handler.java:99)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.os.Looper.loop(Looper.java:144)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.app.ActivityThread.main(ActivityThread.java:4937)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at java.lang.reflect.Method.invokeNative(Native Method)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at java.lang.reflect.Method.invoke(Method.java:521)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:858)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at dalvik.system.NativeStart.main(Native Method)
04-06 17:20:14.561: E/AndroidRuntime(24550): Caused by: android.database.sqlite.SQLiteException: no such column: bmi: , while compiling: SELECT _id, code, days, bmi FROM goals
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:80)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:46)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:53)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1412)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1296)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1251)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1331)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at com.b00348312.workout.DataBaseHelper.getAllTitles(DataBaseHelper.java:131)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at com.b00348312.workout.WorkoutProgress.fillData(WorkoutProgress.java:27)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at com.b00348312.workout.WorkoutProgress.onCreate(WorkoutProgress.java:22)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at   android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1069)
04-06 17:20:14.561: E/AndroidRuntime(24550):    at    android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2751)
04-06 17:20:14.561: E/AndroidRuntime(24550):    ... 11 more

the statment that i thing is telling me the problem is:

 04-06 17:20:14.561: E/AndroidRuntime(24550): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.b00348312.workout/com.b00348312.workout.WorkoutProgress}: android.database.sqlite.SQLiteException: no such column: bmi: , while compiling: SELECT _id, code, days, bmi FROM goals

but i dont no where it refers to as every time the field bmi is called there seems to be no problems. is there something i am missing?

yeah its pointing that out but i have defined dmi in the statement 'public static final String KEY_BMI = "bmi"; ' and it is used in the create statement 'private static final String DATABASE_CREATE = "create table goals (_id integer primary key autoincrement, " + "code text not null, days text not null, " + "bmi text not null);"; ' — Darren Murtagh
– Darren Murtagh, Commented Apr 6, 2012 at 17:29

Kuffs · Accepted Answer · 2012-04-06 17:30:01Z

2

You are trying to access a column named "bmi" in your database but it doesn't exist.

You most likely added the column after the database was created and have not amended your openHelper to correctly upgrade your database.

Add the "bmi" field and the error will be resolved. Changing the Create statement is not enough as the database already exists so the Create statement will not be run. You should override "onUpgrade" to make changes from one database version to another.

Alternatively delete your current database and re-create it from scratch.

answered Apr 6, 2012 at 17:30

Kuffs

35.6k10 gold badges82 silver badges94 bronze badges

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

8 Comments

Darren Murtagh Over a year ago

but the column bmi is there it is set up in this create statement 'private static final String DATABASE_CREATE = "create table goals (_id integer primary key autoincrement, " + "code text not null, days text not null, " + "bmi text not null);";'

Kuffs Over a year ago

answer updated. see above. "Create" statement is only run when the database is created for the very first time.

Darren Murtagh Over a year ago

ok by doing that it is now telling me that the column 'date' does not exist now. but both bmi and date have been in the database from the very start

Kuffs Over a year ago

If it were really there you would not be getting the error telling you it isn't. Computers for the most part don't tend to lie :-) Have a look at sqlitebrowser.sourceforge.net. It is very handy for looking at your databases.

Darren Murtagh Over a year ago

but the column 'date' was in the statment i gave you and after i restarted the application it gave an error saying it cannot find it

|

Sunny · Accepted Answer · 2012-04-06 17:33:40Z

1

You have to unistall and then reinstall your application because android does not change the database if you do not implement onUpgrade.

Or go to settings and your app and clear the data of your app and then run your app.

answered Apr 6, 2012 at 17:33

Sunny

14.9k17 gold badges89 silver badges132 bronze badges

Collectives™ on Stack Overflow

Android SQLite Database Setup error?

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related