SQLiteException: no such column when querying database

Question

I'm getting the following exception when attempting to query the sqlite database table for all rows:

08-24 20:36:48.134: E/AndroidRuntime(351): java.lang.RuntimeException: 
Unable to start activity ComponentInfo{se.p950tes.workouttracker/se.p950tes.workouttracker.SavedActivity}:
android.database.sqlite.SQLiteException: no such column: col_distance: , 
while compiling: SELECT _id, col_date, col_time, col_distance, col_route FROM workout

I haven't ever updated the database, this is the first time I've installed the app on this device, and it's also tested on the emulator with the same result. Even so I've tried to up the version number a few times, with no change.

Here's my code. The exception is thrown in the getAll method.

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler
{
    public static final String DATABASE_NAME = "workout_tracker";

    private static final String KEY_ID       = "_id";
    private static final String KEY_DATE     = "col_date";
    private static final String KEY_TIME     = "col_time";
    private static final String KEY_DISTANCE = "col_distance";
    private static final String KEY_ROUTE    = "col_route";

    private static final String TABLE = "workout";
    private static final String[] ALL_FIELDS = {KEY_ID, KEY_DATE, KEY_TIME, KEY_DISTANCE, KEY_ROUTE};

    private static final int DATABASE_VERSION = 3;

    private final Context context;
    private DatabaseHelper helper;
    private SQLiteDatabase db;

    public DatabaseHandler(Context context)
    {
        this.context = context;
        this.helper = new DatabaseHelper(context);
    }

    public void connect()
    {
        db = helper.getWritableDatabase();
    }
    public void disconnect()
    {
        db.close();
    }

    public Cursor getAll()
    {
        return db.query(TABLE, ALL_FIELDS, null, null, null, null, null);
    }

    private static class DatabaseHelper extends SQLiteOpenHelper
    {
        private static final String CREATE = "CREATE TABLE " + TABLE + "("
                                                + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                                                + KEY_DATE + " INTEGER NOT NULL, "
                                                + KEY_TIME + " INTEGER NOT NULL, "
                                                + KEY_DISTANCE + "INTEGER NOT NULL, "
                                                + KEY_ROUTE + " BLOB NOT NULL"
                                                + ");";
        DatabaseHelper(Context context)
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
        @Override
        public void onCreate(SQLiteDatabase db)
        {
            try
            {
                db.execSQL(CREATE);
            }
            catch(SQLException e)
            {
                e.printStackTrace();
            }
        }
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            Log.w("DBHandler", "Upgrading database from version "+oldVersion+" to "+newVersion+", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS "+TABLE);
            onCreate(db);
        }
    }
}

I realise this "Why do I get this SQLiteException" question is asked a lot, but so far most issues I've seen people have have been not enclosing strings in quotation marks and whatnot. I apologise in advance if it turns out to be a duplicate.

Answer 1

You are missing a leading space in "INTEGER NOT NULL, ".