Lisa Anne Lisa Anne - 27 days ago 14
Android Question

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

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;

}

Answer

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