error in sqlite "DROP TABLE IF EXISTS" android

Question

so i have a problem in my DBAdapter class its just crushes when i try to open the database: from the LogCat i guess the problem is in the onUpgrade function:

 public void onUpgrade(SQLiteDatabase db, int oldVersion,
 int newVersion)
  {
       Log.w("SingleDBAdapter", "Upgrading database from version " + oldVersion
       + " to "
       + newVersion + ", which will destroy all old data");
       db.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE);
       onCreate(db);
  }
 }

here is the error:

07-28 11:32:49.443: E/Database(1244): Failure 1 (near "122": syntax error) on 0x2435b0 when preparing 'DROP TABLE IF EXISTS 122'.
07-28 11:32:49.463: D/AndroidRuntime(1244): Shutting down VM
07-28 11:32:49.463: W/dalvikvm(1244): threadid=1: thread exiting with uncaught exception (group=0x4001d800)
07-28 11:32:49.473: E/AndroidRuntime(1244): FATAL EXCEPTION: main
07-28 11:32:49.473: E/AndroidRuntime(1244): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.shop.list/com.shop.list.main}: android.database.sqlite.SQLiteException: near "122": syntax error: **DROP TABLE IF EXISTS 122**
07-28 11:32:49.473: E/AndroidRuntime(1244):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2663)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2679)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at android.app.ActivityThread.access$2300(ActivityThread.java:125)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2033)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at android.os.Handler.dispatchMessage(Handler.java:99)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at android.os.Looper.loop(Looper.java:123)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at android.app.ActivityThread.main(ActivityThread.java:4627)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at java.lang.reflect.Method.invokeNative(Native Method)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at java.lang.reflect.Method.invoke(Method.java:521)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at dalvik.system.NativeStart.main(Native Method)
07-28 11:32:49.473: E/AndroidRuntime(1244): Caused by: android.database.sqlite.SQLiteException: near "122": syntax error: DROP TABLE IF EXISTS 122
07-28 11:32:49.473: E/AndroidRuntime(1244):     at android.database.sqlite.SQLiteDatabase.native_execSQL(Native Method)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1727)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at com.shop.list.ListDBAdapter$DatabaseHelper.onUpgrade(ListDBAdapter.java:51)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:108)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at com.shop.list.ListDBAdapter.open(ListDBAdapter.java:60)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at com.shop.list.main.onCreate(main.java:60)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
07-28 11:32:49.473: E/AndroidRuntime(1244):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2627)
07-28 11:32:49.473: E/AndroidRuntime(1244):     ... 11 more

i highlighted the problem but i cant solve it :/

*the sterisks in the syntax error are not part of the LogCat i just highlighted the "main" error — eli
– eli, Commented Jul 28, 2012 at 11:41
@user1375265 Can you post full code of your DBAdapter class. — Akshay
– Akshay, Commented Jul 28, 2012 at 12:04

Andriy M · Accepted Answer · 2012-07-28 11:53:12Z

39

The problem seems to be with this statement:

DROP TABLE IF EXISTS 122

where 122, if it is really the name of the table to drop, is not delimited (with ', for instance) and thus cannot be treated as a name. But the parser expects a name there. Just enclose the name in single or double quotes, and it should work:

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

answered Jul 28, 2012 at 11:53

Andriy M

78k18 gold badges100 silver badges157 bronze badges

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

Comments

johngray1965 · Accepted Answer · 2014-03-08 19:55:31Z

2

if the table name were all alpha characters, your original code would have worked. Since the table name isn't all alpha, it needs to be surrounded with single quotes.

answered Mar 8, 2014 at 19:55

johngray1965

7216 silver badges14 bronze badges

Comments

Sathish Gadde · Accepted Answer · 2016-09-01 05:23:46Z

1

db = This is your database handler class

db.execSQL("DROP TABLE IF EXISTS YourTableName");

edited Sep 1, 2016 at 5:23

answered Dec 16, 2015 at 11:07

Sathish Gadde

1,65317 silver badges29 bronze badges

Comments

Hans Hohenfeld · Accepted Answer · 2012-07-28 11:50:32Z

0

I think your missing a ';' at the end of your SQL statement

Beyond that I just checked on an sqlite3 console, it seems, that "122" is an invalid table name:

sqlite> drop table if exists 122;
Error: near "122": syntax error
sqlite> drop table if exists test;
sqlite>

edited Jul 28, 2012 at 11:50

answered Jul 28, 2012 at 11:41

Hans Hohenfeld

1,74911 silver badges14 bronze badges

Comments

Manmohan Soni · Accepted Answer · 2014-04-29 05:34:43Z

-3

Hi Think u should use call the statement : "context.deleteDatabase(DATABASE_NAME);"

before the call of "onCreate();"

public void onUpgrade(SQLiteDatabase db, int oldVersion,
 int newVersion)
  {
       Log.w("SingleDBAdapter", "Upgrading database from version " + oldVersion
       + " to "
       + newVersion + ", which will destroy all old data");
       context.deleteDatabase(DATABASE_NAME);
       onCreate(db);
  }
 }

answered Apr 29, 2014 at 5:34

Manmohan Soni

6,7212 gold badges25 silver badges30 bronze badges

Comments

VCHe · Accepted Answer · 2015-08-15 15:47:38Z

-3

You should change your:

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

with:

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

Also, you need to check if newVersion > oldVersion

answered Aug 15, 2015 at 15:47

VCHe

324 bronze badges

Collectives™ on Stack Overflow

error in sqlite "DROP TABLE IF EXISTS" android

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related