SQLite Error while compiling: CREATE TABLE IF NOT EXISTS entry (TEXT PRIMARY KEY, Test TEXT, )

Question

This is my first attempt at using SQLite and I am receiving a conflict that is common, however, I have not been able to solve it on my own. Below is the cause of the issue.

03-10 17:43:21.747: E/AndroidRuntime(11225): Caused by: android.database.sqlite.SQLiteException: near ")": syntax error (code 1): , while compiling: CREATE TABLE IF NOT EXISTS entry ( TEXT PRIMARY KEY, Test TEXT, )

I understand that this method is somehow constructed incorrectly but I need help figuring out how.

private void createEntryTable(SQLiteDatabase db) {
    StringBuilder entryTableFields = new StringBuilder();
    entryTableFields.append(EntryTable.Cols.COLUMN_ENTRY)
    .append(" TEXT PRIMARY KEY, ")
    .append(EntryTable.Cols.COLUMN_TEST).append(" TEXT, ");
    createTable(db, EntryTable.TABLE_NAME, entryTableFields.toString());
}

Here is how I have set up my SQLite. First, my table that only has two columns.

public class EntryTable {

    public static final String TABLE_NAME = "entry";
    public static final String PATH = "entry";
    public static final int PATH_TOKEN = 2015;
    public static final Uri CONTENT_URI = ContentDescriptor.BASE_URI.buildUpon().appendPath(PATH).build();

    public static class Cols {
        // Table column names
        public static final String COLUMN_ENTRY = "Entry";
        public static final String COLUMN_TEST = "Test";
    }
}

I created a ContentDescriptor class.

public class ContentDescriptor {
    public static final String AUTHORITY = "com.package";
    public static final Uri BASE_URI = Uri.parse("content://" + AUTHORITY);
    public static final UriMatcher URI_MATCHER = buildUriMatcher();

    private static UriMatcher buildUriMatcher() {
        final UriMatcher matcher = new UriMatcher(UriMatcher.NO_MATCH);

        // add as many tables as you want below
        matcher.addURI(AUTHORITY, EntryTable.PATH, EntryTable.PATH_TOKEN);
        return matcher;
    }
}

I have a database manager class

public class DatabaseManager {

    public static void saveEntry(Context context, String entry) {

        try {
            ContentValues values = getContentValuesEntryTable(entry);
            ContentResolver resolver = context.getContentResolver();
            Cursor cursor = resolver.query(EntryTable.CONTENT_URI, null, null, null, null);
            if (cursor != null && cursor.getCount() > 0) {
                resolver.update(EntryTable.CONTENT_URI, values, null, null);
            } else {
                resolver.insert(EntryTable.CONTENT_URI, values);
            }
            cursor.close();
            resolver.insert(EntryTable.CONTENT_URI, values);
        } catch (Exception e) {
            Log.e("TEST", "error: " + e.toString());
            e.printStackTrace();
        }

    }

    public static Cursor getEntry(Context context, String entry) {
        Cursor cursor;
        String sorting = null;
        if (TextUtils.isEmpty(entry)) {
            cursor = context.getContentResolver().query(EntryTable.CONTENT_URI, null, null, null, sorting);
        } else {
            cursor = context.getContentResolver().query(EntryTable.CONTENT_URI, null, EntryTable.Cols.COLUMN_ENTRY + " = '" + entry + "'", null, sorting);
        }

        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }

    private static ContentValues getContentValuesEntryTable(String entry) {
        ContentValues values = new ContentValues();
        values.put(EntryTable.Cols.COLUMN_ENTRY, entry);
        return values;
    }
}

Lastly I have a DBHelper class

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String KEY_CREATE_TABLE = "CREATE TABLE IF NOT EXISTS {0} ({1})";
    public static final String KEY_DROP_TABLE = "DROP TABLE IF EXISTS {0}";

    private static final int CURRENT_DB_VERSION = 1;
    private static final String DB_NAME = "qmun.db";

    public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, CURRENT_DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.d("TEST", "DB Creation :: Going to create db ");
        createEntryTable(db);
    }

    private void createEntryTable(SQLiteDatabase db) {
        StringBuilder entryTableFields = new StringBuilder();
        entryTableFields.append(EntryTable.Cols.COLUMN_ENTRY)
        .append(" TEXT PRIMARY KEY, ")
        .append(EntryTable.Cols.COLUMN_TEST).append(" TEXT, ");
        createTable(db, EntryTable.TABLE_NAME, entryTableFields.toString());
    }

    public void dropTable(SQLiteDatabase db, String name) {
        String query = MessageFormat.format(DatabaseHelper.KEY_DROP_TABLE, name);
        db.execSQL(query);
    }

    public void createTable(SQLiteDatabase db, String name, String fields) {
        String query = MessageFormat.format(DatabaseHelper.KEY_CREATE_TABLE, name, fields);
        db.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        dropTable(db, EntryTable.TABLE_NAME);
        onCreate(db);
    }
}

From my MainActivity I am trying to insert data with

DatabaseManager.saveEntry(MainActivity.this, "text goes into my db");

Thanks in advance for you help. I am slowly understanding SQLite.

Juergen · Accepted Answer · 2015-03-11 01:22:48Z

2

The answer is in your headline.

The problem is the little "," at the end of your CREATE command:

CREATE TABLE IF NOT EXISTS entry (TEXT PRIMARY KEY, Test TEXT**,** )

SQLite explicitly does not like a comma before a closing parenthesis.

I guess, you can fix this, when you finish your command - e.g. in createTable strip of any trailing commas from fields.

Of course it would be simpler to remove it here

    .append(EntryTable.Cols.COLUMN_TEST).append(" TEXT, ");

Only, when you want to use createTable for other tables too, it could be worthwhile to think about a generic solution, where the caller does not need to think about commas ...

edited Mar 11, 2015 at 1:22

answered Mar 11, 2015 at 1:15

Juergen

12.8k7 gold badges43 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

portfoliobuilder Over a year ago

Absolutely correct! That took a keen eye to see that. Thank you very much.

math-eww · Accepted Answer · 2015-03-11 01:16:37Z

2

( TEXT PRIMARY KEY, Test TEXT, )

Looks like you've got an extra comma here?

(Pulled from your log below)

Caused by: android.database.sqlite.SQLiteException: near ")": syntax error (code 1): , while compiling: CREATE TABLE IF NOT EXISTS entry ( TEXT PRIMARY KEY, Test TEXT, )

answered Mar 11, 2015 at 1:16

math-eww

2904 silver badges14 bronze badges

1 Comment

portfoliobuilder Over a year ago

I +1 your post. You and the accepted answer were right. Thank you!

Collectives™ on Stack Overflow

SQLite Error while compiling: CREATE TABLE IF NOT EXISTS entry (TEXT PRIMARY KEY, Test TEXT, )

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related