"No such column" when making SQLite query

Question

I cant seem to find the mistake in my code. I tried deleting the database and changing the version number because I added the bottom_time later, but it seems it isnt recognizing it.. The error is indicated when trying to do the db.query inside the getDiveList()-Method.

public class DatabaseHelper extends SQLiteOpenHelper {

    //---DB Details-------------------------------------
    public static final String DATABASE_NAME = "SpotTheFish.db";
    public static final int DATABASE_VERSION = 3;


    //---User Dives------------------------------
    public static final String DIVE_TABLE_NAME = "all_dives";
    public static final String DIVE_ID = "dive_id";
    public static final String KEY_LOCATION_NAME = "dive_location";
    public static final String KEY_DATE = "date";
    public static final String KEY_TIME_IN = "time_in";
    public static final String KEY_TIME_OUT = "time_out";
    public static final String KEY_BOTTOM_TIME = "bottom_time";
    public static final String KEY_BARS_START = "bars_at_start";
    public static final String KEY_BARS_END = "bars_at_end";
    public static final String KEY_DEPTH = "depth";
    public static final String KEY_TEMPERATURE = "temperature";
    public static final String KEY_VISIBILITY = "visibility";
    public static final String[] DIVE_TABLE_KEYS = {DIVE_ID, LINK_STATES_REGIONS_ID, KEY_LOCATION_NAME, KEY_DATE, KEY_TIME_IN, KEY_TIME_OUT, KEY_BOTTOM_TIME, KEY_BARS_START, KEY_BARS_END, KEY_DEPTH, KEY_TEMPERATURE, KEY_VISIBILITY};

    public static final int COL_DIVE_ID = 0;
    public static final int COL_DIVE_LINK_STATES_REGIONS = 1;
    public static final int COL_LOCATION_NAME = 2;
    public static final int COL_DATE = 3;
    public static final int COL_TIME_IN = 4;
    public static final int COL_TIME_OUT = 5;
    public static final int COL_BOTTOM_TIME = 6;
    public static final int COL_BARS_START = 7;
    public static final int COL_BARS_END = 8;
    public static final int COL_DEPTH = 9;
    public static final int COL_TEMPERATURE = 10;
    public static final int COL_VISIBILITY = 11;

    private static final String CREATE_DIVE_TABLE =
            "CREATE TABLE " + DIVE_TABLE_NAME + " (" +
                    DIVE_ID + " INTEGER PRIMARY KEY, " +
                    LINK_STATES_REGIONS_ID + " INTEGER, " +
                    KEY_LOCATION_NAME + " TEXT, " +
                    KEY_DATE + " TEXT, " +
                    KEY_TIME_IN + " TEXT, " +
                    KEY_TIME_OUT + " TEXT" +
                    KEY_BOTTOM_TIME + " TEXT, " +
                    KEY_BARS_START + " TEXT, " +
                    KEY_BARS_END + " TEXT" +
                    KEY_DEPTH + " TEXT, " +
                    KEY_TEMPERATURE + " TEXT, " +
                    KEY_VISIBILITY + " TEXT" +
                    ")";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try {                
            db.execSQL(CREATE_DIVE_TABLE);                 
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        db.execSQL("DROP TABLE IF EXISTS " + DIVE_TABLE_NAME);
    }

    /**
     * Creates an ArrayList and adds all rows from
     * existing database table
     *
     * @return: ArrayList of subjects
     */
    public ArrayList<Dive> getDiveList() {

        ArrayList<Dive> diveList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(DIVE_TABLE_NAME, DIVE_TABLE_KEYS,
                null, null, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
            while (!cursor.isAfterLast()) {
                Dive dive = new Dive(cursor.getLong(DatabaseHelper.COL_DIVE_ID),
                        cursor.getLong(COL_DIVE_LINK_STATES_REGIONS),
                        cursor.getString(COL_LOCATION_NAME),
                        cursor.getString(COL_DATE),
                        cursor.getString(COL_TIME_IN),
                        cursor.getString(COL_TIME_OUT),
                        cursor.getString(COL_BOTTOM_TIME),
                        cursor.getString(COL_BARS_START),
                        cursor.getString(COL_BARS_END),
                        cursor.getString(COL_DEPTH),
                        cursor.getString(COL_TEMPERATURE),
                        cursor.getString(COL_VISIBILITY));
                diveList.add(dive);
                cursor.moveToNext();
            }
        }

        cursor.close();
        db.close();
        return diveList;
    }
}

Here is the error log:

 --------- beginning of crash
05-18 13:43:19.820 7044-7044/com.id12538676.catchemallfishcollection E/AndroidRuntime: FATAL EXCEPTION: main

                                                                                       Process: com.id12538676.catchemallfishcollection, PID: 7044
                                                                                       java.lang.RuntimeException: Unable to start activity ComponentInfo{com.id12538676.catchemallfishcollection/com.id12538676.catchemallfishcollection.StartActivity}: android.database.sqlite.SQLiteException: no such column: bottom_time (code 1): , while compiling: SELECT dive_id, state_region_link_id, dive_location, date, time_in, time_out, bottom_time, bars_at_start, bars_at_end, depth, temperature, visibility FROM all_dives
                                                                                           at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2416)
                                                                                           at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476)
                                                                                           at android.app.ActivityThread.-wrap11(ActivityThread.java)
                                                                                           at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344)
                                                                                           at android.os.Handler.dispatchMessage(Handler.java:102)
                                                                                           at android.os.Looper.loop(Looper.java:148)
                                                                                           at android.app.ActivityThread.main(ActivityThread.java:5417)
                                                                                           at java.lang.reflect.Method.invoke(Native Method)
                                                                                           at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726)
                                                                                           at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)
                                                                                        Caused by: android.database.sqlite.SQLiteException: no such column: bottom_time (code 1): , while compiling: SELECT dive_id, state_region_link_id, dive_location, date, time_in, time_out, bottom_time, bars_at_start, bars_at_end, depth, temperature, visibility FROM all_dives
                                                                                           at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                                                                                           at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
                                                                                           at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
                                                                                           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:1316)
                                                                                           at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1163)
                                                                                           at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1034)
                                                                                           at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1240)
                                                                                           at com.id12538676.catchemallfishcollection.DatabaseHelper.getDiveList(DatabaseHelper.java:364)
                                                                                           at com.id12538676.catchemallfishcollection.DatabaseManager.<init>(DatabaseManager.java:26)
                                                                                           at com.id12538676.catchemallfishcollection.DatabaseManager.getInstance(DatabaseManager.java:19)
                                                                                           at com.id12538676.catchemallfishcollection.StartActivity.onCreate(StartActivity.java:29)
                                                                                           at android.app.Activity.performCreate(Activity.java:6237)
                                                                                           at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1107)
                                                                                           at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2369)
                                                                                           at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476) 
                                                                                           at android.app.ActivityThread.-wrap11(ActivityThread.java) 
                                                                                           at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344) 
                                                                                           at android.os.Handler.dispatchMessage(Handler.java:102) 
                                                                                           at android.os.Looper.loop(Looper.java:148) 
                                                                                           at android.app.ActivityThread.main(ActivityThread.java:5417) 
                                                                                           at java.lang.reflect.Method.invoke(Native Method) 
                                                                                           at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726) 
                                                                                           at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)

Check your CREATE_TABLE statement. Missed a comma. – 323go May 18 '16 at 14:10 — 323go, May 18 '16 at 14:10

laalto · Accepted Answer · 2016-05-18T14:17:35.077

2

KEY_TIME_OUT + " TEXT" +
KEY_BOTTOM_TIME + " TEXT, " +

You missed a , comma on the previous column declaration. You have the same problem on some other columns, too.

After adding it there, you can uninstall your app to get the database recreated.

edited May 18 '16 at 14:17

answered May 18 '16 at 14:09

laalto

150,114
66
286
303

1

also at KEY_BARS_END + " TEXT" + KEY_DEPTH + " TEXT, " – user1930106 May 18 '16 at 14:15
I just cant beliece I didnt see that.. I was looking at my code for over an hour. Thanks!! – Thesie May 18 '16 at 23:47

pouyan · Answer 2 · 2016-05-18T14:24:29.377

you should call create function in your upgrade:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    db.execSQL("DROP TABLE IF EXISTS " + DIVE_TABLE_NAME);
    onCreate(db);
}

and also you should replace your creation query as follow:

private static final String CREATE_DIVE_TABLE =
        "CREATE TABLE " + DIVE_TABLE_NAME + " (" +
                DIVE_ID + " INTEGER PRIMARY KEY, " +
                LINK_STATES_REGIONS_ID + " INTEGER, " +
                KEY_LOCATION_NAME + " TEXT, " +
                KEY_DATE + " TEXT, " +
                KEY_TIME_IN + " TEXT, " +
                KEY_TIME_OUT + " TEXT, " +
                KEY_BOTTOM_TIME + " TEXT, " +
                KEY_BARS_START + " TEXT, " +
                KEY_BARS_END + " TEXT, " +
                KEY_DEPTH + " TEXT, " +
                KEY_TEMPERATURE + " TEXT, " +
                KEY_VISIBILITY + " TEXT" +
                ")";

score 0 · Answer 3 · edited May 18 '16 at 19:45

0

Please check table creation query.. You missed one comma, after key time out text

KEY_TIME_IN + " TEXT, " + KEY_TIME_OUT + " TEXT" + KEY_BOTTOM_TIME + " TEXT, "

edited May 18 '16 at 19:45

Laur Ivan

4,117
3
38
62

answered May 18 '16 at 14:13

Sush

3,864
2
17
35

"No such column" when making SQLite query

3 Answers3