E/SQLiteLog﹕ (1) near "TABLEaccount": syntax error

Question

I am getting this error in my app when I try to write to database E/SQLiteLog﹕ (1) near "TABLEaccount": syntax error

I cant figure it out why? Here is my code for SQLite databse. I am attaching the complete code for database class here

package com.astrolabe.tcpremote1;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.sql.SQLDataException;
import java.sql.SQLException;

public class accountsDB {

    //private variables
    public static final String KEY_ROWID = "_id";
    public static final String KEY_CTYPE = "c_type";
    public static final String KEY_SNAME = "s_name";
    public static final String KEY_SNUMB = "s_numb";
    public static final String KEY_USRN = "user_name";
    public static final String KEY_PASS = "password";
    public static final String KEY_EMPTY = "empty";

    private static final String DATABASE_NAME ="account.db";
    private static final String DATABASE_TABLE ="accounts";
    private static final int DATABASE_VERSION =1;

    private DbHelper  ourHelper;
    private final Context  ourContext;
    private SQLiteDatabase  ourDatabase;

    public long createEntry(String ctype, String siteName, String siteNum, String username, String pass) {

        ContentValues cv= new ContentValues();
        cv.put(KEY_CTYPE,ctype);
        cv.put(KEY_SNAME,siteName);
        cv.put(KEY_SNUMB,siteNum);
        cv.put(KEY_USRN,username);
        cv.put(KEY_PASS,pass);
        //cv.put(KEY_EMPTY,0);
        return ourDatabase.insert(DATABASE_TABLE,null,cv);
    }

    private static class DbHelper extends SQLiteOpenHelper {
         public DbHelper(Context context){
             super(context,DATABASE_NAME,null,DATABASE_VERSION);

         }

         @Override
         public void onCreate(SQLiteDatabase db) {
             db.execSQL("CREATE TABLE"+ DATABASE_NAME + "(" +
                KEY_ROWID + "INTEGER PRIMARY KEY AUTOINCREMENT"
                     + KEY_CTYPE + "TEXT NOT NULL,"
                     +  KEY_SNAME + "TEXT NOT NULL,"
                     +  KEY_SNUMB + "TEXT NOT NULL,"
                     +  KEY_USRN + "TEXT NOT NULL,"
                     +  KEY_PASS + "TEXT NOT NULL,"
                     +  KEY_EMPTY + "INTEGER);"
                );

         }

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

         }
     }

    public accountsDB (Context c)
    {
        ourContext= c;
    }
    public accountsDB open() throws SQLException
    {
        ourHelper = new DbHelper(ourContext);
        ourDatabase = ourHelper.getWritableDatabase();
        return  this;
    }
    public  void close()
    {
        ourHelper.close();
    }

  }

Answer 1

Check your code-

public class accountsDB {

    //private variables
    public static final String KEY_ROWID = "_id";
    public static final String KEY_CTYPE = "c_type";
    public static final String KEY_SNAME = "s_name";
    public static final String KEY_SNUMB = "s_numb";
    public static final String KEY_USRN = "user_name";
    public static final String KEY_PASS = "password";
    public static final String KEY_EMPTY = "empty";

    private static final String DATABASE_NAME ="account.db";
    private static final String DATABASE_TABLE ="accounts";
    private static final int DATABASE_VERSION =1;

    private DbHelper  ourHelper;
    private final Context  ourContext;
    private SQLiteDatabase  ourDatabase;

    public long createEntry(String ctype, String siteName, String siteNum, String username, String pass) {


        ContentValues cv= new ContentValues();
        cv.put(KEY_CTYPE,ctype);
        cv.put(KEY_SNAME,siteName);
        cv.put(KEY_SNUMB,siteNum);
        cv.put(KEY_USRN,username);
        cv.put(KEY_PASS,pass);
        //cv.put(KEY_EMPTY,0);
        return ourDatabase.insert(DATABASE_TABLE,null,cv);
    }

    private class DbHelper extends SQLiteOpenHelper {
         public DbHelper(Context context){
             super(context,DATABASE_NAME,null,DATABASE_VERSION);

         }

         @Override
         public void onCreate(SQLiteDatabase db) {
             String q="CREATE TABLE "+ DATABASE_TABLE + " (" +
                    KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                     + KEY_CTYPE + " TEXT NOT NULL,"
                     +  KEY_SNAME + " TEXT NOT NULL,"
                     +  KEY_SNUMB + " TEXT NOT NULL,"
                     +  KEY_USRN + " TEXT NOT NULL,"
                     +  KEY_PASS + " TEXT NOT NULL,"
                     +  KEY_EMPTY + " INTEGER);";

             Log.e("","ssssssssss: "+ q);


             db.execSQL(q);

         }

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

         }
     }

    public accountsDB (Context c)
    {
        ourContext= c;
    }
    public accountsDB open() throws SQLException
    {
        ourHelper = new DbHelper(ourContext);
        ourDatabase = ourHelper.getWritableDatabase();
        return  this;
    }
    public  void close()
    {
        ourHelper.close();
    }

  }

Answer 2

You need to add whitespace between SQL tokens, e.g. instead of

db.execSQL("CREATE TABLE"+ DATABASE_NAME + "(" +

change to

db.execSQL("CREATE TABLE "+ DATABASE_TABLE + "(" +

Notice the space after TABLE. I've also changed the table name from account.db to accounts.

This is what caused the exception. But there are other problems, for example in column specifications

KEY_ROWID + "INTEGER PRIMARY KEY AUTOINCREMENT"
    + KEY_CTYPE + "TEXT NOT NULL,"
    +  KEY_SNAME + "TEXT NOT NULL,"
    +  KEY_SNUMB + "TEXT NOT NULL,"
    +  KEY_USRN + "TEXT NOT NULL,"
    +  KEY_PASS + "TEXT NOT NULL,"
    +  KEY_EMPTY + "INTEGER);"

you need spaces between column names and datatypes and a comma , after each column spec:

KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
    + KEY_CTYPE + " TEXT NOT NULL,"
    +  KEY_SNAME + " TEXT NOT NULL,"
    +  KEY_SNUMB + " TEXT NOT NULL,"
    +  KEY_USRN + " TEXT NOT NULL,"
    +  KEY_PASS + " TEXT NOT NULL,"
    +  KEY_EMPTY + " INTEGER);"

In addition there's similar whitespace issue in onUpgrade():

db.execSQL("DROP TABLE IF EXISTS" +DATABASE_TABLE);

should be

db.execSQL("DROP TABLE IF EXISTS " +DATABASE_TABLE);

For easier debugging on your own, have a look at the exception stacktrace to find the exact line of code where the exception occurred.