DB Exception while Dropping table

Question

I am somewhere wrong with syntax.

Crash comes when ever i call clearAll() method here is my method.

    /**
     * Method to clear all db
     */
    public void clearAll() {
        if(sqLiteDatabase==null){
            sqLiteDatabase=this.getWritableDatabase();
        }else {
            if(!sqLiteDatabase.isOpen()){
                sqLiteDatabase=this.getWritableDatabase();
            }
        }
        sqLiteDatabase.execSQL(SqlConstant.DROP_TABLE+ SqlConstant.CREATE_USER_PERSONAL_DETAILS);
        sqLiteDatabase.execSQL(SqlConstant.DROP_TABLE+ SqlConstant.CREATE_POSITIONS);
        sqLiteDatabase.execSQL(SqlConstant.DROP_TABLE+ SqlConstant.CREATE_TABLE_INDUSTRY);
        sqLiteDatabase.execSQL(SqlConstant.DROP_TABLE+ SqlConstant.CREATE_TABLE_INTERESTS);
        sqLiteDatabase.execSQL(SqlConstant.DROP_TABLE+ SqlConstant.CREATE_TABLE_JOB_LOCATION);
        sqLiteDatabase.execSQL(SqlConstant.DROP_TABLE+ SqlConstant.CREATE_TABLE_JOB_CITY);
        sqLiteDatabase.execSQL(SqlConstant.DROP_TABLE+ SqlConstant.CREATE_TABLE_KNOWN_LANGUAGE);
        sqLiteDatabase.execSQL(SqlConstant.DROP_TABLE+ SqlConstant.CREATE_TABLE_ORGANIZATION_DETAILS);
        sqLiteDatabase.execSQL(SqlConstant.DROP_TABLE+ SqlConstant.CREATE_TABLE_QUALIFICATIONS);
        sqLiteDatabase.execSQL(SqlConstant.DROP_TABLE+ SqlConstant.CREATE_TABLE_CURRENT_LOCATION);
    }

Query:

DROP TABLE IF EXISTS create table user_personal_detail ( first_name varchar2 , middle_name varchar2 , last_name varchar2 , user_name varchar2 , email_id varchar2 , alternative_email_id varchar2 , phone_number varchar2 , mobile_code varchar2 , date_of_birth varchar2 , gender varchar2 , marrital_status varchar2 , education_level varchar2 , profile_image varchar2 , hupo_score varchar2 , hupo_loyalty_point varchar2 , signup_date varchar2 , totalWorkExperience varchar2 )

Crash report:

04-02 16:11:50.943 23043-23043/com.app.hupo D/AndroidRuntime: Shutting down VM 04-02 16:11:50.944 23043-23043/com.app.hupo E/AndroidRuntime: FATAL EXCEPTION: main Process: com.app.hupo, PID: 23043 android.database.sqlite.SQLiteException: near "create": syntax error (code 1): , while compiling: DROP TABLE IF EXISTS create table user_personal_detail ( first_name varchar2 , middle_name varchar2 , last_name varchar2 , user_name varchar2 , email_id varchar2 , alternative_email_id varchar2 , phone_number varchar2 , mobile_code varchar2 , date_of_birth varchar2 , gender varchar2 , marrital_status varchar2 , education_level varchar2 , profile_image varchar2 , hupo_score varchar2 , hupo_loyalty_point varchar2 , signup_date varchar2 , totalWorkExperience varchar2 ) at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31) at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1675) at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1606) at com.app.hupo.database.DataBaseHelper.clearAll(DataBaseHelper.java:1048) at

You can uninstall and re-install app hope it will work or if this still occurs try to update DATABASE_VERSION — Xay
– Xay, Commented Apr 2, 2018 at 10:57
Where did you get that syntax:DROP TABLE IF EXISTS CREATE TABLE ...?! — Phantômaxx
– Phantômaxx, Commented Apr 2, 2018 at 11:07

Jyoti JK · Accepted Answer · 2018-04-02 11:03:19Z

2

android.database.sqlite.SQLiteException: near "create": syntax error (code 1): , while compiling: DROP TABLE IF EXISTS create table user_personal_detail..

here, the table name is missing .

You need to remove SqlConstant.DROP_TABLE+

Or use,

 SqlConstant.DROP_TABLE+ "tablename ;"+SqlConstant.CREATE_USER_PERSONAL_DETAILS)

Your command should be like,

DROP TABLE IF EXISTS user_personal_detail ; create table user_personal_detail...

Do it for all the tables.

edited Apr 2, 2018 at 11:03

answered Apr 2, 2018 at 10:58

Jyoti JK

2,1711 gold badge20 silver badges41 bronze badges

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

4 Comments

Jyoti JK Over a year ago

Then you need to use drop table if exists tablename. In your code table name is missing

Jyoti JK Over a year ago

Are you getting any exception after adding table name?

Rishabh Saxena Over a year ago

Same exception is coming

Jyoti JK Over a year ago

Please post the exception which you got now. And also do Clean your project,do invalidate caches then debug app

Collectives™ on Stack Overflow

DB Exception while Dropping table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related