Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Android Android Data Persistence Using SQLite for Structured Data Adding the Annotations Table

Richard Luick
Richard Luick
10,955 Points
Posted by Richard Luick
Richard Luick
10,955 Points

android.database.sqlite.SQLiteException: near "CREATE_TABLE"

I am working on the SQLite project and my app is crashing after the adding the annotations stage.

My Code

package com.teamtreehouse.mememaker.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;

/**
 * Created by Evan Anger on 8/17/14.
 */
public class MemeSQLiteHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "memes.db";
    private static final int DB_VERSION = 1;

    //Meme Table functionality
    public static final String MEMES_TABLE = "MEMES";
    public static final String COLUMN_MEME_ASSET = "ASSET";
    public static final String COLUMN_MEME_NAME = "NAME";
    private static final String CREATE_MEMES =
            "CREATE_TABLE " + MEMES_TABLE + " (" + BaseColumns._ID +
            " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_MEME_ASSET + " TEXT, " +
            COLUMN_MEME_NAME + " TEXT)";

    //Meme Table Annotations functionality
    //Meme Table Annotations functionality
    public static final String ANNOTATIONS_TABLE = "ANNOTATIONS";
    public static final String COLUMN_ANNOTATION_COLOR = "COLOR";
    public static final String COLUMN_ANNOTATION_X = "X";
    public static final String COLUMN_ANNOTATION_Y = "Y";
    public static final String COLUMN_ANNOTATION_TITLE = "TITLE";
    public static final String COLUMN_FOREIGN_KEY_MEME = "MEME_ID";
    private static final String CREATE_ANNOTATIONS = "CREATE TABLE " + ANNOTATIONS_TABLE + " (" +
            BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_ANNOTATION_X + " INTEGER, " +
            COLUMN_ANNOTATION_Y + " INTEGER, " +
            COLUMN_ANNOTATION_TITLE + " TEXT, " +
            COLUMN_ANNOTATION_COLOR + " TEXT, " +
            COLUMN_FOREIGN_KEY_MEME + " INTEGER, " +
            "FOREIGN KEY(" + COLUMN_FOREIGN_KEY_MEME +
            ")REFERENCES MEMES(ID))";

    public MemeSQLiteHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(CREATE_MEMES);
        sqLiteDatabase.execSQL(CREATE_ANNOTATIONS);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i2) {

    }
}

The logcat

 Process: com.teamtreehouse.mememaker, PID: 1695
    android.database.sqlite.SQLiteException: near "CREATE_TABLE": syntax error (code 1): , while compiling: CREATE_TABLE MEMES (_id INTEGER PRIMARY KEY AUTOINCREMENT, ASSET TEXT, NAME TEXT)
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
            at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1672)
            at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1603)
            at com.teamtreehouse.mememaker.database.MemeSQLiteHelper.onCreate(MemeSQLiteHelper.java:50)
            at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:252)
            at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:188)
            at com.teamtreehouse.mememaker.database.MemeDatasource.<init>(MemeDatasource.java:17)
            at com.teamtreehouse.mememaker.ui.fragments.MemeItemFragment.onResume(MemeItemFragment.java:89)
            at android.app.Fragment.performResume(Fragment.java:1743)
            at android.app.FragmentManagerImpl.moveToState(FragmentManager.java:924)
            at android.app.FragmentManagerImpl.performPendingDeferredStart(FragmentManager.java:785)
            at android.app.FragmentManagerImpl.startPendingDeferredFragments(FragmentManager.java:1086)
            at android.app.FragmentManagerImpl.execPendingActions(FragmentManager.java:1464)
            at android.app.FragmentManagerImpl.executePendingTransactions(FragmentManager.java:479)
            at android.support.v13.app.FragmentPagerAdapter.finishUpdate(FragmentPagerAdapter.java:145)
            at android.support.v4.view.ViewPager.populate(ViewPager.java:1068)
            at android.support.v4.view.ViewPager.populate(ViewPager.java:914)
            at android.support.v4.view.ViewPager.onMeasure(ViewPager.java:1436)
            at android.view.View.measure(View.java:16497)
            at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5125)
            at android.widget.FrameLayout.onMeasure(FrameLayout.java:310)
            at android.view.View.measure(View.java:16497)
            at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5125)
            at com.android.internal.widget.ActionBarOverlayLayout.onMeasure(ActionBarOverlayLayout.java:327)
            at android.view.View.measure(View.java:16497)
            at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5125)
            at android.widget.FrameLayout.onMeasure(FrameLayout.java:310)
            at com.android.internal.policy.impl.PhoneWindow$DecorView.onMeasure(PhoneWindow.java:2291)
            at android.view.View.measure(View.java:16497)
            at android.view.ViewRootImpl.performMeasure(ViewRootImpl.java:1916)
            at android.view.ViewRootImpl.measureHierarchy(ViewRootImpl.java:1113)
            at android.view.ViewRootImpl.performTraversals(ViewRootImpl.java:1295)
            at android.view.ViewRootImpl.doTraversal(ViewRootImpl.java:1000)
            at android.view.ViewRootImpl$TraversalRunnable.run(ViewRootImpl.java:5670)
            at android.view.Choreographer$CallbackRecord.run(Choreographer.java:761)
            at android.view.Choreographer.doCallbacks(Choreographer.java:574)
            at android.view.Choreographer.doFrame(Choreographer.java:544)
            at android.view.Choreographer$FrameDisplayEventReceiver.run(Choreographer.java:747)
            at android.os.Handler.handleCallback(Handler.java:733)
            at android.os.Handler.dispatchMessage(Handler.java:95)
            at android.os.Looper.loop(Looper.java:136)
            at android.app.ActivityThread.main(ActivityThread.java:5017)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:515)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
            at dalvik.system.NativeStart.main(Native Method)

1 Answer

Evan Anger
STAFF
Evan Anger
Treehouse Guest Teacher
Evan Anger
Evan Anger
Treehouse Guest Teacher

Hi Richard,

First of all thanks so much for putting in the logcat that helps a ton.

I think the bug rears its ugly head in the first line of your logcat...

Process: com.teamtreehouse.mememaker, PID: 1695 android.database.sqlite.SQLiteException: near "CREATE_TABLE": syntax error (code 1): , while compiling: CREATE_TABLE MEMES (_id INTEGER PRIMARY KEY AUTOINCREMENT, ASSET TEXT, NAME TEXT

Android understands "CREATE TABLE..." as its valid SQLite, "CREATE_TABLE" is not. It looks like you're referencing that in the code.

Richard Luick
Richard Luick
10,955 Points
Richard Luick
Richard Luick
10,955 Points

Thanks Evan! That fixed it.