android.database.sqlite.SQLiteException: near "1": syntax error (code 1) when creating a new table to store data

Question

package com.example.android.minerals;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.example.android.minerals.QuizContract.*;

import java.util.ArrayList;
import java.util.List;

public class QuizDbHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "minerals.db";
    private static final int DATABASE_VERSION = 2;
    //private static final SQLiteDatabase.CursorFactory factory = null;

    private SQLiteDatabase db;
    public QuizDbHelper(Context context) {
        super( context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        this.db = db;

        final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
                QuestionsTable.TABLE_NAME + " ( "+
                QuestionsTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT " +
                QuestionsTable.COLUMN_QUESTION + " TEXT, " +
                QuestionsTable.COLUMN_OPTION1 + " TEXT, " +
                QuestionsTable.COLUMN_OPTION2 + " TEXT, " +
                QuestionsTable.COLUMN_OPTION3 + " TEXT, " +
                ")";


        db.execSQL( SQL_CREATE_QUESTIONS_TABLE );
        fillQuestionsTable();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL( "DROP TABLE IF EXISTS " + QuestionsTable.TABLE_NAME);
        onCreate(db);

    }

}

I got a error.

Caused by: android.database.sqlite.SQLiteException: near "1": syntax error (code 1): , while compiling: CREATE TABLE quiz_questions ( _id INTEGER PRIMARY KEY AUTOINCREMENT, question TEXT, option 1 TEXT, option 2 TEXT, option 3 TEXT, )

I get a syntax error (code 1) when I try to create the questions table.

Remove the last comma before the closing parenthesis

Klaus Gütter
– Klaus Gütter

2018-12-26 07:30:58 +00:00
Commented Dec 26, 2018 at 7:30 — Klaus Gütter
– Klaus Gütter, Commented Dec 26, 2018 at 7:30

IntelliJ Amiya · Accepted Answer · 2018-12-26 07:47:25Z

3

Caused by: android.database.sqlite.SQLiteException: near "1": syntax error (code 1): , while compiling:

A SQLite exception that indicates there was an error with SQL parsing or execution.

You should remove last comma from your statement , " + ")";

Try with

private static final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE IF NOT EXISTS "
        + QuestionsTable.TABLE_NAME + "(" + QuestionsTable._ID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + QuestionsTable.COLUMN_QUESTION + " TEXT NOT NULL, "
        + QuestionsTable.COLUMN_OPTION1 + " TEXT NOT NULL, "
        + QuestionsTable.COLUMN_OPTION2 + " TEXT NOT NULL, "
        + QuestionsTable.COLUMN_OPTION3 + " TEXT NOT NULL )";

Then Clean-Rebuild-Run

edited Dec 26, 2018 at 7:47

answered Dec 26, 2018 at 7:20

IntelliJ Amiya

75.8k15 gold badges169 silver badges199 bronze badges

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

Comments

dev · Accepted Answer · 2018-12-26 07:19:23Z

final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
                QuestionsTable.TABLE_NAME + " ( "+
                QuestionsTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT " +
                QuestionsTable.COLUMN_QUESTION + " TEXT, " +
                QuestionsTable.COLUMN_OPTION1 + " TEXT, " +
                QuestionsTable.COLUMN_OPTION2 + " TEXT, " +
                QuestionsTable.COLUMN_OPTION3 + " TEXT, " +
                ")";

change this to:

final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
                QuestionsTable.TABLE_NAME + " ( "+
                QuestionsTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT " +
                QuestionsTable.COLUMN_QUESTION + " TEXT, " +
                QuestionsTable.COLUMN_OPTION1 + " TEXT, " +
                QuestionsTable.COLUMN_OPTION2 + " TEXT, " +
                QuestionsTable.COLUMN_OPTION3 + " TEXT " +
                ")";

remove comma after last column name which is TEXT QuestionsTable.COLUMN_OPTION3 + " TEXT " + ")";

Akash Patel · Accepted Answer · 2018-12-26 07:58:20Z

0

Do not use empty space for column name.

error: Caused by: android.database.sqlite.SQLiteException: near "1": syntax error (code 1): , while compiling: CREATE TABLE quiz_questions ( _id INTEGER PRIMARY KEY AUTOINCREMENT, question TEXT, option 1 TEXT, option 2 TEXT, option 3 TEXT, )

solution: Remove space from column name.

Use option_1, option_2, option_3 instead of option 1, option 2, option 3.

And also remove last comma from query.

Your query should be as below,

CREATE TABLE quiz_questions ( _id INTEGER PRIMARY KEY AUTOINCREMENT, question TEXT, option_1 TEXT, option_2 TEXT, option_3 TEXT)

answered Dec 26, 2018 at 7:58

Akash Patel

3,1413 gold badges18 silver badges28 bronze badges

Comments

sticky bit · Accepted Answer · 2018-12-26 08:00:06Z

The comma at the end of the definition of the last column has to be removed.

But the actual error you posted is generated because you have column names with spaces in them, option 1. The parser thinks the column name is option and doesn't know what to do with the 1. I strongly advise not to have column names (or names of any object) with spaces (or any other characters than alphanumerics and an underscore), that only leads to trouble. If you however insist using such names, you must enclose them in double quotes.

...
final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
        "\"" + QuestionsTable.TABLE_NAME + "\" ( "+
        "\"" + QuestionsTable._ID + "\" INTEGER PRIMARY KEY AUTOINCREMENT " +
        "\"" + QuestionsTable.COLUMN_QUESTION + "\" TEXT, " +
        "\"" + QuestionsTable.COLUMN_OPTION1 + "\" TEXT, " +
        "\"" + QuestionsTable.COLUMN_OPTION2 + "\" TEXT, " +
        "\"" + QuestionsTable.COLUMN_OPTION3 + "\" TEXT " +
        ")";
...

But be aware, if you do so, you have to enclose the object name in double quotes in any query you use it afterwards.

Collectives™ on Stack Overflow

android.database.sqlite.SQLiteException: near "1": syntax error (code 1) when creating a new table to store data

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related