android.database.sqlite.SQLiteException: near "0": syntax error: ALTER TABLE data ADD COLUMN 0 INTEGER

Question

I am very new to databases.

I am trying to add columns to a table, I need to add columns named "0", "1", "2", etc.. until "94169".

I get the following error:

05-04 10:12:50.656: E/An    droidRuntime(2022): FATAL EXCEPTION: main
05-04 10:12:50.656: E/AndroidRuntime(2022): java.lang.RuntimeException: Unable to start     activity ComponentInfo{com.example.koday/com.example.koday.MainActivity}:     android.database.sqlite.SQLiteException: near "0": syntax error: ALTER TABLE data ADD     COLUMN 0 INTEGER
05-04 10:12:50.656: E/AndroidRuntime(2022):     at     android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1647)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at     android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1663)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at android.app.ActivityThread.access$1500(ActivityThread.java:117)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:931)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at android.os.Handler.dispatchMessage(Handler.java:99)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at android.os.Looper.loop(Looper.java:130)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at android.app.ActivityThread.main(ActivityThread.java:3683)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at java.lang.reflect.Method.invokeNative(Native Method)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at java.lang.reflect.Method.invoke(Method.java:507)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at dalvik.system.NativeStart.main(Native Method)
05-04 10:12:50.656: E/AndroidRuntime(2022): Caused by: android.database.sqlite.SQLiteException: near "0": syntax error: ALTER TABLE data ADD COLUMN 0 INTEGER
05-04 10:12:50.656: E/AndroidRuntime(2022):     at android.database.sqlite.SQLiteDatabase.native_execSQL(Native Method)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1763)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at com.example.koday.DataBaseHelper.aggiungi(DataBaseHelper.java:186)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at com.example.koday.MainActivity.onCreate(MainActivity.java:61)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
05-04 10:12:50.656: E/AndroidRuntime(2022):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1611)
05-04 10:12:50.656: E/AndroidRuntime(2022):     ... 11 more

This is the snippet of code that causes the error:

public void aggiungi() {
    SQLiteDatabase db = this.getWritableDatabase();

    for (int i = 0; i < 94170; i++) {
    db.execSQL("ALTER TABLE data ADD COLUMN " +Integer.toString(i)+" INTEGER"); 
    }
    db.close();
    return;

}

Why on earth are you adding 94 thousand columns to your table? — tolgap
– tolgap, Commented May 4, 2013 at 10:32
@tolgap bad idea indeed, moreover 94000 exceeds maximum cols allowed :-) — Lisa Anne
– Lisa Anne, Commented May 4, 2013 at 10:51

Sankar V · Accepted Answer · 2013-05-04 11:03:31Z

Legal characters in identifiers

Unquoted identifiers can consist of any alphanumeric characters in the system default character set (utf8), plus the characters '_' and '$'. Identifiers can start with any character that is legal in an identifier, including a digit.

However, an identifier cannot consist entirely of digits because that would make it indistinguishable from a number. MySQL's support for identifiers that begin with a number is somewhat unusual among database systems. If you use such an identifier, be particularly careful if it contains an 'E' or 'e' because those characters can lead to ambiguous expressions. For example, the expression 23e + 14 (with spaces surrounding the '+' sign) means column 23e plus the number 14, but what about 23e+14? Does it mean the same thing, or is it a number in scientific notation? You should also be careful about using identifiers such as 0x1020 that begin with 0x because they might be interpreted as hexadecimal constants.

Reference : http://www.informit.com/articles/article.aspx?p=377068

Kindly consider modifying your database design because 94170 columns exceed the maximum number of allowed columns. Check SQLITE Limits

Collectives™ on Stack Overflow

android.database.sqlite.SQLiteException: near "0": syntax error: ALTER TABLE data ADD COLUMN 0 INTEGER

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related