SQLiteException: near "CREATE": syntax error (code 1)

Question

I just started learning how to develop in Android and my first project is building an app using SQLite to store student names and consequently displaying the list. I am getting the below error message when attempting to run the application:

Caused by: android.database.sqlite.SQLiteException: near "CREATE": syntax error     (code 1): , while compiling: DROP TABLE IF EXISTS CREATE TABLE students(id INTEGER   PRIMARY KEY AUTOINCREMENT,firstname TEXT,lastname TEXT );

I have been looking and looking over my code to see what "," or space I might be missing but have been out of luck. Hopefully you can take a look at my code and help me out.

MyDBHandler.java is as follows:

package com.sammojohn.kiddosdb;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

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

public class MyDBHandler extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "kiddos.db";
    private static final int DATABASE_VERSION = 3;
    private static final String TABLE_STUDENTS = "students";

    private static final String COLUMN_ID = "id";
    private static final String COLUMN_FIRSTNAME = "firstname";
    private static final String COLUMN_LASTNAME = "lastname";
    public static final String TAG = "tag";


   //This part creates the tables
   private static final String CREATE_TABLE_STUDENTS = "CREATE TABLE "
           + TABLE_STUDENTS + "(" + COLUMN_ID
           + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_FIRSTNAME + " TEXT,"
           + COLUMN_LASTNAME + " TEXT );";

    public MyDBHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL(CREATE_TABLE_STUDENTS); // create students table
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_STUDENTS); // drop table if exists

        onCreate(db);
    }

       public long addStudentDetail(StudentsModel student) {
        SQLiteDatabase db = this.getWritableDatabase();

        // Creating content values
        ContentValues values = new ContentValues();
        values.put(COLUMN_FIRSTNAME, student.firstname);
        values.put(COLUMN_LASTNAME, student.lastname);

        // insert row in students table
        long insert = db.insert(TABLE_STUDENTS, null, values);
        return insert;
    }

       public int updateEntry(StudentsModel student) {
        SQLiteDatabase db = this.getWritableDatabase();

        // Creating content values
        ContentValues values = new ContentValues();
        values.put(COLUMN_FIRSTNAME, student.firstname);
        values.put(COLUMN_LASTNAME, student.lastname);

        // update row in students table base on students.is value
        return db.update(TABLE_STUDENTS, values, COLUMN_ID + " = ?",
                new String[] { String.valueOf(student.id) });
    }

    public void deleteEntry(long id) {

        // delete row in students table based on id
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_STUDENTS, COLUMN_ID + " = ?",
                new String[] { String.valueOf(id) });
    }


    public StudentsModel getStudent(long id) {
        SQLiteDatabase db = this.getReadableDatabase();
        // SELECT * FROM students WHERE id = ?;
        String selectQuery = "SELECT  * FROM " + TABLE_STUDENTS + " WHERE "
                + COLUMN_ID + " = " + id;
        Log.d(TAG, selectQuery);

        Cursor c = db.rawQuery(selectQuery, null);

        if (c != null)
            c.moveToFirst();

        StudentsModel kiddos = new StudentsModel();
        kiddos.id=c.getInt(c.getColumnIndex(COLUMN_ID));
        kiddos.firstname=c.getString(c.getColumnIndex(COLUMN_FIRSTNAME));
        kiddos.lastname=c.getString(c.getColumnIndex(COLUMN_LASTNAME));

        return kiddos;
    }


    public List<StudentsModel> getAllStudentsList() {
        List<StudentsModel> studentsArrayList = new ArrayList<StudentsModel>();

        String selectQuery = "SELECT  * FROM " + TABLE_STUDENTS;
        Log.d(TAG, selectQuery);

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {

                StudentsModel kiddos = new StudentsModel();
                kiddos.id = c.getInt(c.getColumnIndex(COLUMN_ID));
                kiddos.firstname = c.getString(c
                        .getColumnIndex(COLUMN_FIRSTNAME));
                kiddos.lastname = c.getString(c.getColumnIndex(COLUMN_LASTNAME));

                // adding to Students list
                studentsArrayList.add(kiddos);
            } while (c.moveToNext());
        }


        return studentsArrayList;
    }
}

And my MainActivity.java is:

package com.sammojohn.kiddosdb;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

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


public class MainActivity extends Activity {

    EditText firstname, lastname, id;
    Button addButton, deleteButton;
    TextView tv;

    List<StudentsModel> list = new ArrayList<StudentsModel>();
    MyDBHandler db;



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

        db = new MyDBHandler(getApplicationContext());
        firstname = (EditText) findViewById(R.id.firstInput);
        lastname = (EditText) findViewById(R.id.lastInput);
        id = (EditText) findViewById(R.id.idInput);
        addButton = (Button) findViewById(R.id.add);
        deleteButton = (Button) findViewById(R.id.delete);
        tv = (TextView) findViewById(R.id.displayData);
       // addButton.setOnClickListener(this);
        //deleteButton.setOnClickListener(this);

        //adding some dummy users
        StudentsModel student = new StudentsModel();
        student.firstname = "Samuel";
        student.lastname = "Johnson";
        db.addStudentDetail(student);
        list = db.getAllStudentsList();
        print(list);
    }

    private void print(List<StudentsModel> list){
        String value = "";
        for(StudentsModel sm : list){
            value = value+"ID: "+sm.id+"\n"+"First Name: "+sm.firstname+"\n"+"Last Name: "+sm.lastname+"\n";
        }
        tv.setText(value);
    }

    private void onClick(View v) {
        // TODO Auto-generated method stub
        if(v == findViewById(R.id.delete)){
            tv.setText("");
            String student_id = id.getText().toString();
            db.deleteEntry(Integer.parseInt(student_id));
            list = db.getAllStudentsList();
            print(list);
        }
        if(v == findViewById(R.id.add)){
            tv.setText("");
            StudentsModel student = new StudentsModel();
            student.firstname = firstname.getText().toString();
            student.lastname = lastname.getText().toString();
            db.addStudentDetail(student);
            list = db.getAllStudentsList();
            print(list);
        }
    }


}

Any help will be greatly appreciated. Thank you in advance for taking the time to go over this.

Regards, Sam

Answer 1

Try split drop table and create table statements and run them one by one .

Also checkout drop table syntax. I guess you should specify table name there.

Answer 2

This is wrong:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_STUDENTS); // drop table if exists

    onCreate(db);
}

it should be

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_STUDENTS ); // drop table if exists

    onCreate(db);
}

or alternatively:

private static final String SQL_DELETE_TABLES =
        "DROP TABLE IF EXISTS " + TABLE_STUDENTS;

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL(SQL_DELETE_TABLES); // drop table if exists

    onCreate(db);
}