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, Jul 28 '12 at 11:41
@user1375265 Can you post full code of your DBAdapter class. — Akshay, Jul 28 '12 at 12:04

score 38 · Answer 1 · answered Jul 28 '12 at 11:53

38

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 '12 at 11:53

Andriy M

71,352
17
87
142

Thank you Sir, Great answer – salih kallai Jan 25 '17 at 13:54

score 2 · Answer 2 · answered Mar 08 '14 at 19:55

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 08 '14 at 19:55

johngray1965

103
1
6

Hans Hohenfeld · Answer 3 · 2012-07-28T11:50:32.433

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 '12 at 11:50

answered Jul 28 '12 at 11:41

Hans Hohenfeld

1,689
10
14

Sathish Gadde · Answer 4 · 2016-09-01T05:23:46.603

0

db = This is your database handler class

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

edited Sep 01 '16 at 05:23

answered Dec 16 '15 at 11:07

Sathish Gadde

921
9
24

score -3 · Answer 5 · answered Apr 29 '14 at 05:34

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);
  }
 }

score -3 · Answer 6 · answered Aug 15 '15 at 15:47

-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 '15 at 15:47

VCHe

32
4

error in sqlite "DROP TABLE IF EXISTS" android

6 Answers6