CODEWITHSUNDEEP
androiddatabaseandroid-logcat
Darren Murtagh
Darren Murtagh

Reputation: 591

Android SQLite Database Setup error?

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?

Upvotes: 1

Views: 297

Answers (2)

Sunny
Sunny

Reputation: 14828

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.

Upvotes: 1

Kuffs
Kuffs

Reputation: 35661

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.

Upvotes: 2

Related Questions