Error creating a sqlite database giving syntax error

Question

I am on working android contact list application, I just started development in android platform, In my contactlist app, i have to create a sqlite database table to store contacts i am facing difficulty on creating a sqlite database it gives me a error on my logcat window,like: "Logcat show these error "sqlite returned: error code = 1, msg = near "," syntax error" Failure 1 (near ",": syntax error) on 0x1f14d0 when preparing 'CREATE TABLE Places(ID INT PRIMARY KEY NOT NULL,Name TEXT NOT NULL,Phone TEXT NOT NULL,Address TEXT NOT NULL,Website TEXT,HOME TEXT NOT NULL,' FATAL EXCEPTION: main "java.lang.RuntimeException: Unable to start activity ComponentInfo{com.webpreneur_contactlist/com.webpreneur_contactlist.WebpreneurActivity}: java.lang.IllegalStateException: database not open" please help me

public class DBHandler extends SQLiteOpenHelper{

    private static final int DB_Version = 1;

    private static final String DB_Name = "Places";

    protected static final String Places_Table = "Places";

    String name, Address, Website,ID;
    //WebpreneurActivity Contact;
 public static final String Key_ID = "ID";
 public static final String Key_Name = "Name";
 public static final String Key_Phone = "Phone";
 public  static final String Key_Address = "Address";
public static final String Key_Website = "Website";
public  static final String Key_Home = "HOME";
public static final String PROJECTION[] = {
    Key_ID,
    Key_Name,
    Key_Address,
    Key_Phone,
    Key_Home
};

    String CREATE_PLACES_TABLE = "create table if not exists Places_Table (id integer primary key ,"+
            "name VARCHAR not null, phone VARCHAR not null, address VARCAHR not null, website VARCHAR not null,Home VARCHAR not null)";

    Context context;
    SQLiteDatabase db;

    public DBHandler(Context context) {

        super(context, DB_Name, null, DB_Version);
        Log.d("database1" ,"4");
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try{
            Log.d("DB", "DB creationnewwwww");
                db.execSQL("CREATE TABLE  " + Places_Table + "(" +
                        Key_ID + " INT PRIMARY KEY NOT NULL," +
                        Key_Name + " TEXT NOT NULL," +
                        Key_Phone + " TEXT NOT NULL," +
                        Key_Address + " TEXT NOT NULL," +
                        Key_Website + " TEXT," +
                        Key_Home + " TEXT,");
                Log.d("DB", "DB creationnewwwwwwwwwwwwwwwwwwwwwwwwwww");
        }
        catch(SQLiteException e){
            Log.d("DB", "DB creation excptionhhhhhhhhhhhhhhh");
            e.printStackTrace();
        }

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

        db.execSQL("Drop Table If Exists" + Places_Table);
        onCreate(db);
    }



    public DBHandler open()
    {Log.d("DB", "DB creation 9");
     db = this.getWritableDatabase();
        onCreate(db);
    Log.d("DB", "DB creation 9");
    return this;
    }
    public void close()
    {
        db.close();

    }

    //Adding Places 
    void addPlaces( int id, String name,String phone, String address,String url){

        Log.d("DB", "DB creation 1");
        //SinglePlaceActivity single = new SinglePlaceActivity();   Log.d("DB", "DB creation 2");
        ContentValues contentValues = new ContentValues();
        Log.d("DB", "DB creation 3");
        contentValues.put(Key_ID, id);
        Log.d("DB", "DB creation 4");
        contentValues.put(Key_Name, name);
        contentValues.put(Key_Phone, phone);
        contentValues.put(Key_Address, address);
        contentValues.put(Key_Website, url);
        Log.d("DB", "DB creation 4");
        db.insert(Places_Table, null, contentValues);
        Log.d("DB", "DB creation 5555");
        //db.close();
    }

    public String getdata() {
        // TODO Auto-generated method stub
        String [] columns =new String[]{Key_ID ,Key_Name,Key_Address,Key_Website};
        Cursor c =db.query(DB_Name, columns, null, null, null, null, null);
        String Result=""; 
        int iRow=c.getColumnIndex(Key_ID);
        int iName=c.getColumnIndex(Key_Name);
        int iAddress=c.getColumnIndex(Key_Address);
        int iWebsite=c.getColumnIndex(Key_Website);
        for(c.moveToFirst();!c.isAfterLast();c.moveToNext()){
            Result=Result+c.getString(iRow)+"    "+c.getString(iName)+ "  "+c.getString(iAddress)+ "   "+c.getString(iWebsite)+ "\n";
        }

        return Result;
    }   


    public void createRow(String name, String address, String Phone, String home)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(Key_Name, name);
        initialValues.put(Key_Address, address);
        initialValues.put(Key_Phone, Phone);
        initialValues.put(Key_Home, home);
                //pass the initialValues to the database to insert the row
        db.insert(Places_Table, null, initialValues);
    }

        public void deleteRow(long rowId){
        db.delete(Places_Table, Key_ID+"="+rowId,null);
    }

        public boolean updateRow (long rowId, String name, String address, String Phone, String home){
        ContentValues args = new ContentValues();
        args.put(Key_Name, name);
        args.put(Key_Address, address);
        args.put(Key_Phone, Phone);
        args.put(Key_Home, home);
        return db.update(Places_Table, args, Key_ID +"="+ rowId, null)>0;
    }
        public Cursor fetchRow(long rowId) throws SQLException{
            Cursor result = db.query( Places_Table, null, 
                    Key_ID + "=" + rowId, null, null, null,null);
            if ((result.equals(rowId)) || !result.isFirst()) {
                throw new SQLException("No note matching ID: " + rowId);
            }
            return result;
        }

        public Cursor fetchAllRows(){
            Log.d("Your Location4", "ok99:");
            return db.query(Places_Table, PROJECTION, 
                    null, null, null, null, null);

        }
}

"java.lang.IllegalStateException: database not open" might be a clue. — Nick
– Nick, Commented Jan 22, 2014 at 8:34

laalto · Accepted Answer · 2014-01-22 08:42:44Z

2

Replace

Key_Home + " TEXT,");

with

Key_Home + " TEXT)");

There's a , so that further column specifications are expected but there's nothing followin so you need to terminate the table specification with ).

Also, you should not be catching SQLiteException in database helper onCreate(). When onCreate() returns normally, the database creation is assumed to be succesful.

After fixing the problems, uninstall your app so the old, incorrect database file is removed and onCreate() is run again.

Also in your open() you seem to call onCreate() yourself. Don't do that. get{Read,Writ}ableDatabase() will do it for you when needed.

edited Jan 22, 2014 at 8:42

answered Jan 22, 2014 at 8:33

laalto

153k68 gold badges293 silver badges306 bronze badges

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

2 Comments

Shazar Over a year ago

Hi Laalto its very helpful for me but locat giving me another error of "java.lang.RuntimeException: Unable to start activity ComponentInfo{com.webpreneur_contactlist/com.webpreneur_contactlist.WebpreneurActivity}: java.lang.IllegalStateException: database not open, please help me ,your guidance would highly appreciated.

laalto Over a year ago

Check the stacktrace where you are in your code when the exception is thrown.

NoGall · Accepted Answer · 2014-01-22 08:35:22Z

You're not closing off your SQL statement. For example:

this:

db.execSQL("CREATE TABLE  " + Places_Table + "(" +
                    Key_ID + " INT PRIMARY KEY NOT NULL," +
                    Key_Name + " TEXT NOT NULL," +
                    Key_Phone + " TEXT NOT NULL," +
                    Key_Address + " TEXT NOT NULL," +
                    Key_Website + " TEXT," +
                    Key_Home + " TEXT,");

Should be:

db.execSQL("CREATE TABLE  " + Places_Table + "(" +
                    Key_ID + " INT PRIMARY KEY NOT NULL," +
                    Key_Name + " TEXT NOT NULL," +
                    Key_Phone + " TEXT NOT NULL," +
                    Key_Address + " TEXT NOT NULL," +
                    Key_Website + " TEXT," +
                    Key_Home + " TEXT)");

Which will then close off the create table statement. It was expecting another parameter, due to the comma.

Collectives™ on Stack Overflow

Error creating a sqlite database giving syntax error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related