Having Trouble Getting SQLite Working in my Android Application

I am having a problem getting my SQLite working properly in this Android application that I'm developing. It appears as though the table isn't properly being created based on the LogCat messages. I feel like I've been staring at the screen too long. Can anyone here spot the problem in my code?

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

public class DBHelper extends SQLiteOpenHelper {

    // table name
    public static final String TABLE_JOKES = "jokes";

    // database field names
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_CATEGORY = "category";
    public static final String COLUMN_SUBCATEGORY = "subcategory";
    public static final String COLUMN_JOKE_TYPE = "jokeType";
    public static final String COLUMN_DESCRIPTION = "description";
    public static final String COLUMN_QUESTION_TEXT = "questionText";
    public static final String COLUMN_ANSWER_TEXT = "answerText";
    public static final String COLUMN_MONOLOGUE_TEXT = "monologueText";
    public static final String COLUMN_RATING_SCALE = "ratingScale";
    public static final String COLUMN_COMMENTS = "comments";
    public static final String COLUMN_JOKE_SOURCE = "jokeSource";
    public static final String COLUMN_RELEASE_STATUS = "releaseStatus";
    public static final String COLUMN_CREATED = "created";
    public static final String COLUMN_MODIFIED = "modified";

    private static final String DATABASE_NAME = "jokes.db"; // file name
    private static final int DATABASE_VERSION = 1;

    // Database creation raw SQL statement
    private static final String DATABASE_CREATE = "create table " + TABLE_JOKES
            + "( " + COLUMN_ID + " integer primary key autoincrement, "
            + COLUMN_CATEGORY + " text not null, " + COLUMN_SUBCATEGORY
            + " text not null, " + COLUMN_JOKE_TYPE + " integer not null, "
            + COLUMN_DESCRIPTION + " text," + COLUMN_QUESTION_TEXT + "text,"
            + COLUMN_ANSWER_TEXT + "text," + COLUMN_MONOLOGUE_TEXT + "text,"
            + COLUMN_RATING_SCALE + "integer," + COLUMN_COMMENTS + "text,"
            + COLUMN_JOKE_SOURCE + "text," + COLUMN_RELEASE_STATUS + "integer,"
            + COLUMN_CREATED + "text," + COLUMN_MODIFIED + "text" + ");";

    // static instance to share DBHelper
    private static DBHelper dbHelper = null;
    private SQLiteDatabase db ;

    /**
     * Constructor
     * 
     * @param context
     * @param name
     * @param factory
     * @param version
     */
    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        db = getWritableDatabase();
    }

    /**
     * This is a static method that makes sure that only one database helper
     * exists across the app's lifecycle
     * 
     * @param context
     * @return
     */
    public static DBHelper getDBHelper(Context context) {
        if (dbHelper == null) {
            dbHelper = new DBHelper(context.getApplicationContext());
        }
        return dbHelper;
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite
     * .SQLiteDatabase)
     */
    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(DATABASE_CREATE);
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite
     * .SQLiteDatabase, int, int)
     */
    @Override
    public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
        Log.w(DBHelper.class.getName(), "Upgrading database from version "
                + oldVersion + " to " + newVersion);
    }

    /**
     * CRUD - Update
     * 
     * @param category
     * @param subcategory
     * @param jokeType
     * @param description
     * @param questionText
     * @param answerText
     * @param monologueText
     * @param comments
     * @param jokeSource
     * @param ratingScale
     * @param releaseStatus
     * @return
     */
    public long insertNewJoke(String category, String subcategory,
            int jokeType, String description, String questionText,
            String answerText, String monologueText, String comments,
            String jokeSource, int ratingScale, int releaseStatus) {
        ContentValues cv = new ContentValues();
        cv.put(COLUMN_CATEGORY, category);
        cv.put(COLUMN_SUBCATEGORY, subcategory);
        cv.put(COLUMN_JOKE_TYPE, jokeType);
        cv.put(COLUMN_DESCRIPTION, description);
        cv.put(COLUMN_QUESTION_TEXT, questionText);
        cv.put(COLUMN_ANSWER_TEXT, answerText);
        cv.put(COLUMN_MONOLOGUE_TEXT, monologueText);
        cv.put(COLUMN_COMMENTS, comments);
        cv.put(COLUMN_JOKE_SOURCE, jokeSource);
        cv.put(COLUMN_RATING_SCALE, ratingScale);
        cv.put(COLUMN_RELEASE_STATUS, releaseStatus);
        cv.put(COLUMN_CREATED, ""); // how to put date??
        cv.put(COLUMN_CREATED, "");

        long code = getWritableDatabase().insert(TABLE_JOKES, null, cv);
        return code;
    }

    /**
     * CRUD - Retrieve
     * 
     * @return
     */
    public Cursor getJokes() {
        String[] columns = { COLUMN_CATEGORY, COLUMN_SUBCATEGORY,
                COLUMN_JOKE_TYPE, COLUMN_DESCRIPTION, COLUMN_ANSWER_TEXT,
                COLUMN_MONOLOGUE_TEXT, COLUMN_RATING_SCALE, COLUMN_COMMENTS,
                COLUMN_JOKE_SOURCE, COLUMN_RELEASE_STATUS, COLUMN_CREATED,
                COLUMN_MODIFIED }; // might need the _id column
        return getWritableDatabase().query(TABLE_JOKES, columns, null, null,
                null, null, null);
    }

    /**
     * CRUD - Delete
     * 
     * @param id
     */
    public void deleteJoke(int id) {
        getWritableDatabase().delete(TABLE_JOKES, COLUMN_ID + "=?",
                new String[] { String.valueOf(id) });
    }

}

So in onCreate() (in my MainActivity) I try to insert a new record into the database and then try to retrieve it into a cursor. However, it seems to be getting caught at the insert statement.

@Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        // the projection (fields from the database that we want to use)
        String from[] = { DBHelper.COLUMN_DESCRIPTION };
        // matching fields on the layout to be used with the adapter
        int to[] = { R.id.tv1 };

        setContentView(R.layout.activity_main);

        lv = (ListView) findViewById(R.id.lvMainJokes);
        dbHelper = DBHelper.getDBHelper(this);

        long code = dbHelper.insertNewJoke("Holiday", "", 2, "Joke 1 Description", "", "",
                "joke", "", "", 5, 1);

        if (code != -1)
        cursor = dbHelper.getJokes();
...

Here is the LogCat output:

11-15 15:19:08.040: I/Choreographer(1016): Skipped 45 frames!  The application may be doing too much work on its main thread.
11-15 15:19:35.358: D/dalvikvm(1064): GC_FOR_ALLOC freed 58K, 4% free 2726K/2836K, paused 42ms, total 45ms
11-15 15:19:35.368: I/dalvikvm-heap(1064): Grow heap (frag case) to 3.315MB for 635812-byte allocation
11-15 15:19:35.438: D/dalvikvm(1064): GC_FOR_ALLOC freed 2K, 4% free 3344K/3460K, paused 63ms, total 63ms
11-15 15:19:35.548: E/SQLiteLog(1064): (1) table jokes has no column named releaseStatus
11-15 15:19:35.568: E/SQLiteDatabase(1064): Error inserting category=Holiday releaseStatus=1 jokeType=2 created= monologueText=joke description=Joke 1 Description subcategory= answerText= questionText= jokeSource= ratingScale=5 comments=
11-15 15:19:35.568: E/SQLiteDatabase(1064): android.database.sqlite.SQLiteException: table jokes has no column named releaseStatus (code 1): , while compiling: INSERT INTO jokes(category,releaseStatus,jokeType,created,monologueText,description,subcategory,answerText,questionText,jokeSource,ratingScale,comments) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at com.teamdotgetname.android.phase1.jokeapplication.persistence.DBHelper.insertNewJoke(DBHelper.java:143)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at com.teamdotgetname.android.phase1.jokeapplication.presentation.MainActivity.onCreate(MainActivity.java:53)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.app.Activity.performCreate(Activity.java:5133)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2175)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2261)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.app.ActivityThread.access$600(ActivityThread.java:141)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1256)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.os.Handler.dispatchMessage(Handler.java:99)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.os.Looper.loop(Looper.java:137)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.app.ActivityThread.main(ActivityThread.java:5103)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at java.lang.reflect.Method.invokeNative(Native Method)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at java.lang.reflect.Method.invoke(Method.java:525)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:737)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at dalvik.system.NativeStart.main(Native Method)
11-15 15:19:35.798: I/Choreographer(1064): Skipped 42 frames!  The application may be doing too much work on its main thread.
11-15 15:19:35.868: D/gralloc_goldfish(1064): Emulator without GPU emulation detected.

Thanks for taking a look.

Answers


At a quick glance, your code looks good. The exception:

    android.database.sqlite.SQLiteException: table jokes has no column named releaseStatus (code 1)

seems erroneous because clearly you are creating the releaseStatus column in your create table DATABASE_CREATE String. I ran into a problem where I was modifying the schema of my database and the changes were not taking effect in my application because I was not incrementing the DATABASE_VERSION.

Your options to try which will all cause the database to get recreated are:

  1. Clear the applications data via the Settings apk
  2. Manually uninstall then reinstall your application. (Just running gradle installDebug or having Eclipse install your app isn't good enough)
  3. Increment your DATABASE_VERSION value

I would say that you need some space in Create statement. Starting: COLUMN_QUESTION_TEXT + "text," into COLUMN_QUESTION_TEXT + " text,".


It looks just like you don't have spaces between the column names and the column types. Try this.

    private static final String DATABASE_CREATE = "create table " + TABLE_JOKES
        + "( " + COLUMN_ID + " integer primary key autoincrement, "
        + COLUMN_CATEGORY + " text not null, " + COLUMN_SUBCATEGORY
        + " text not null, " + COLUMN_JOKE_TYPE + " integer not null, "
        + COLUMN_DESCRIPTION + " text," + COLUMN_QUESTION_TEXT + " text,"
        + COLUMN_ANSWER_TEXT + " text," + COLUMN_MONOLOGUE_TEXT + " text,"
        + COLUMN_RATING_SCALE + " integer," + COLUMN_COMMENTS + " text,"
        + COLUMN_JOKE_SOURCE + " text," + COLUMN_RELEASE_STATUS + " integer,"
        + COLUMN_CREATED + " text," + COLUMN_MODIFIED + " text" + ");";

Need Your Help

jQuery : find tags within the string which contain text

jquery regex search filter

I have a variable, which stores table rows with some content. I would like to search that string for a specific term using the %%LIKE%% approach and if found add this html row to a new variable