lundi 29 juin 2015

SQLite: no such column; unclear error

When I query my database I keep getting an error saying SQLException: no such column: item and i cannot see where the mistake is.

To start with, here are the elements to create the database.

public class DataBaseHelper {
private Context mCtx = null;
private DataBaseHelperInternal mDbHelper = null;
private SQLiteDatabase mDb = null;
private static final String DATABASE_NAME = "SHOPLIST";
private static final int DATABASE_VERSION = 3;
private static final String DATABASE_TABLE_SHOPLIST = "shoplist";
public static final String SL_ID = "_id";
public static final String SL_ITEM = "item";
public static final String SL_PLACE = "place";
public static final String SL_IMPORTANCE = "importance";
public static final String SL_PRICE = "price";
private static final String DATABASE_CREATE_SHOPLIST = "create table " + DATABASE_TABLE_SHOPLIST
        + " ( " + SL_ID + " integer primary key, " + SL_ITEM + " text not null, " + SL_PLACE +
        " text not null, " + SL_IMPORTANCE + " integer not null, "+ SL_PRICE + " float not null)";

I am using a private internal class called DataBaseHelperInternal to access to the database.

private static class DataBaseHelperInternal extends SQLiteOpenHelper {
    public DataBaseHelperInternal(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    public void onCreate(SQLiteDatabase db){
        createTables(db);
    }
    public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion){
        deleteTables(db);
        createTables(db);
    }
    private void createTables(SQLiteDatabase db){
        db.execSQL(DATABASE_CREATE_SHOPLIST);;
    }
    private void deleteTables(SQLiteDatabase db){
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_SHOPLIST);
    }
}

And the mistake is found here, when this function is called by a different class:

public Cursor getItems(){
    return mDb.query(DATABASE_TABLE_SHOPLIST, new String[] {SL_ID, SL_ITEM, SL_PLACE,
            SL_IMPORTANCE}, null, null, null, null, SL_IMPORTANCE);
}

And here is the full error:

1027-1027/com.acme.listadecompra E/AndroidRuntime﹕ FATAL EXCEPTION: main
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.acme.listadecompra/com.acme.listadecompra.ItemList}: android.database.sqlite.SQLiteException: no such column: item (code 1): , while compiling: SELECT _id, item, place, importance FROM shoplist ORDER BY importance
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2059)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
        at android.app.ActivityThread.access$600(ActivityThread.java:130)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loop(Looper.java:137)
        at android.app.ActivityThread.main(ActivityThread.java:4745)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:511)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
        at dalvik.system.NativeStart.main(Native Method)
Caused by: android.database.sqlite.SQLiteException: no such column: item (code 1): , while compiling: SELECT _id, item, place, importance FROM shoplist ORDER BY importance
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
        at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
        at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1161)
        at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1032)
        at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1200)
        at com.acme.listadecompra.DataBaseHelper.getItems(DataBaseHelper.java:66)
        at com.acme.listadecompra.ItemList.fillData(ItemList.java:43)
        at com.acme.listadecompra.ItemList.onCreate(ItemList.java:37)
        at android.app.Activity.performCreate(Activity.java:5008)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2023)

Greetings from Spain.

Aucun commentaire:

Enregistrer un commentaire