SQLite syntax error in create table method

Question

logcat says thare is a SQLite error near the word "table" in the database class here. I looked over this code and I don't see any ayntax arrors , am i missing something?

Database class

 public class Database {

public static final String DATABASE = "tester";

public static final int DATABASE_VERSION = 1;

public static final String TABLENAME = "table";

public static final String _ID = "_id";

// collumns
public static final String SAMPLE = "sample";

private static final String SCRIPT_CREATE_TABLE =
       "CREATE TABLE IF NOT EXISTS " + TABLENAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
       SAMPLE + " TEXT)";


            SQLiteDatabase sqLiteDatabase;
            SQLiteHelper sqLiteHelper;
            Context context;

            public Database(Context c){
                  context = c;
                 }

            public void openToRead() throws android.database.SQLException {
                  sqLiteHelper = new SQLiteHelper(context, DATABASE, null, DATABASE_VERSION);
                  sqLiteDatabase = sqLiteHelper.getReadableDatabase();
                 }

            public void openToWrite() throws android.database.SQLException {
                  sqLiteHelper = new SQLiteHelper(context, DATABASE, null, DATABASE_VERSION);
                  sqLiteDatabase = sqLiteHelper.getWritableDatabase();
                 }

                 public void close(){
                 sqLiteHelper.close();
                 }

                 public void deleteDB(){
                     context.deleteDatabase(DATABASE);
                 }

                   public void insert(){

                       ContentValues contentValues = new ContentValues();
                       contentValues.put(SAMPLE, "TEST ONE");
                       sqLiteDatabase.insert(TABLENAME, null, contentValues);


                   }


                   public String get(){
                       String returnString = "";
                       Cursor cursor = sqLiteDatabase.query(TABLENAME, new String[]{SAMPLE}, null, null, null, null, null);
                       if(cursor!=null){
                          cursor.moveToFirst();
                              returnString = cursor.getString(cursor.getColumnIndex(SAMPLE));

                       }
                       return returnString;
                   }



         public class SQLiteHelper extends SQLiteOpenHelper {

                  public SQLiteHelper(Context context, String name,
                    CursorFactory factory, int version) {
                   super(context, name, factory, version);
                  }

                  // onCreate of the SQLiteOpenhelper only called if the database does not already exist
                  @Override
                  public void onCreate(SQLiteDatabase db) {

                   db.execSQL(SCRIPT_CREATE_TABLE);


                  }

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



                   onCreate(db);
                  }

                 }

 }

MainAcivity class

   public class MainActivity extends Activity {
TextView textViewOne;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    textViewOne = (TextView) findViewById(R.id.textView1);


    Database db = new Database(this);
    db.openToWrite();
    db.insert();
    db.close();

    db.openToRead();
    String getStr = db.get();
    db.close();

    textViewOne.setText(getStr);

}



 }

it may be as table is a keyword. so while creating the table, it might be giving error. Try to choose a different table name like 'MyTable` — Shobhit Puri
– Shobhit Puri, Commented Aug 18, 2013 at 10:07

Paweł Piecyk · Accepted Answer · 2013-08-18 11:03:22Z

3

You've got table with name "table" - this is the problem, because it's SQLite's keyword.

If you would like to create table with that name you should quote it like below:

CREATE TABLE IF NOT EXISTS "table" ...

edited Aug 18, 2013 at 11:03

answered Aug 18, 2013 at 10:07

Paweł Piecyk

2,79917 silver badges17 bronze badges

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

Comments

Joachim Isaksson · Accepted Answer · 2013-08-18 10:12:12Z

2

You're trying to create a table called table (a reserverd word in SQLite) without quoting the name. Doing the same thing at the sqlite3 prompt gives;

sqlite> CREATE TABLE IF NOT EXISTS table (_id INTEGER PRIMARY KEY AUTOINCREMENT, 
                                          sample TEXT);
Error: near "table": syntax error

If you really mean to have a table called "table", you'll need to quote the name;

sqlite> CREATE TABLE IF NOT EXISTS "table" (_id INTEGER PRIMARY KEY AUTOINCREMENT, 
                                            sample TEXT);
sqlite>

answered Aug 18, 2013 at 10:12

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Collectives™ on Stack Overflow

SQLite syntax error in create table method

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related