Android SQLite CREATE TABLE not working

Question

CREATE TABLE is not creating table

From here I am taking table name

private static  String getTableName() {
    Calendar calendar = Calendar.getInstance();
    calendar.setTimeZone(TimeZone.getTimeZone("Asia/Calcutta"));
    SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
    String date = df.format(calendar.getTime());
    return date;
}


//table name
private String TABLE_DATE = getTableName();

Here is the code to create table

@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_ENTRY_TABLE = "CREATE TABLE " +TABLE_DATE + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_ROLL + " TEXT,"
            + KEY_DATE + " TEXT," +  KEY_TIME + " TEXT" + ")";
    db.execSQL(CREATE_ENTRY_TABLE);
}

Error

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.jagdishchoudhary.iitgandhinagarmesssystem/com.jagdishchoudhary.iitgandhinagarmesssystem.MainActivity}: android.database.sqlite.SQLiteException: near "20180119": syntax error (code 1): , while compiling: CREATE TABLE  20180119(id INTEGER PRIMARY KEY,Roll TEXT,Date TEXT,Time TEXT)
                                               at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2659)
                                               at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2724)
                                               at android.app.ActivityThread.-wrap12(ActivityThread.java)
                                               at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1473)
                                               at android.os.Handler.dispatchMessage(Handler.java:102)
                                               at android.os.Looper.loop(Looper.java:154)
                                               at android.app.ActivityThread.main(ActivityThread.java:6123)
                                               at java.lang.reflect.Method.invoke(Native Method)
                                               at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:867)
                                               at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:757)
                                            Caused by: android.database.sqlite.SQLiteException: near "20180119": syntax error (code 1): , while compiling: CREATE TABLE  20180119(id INTEGER PRIMARY KEY,Roll TEXT,Date TEXT,Time TEXT)

Please see the logcat.

Why create it in your app anyway? Deploy the DB with your app after you prepared it. — juergen d, Jan 18 '18 at 23:08

ViaTech · Accepted Answer · 2018-01-19T13:21:36.253

3

I am pretty close to 100% sure that 20180119 is an invalid table name, which seems to be what the error log is referring to.

Add an underscore to the start of your returned table name and I think you will find it works like you would expect.

Ex:

private String TABLE_DATE = "_"+getTableName();

This shouldn't be needed, but if that doesn't work for some odd reason, try appending a letter to the underscore like this:

private String TABLE_DATE = "_T"+getTableName();

edited Jan 19 '18 at 13:21

answered Jan 18 '18 at 23:23

ViaTech

2,143
1
16
51

1

Fully numeric names or table, column names etc starting with a numeric are possible/usable. However, they have to be enclosed in special characters so that the name is forced/coerced to become an identifier. Valid characters are ***" "*** (double quotes) ***grave accents (ASCII code 96)*** and ***[ ]***. e.g: :- `CREATE TABLE IF NOT EXISTS "2017" ("1234567890abc" INTEGER PRIMARY KEY, [1aaaaa] TEXT)` (note grave accents are used to display code so can't be shown). [SQL As Understood By SQLite - SQLite Keywords](https://www.sqlite.org/lang_keywords.html) Note! grave accents are not standard SQL. – MikeT Jan 19 '18 at 00:29
I do remember reading about wrapping the name in [] being a solution to numeric Table Names a bit ago, so this is very likely a valid solution as well. Thanks for your comment @MikeT, that didn't pop into my mind while originally answering. – ViaTech Jan 19 '18 at 00:57

score 0 · Answer 2 · answered Jan 19 '18 at 08:48

0

Try using the trim() function with the date.

In the CREATE_ENTRY_TABLE variable use :

String CREATE_ENTRY_TABLE = "CREATE TABLE '" + TABLE_DATE + "' (...);";

Notice the single inverted commas before and after the table name.

answered Jan 19 '18 at 08:48

Aditya Pattani

75
2
14

Android SQLite CREATE TABLE not working

2 Answers2