Sqlite_Error - SQL Error or Missing Database (Near "Autoincrement": Syntax Error)

SQLITE_ERROR - SQL error or missing database (near "AUTOINCREMENT": syntax error)

You can use the AUTOINCREMENT keyword only with INTEGER PRIMARY KEY, not INT NOT NULL.

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "as": syntax error)

@Column(name = "observaciones,", length = 100, nullable = true, unique = false) you have a comma at the end of the name that is wreaking havoc with the SQL. Remove the comma! Same for fecha,.

Error in near 'Autoincrement'

According to SQLite FAQ you have to declare either a INTEGER PRIMARY KEY or INTEGER PRIMARY KEY AUTOINCREMENT column to achieve that.

Error while using autoincrement keyword in sqlite

Try this:

//Create Table
private static final String CREATE_SIMPLENOTE = "create table calSimpleNote (_id integer primary key autoincrement, Cal_Id TEXT , User_ID INTEGER , Cal_Date TEXT , Note TEXT,Involvers TEXT, "+
                                                "DeleteReason TEXT ,Create_Date TEXT ,Changed_date TEXT,EmailInvolvers TEXT, "+ 
                                                "RemindSameDay INTEGER, RemindAllDay INTEGER,RemindBeforeDays INTEGER );";

& this:

public long insertCalSimpleNote(String noteId,int userId,String cal_date,String note,String Involved,String DeleteReason,String Create_Date, String Changed_date,String emailInvolvers,int remindSameday,int remindAllday,int remindBeforeday){

    ContentValues initialValues = new ContentValues();


    initialValues.put("Cal_Id", noteId);
    initialValues.put("User_ID", userId);
    initialValues.put("Cal_Date", cal_date);
    initialValues.put("Note", note);
    initialValues.put("Involvers", Involved);
    initialValues.put("DeleteReason", DeleteReason);
    initialValues.put("Create_Date", Create_Date);
    initialValues.put("Changed_date", Changed_date);
    initialValues.put("EmailInvolvers", emailInvolvers);
    initialValues.put("RemindSameDay", remindSameday);
    initialValues.put("RemindAllDay", remindAllday);
    initialValues.put("RemindBeforeDays", remindBeforeday);

    return db.insert("calSimpleNote", null, initialValues);
}

Syntax error near AutoIncrement with SQLite Database creation

Nothing stupid about your code; matches the code samples on https://github.com/praeclarum/sqlite-net alright. But apparently the code samples are wrong, considering this similar problem:

Android table creation Failure (near "autoincrement": syntax error)?

The problem was solved by removing AutoIncrement. Or to quote http://www.sqlite.org/faq.html#q1 :

Short answer: A column declared INTEGER PRIMARY KEY will autoincrement.

(Please double-check whether column ID actually has type INTEGER PRIMARY KEY once the table has been created.)

Longer answer: If you declare a column of a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty.

Make sure your INSERT statements do not contain an explicit value (other than NULL) for column ID, otherwise the column will not auto-increment. If that is not possible in SQLite-net (you may need a debugger here), then that may well be a bug. Though it would be surprising that nobody else has ever ran into this.

Maybe you need to make property ID nullable (i.e. use type int?; yes, with the question mark). Mind you, I'm only guessing here; you may need to experiment a bit.

Database and table not created on android

AUTOINCREMENT can only be used as part of the column type designation and must immediately follow INTEGER PRIMARY KEY.

You would have had an error along the lines of :-

08-18 14:23:18.865 12008-12008/? E/AndroidRuntime: FATAL EXCEPTION: main
                                                   Process: mjt.so45722118, PID: 12008
                                                   java.lang.RuntimeException: Unable to start activity ComponentInfo{mjt.so45722118/mjt.so45722118.MainActivity}: android.database.sqlite.SQLiteException: near "AUTOINCREMENT": syntax error (code 1): , while compiling: CREATE TABLE user_account(id INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT, full_name TEXT NOT NULL, address TEXT NOT NULL, contact TEXT NOT NULL, password TEXT NOT NULL)
                                                       at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2325)
                                                       at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2387)
                                                       at android.app.ActivityThread.access$800(ActivityThread.java:151)
                                                       at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303)
                                                       at android.os.Handler.dispatchMessage(Handler.java:102)
                                                       at android.os.Looper.loop(Looper.java:135)
                                                       at android.app.ActivityThread.main(ActivityThread.java:5254)
                                                       at java.lang.reflect.Method.invoke(Native Method)
                                                       at java.lang.reflect.Method.invoke(Method.java:372)
                                                       at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
                                                       at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)
                                                    Caused by: android.database.sqlite.SQLiteException: near "AUTOINCREMENT": syntax error (code 1): , while compiling: CREATE TABLE user_account(id INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT, full_name TEXT NOT NULL, address TEXT NOT NULL, contact TEXT NOT NULL, password TEXT NOT NULL)
                                                       at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                                                       at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
                                                       at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
                                                       at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
                                                       at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
                                                       at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
                                                       at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1674)
                                                       at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1605)
                                                       at mjt.so45722118.DatabaseHelper.onCreate(DatabaseHelper.java:38)
                                                       at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:251)
                                                       at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:163)
                                                       at mjt.so45722118.DatabaseHelper.<init>(DatabaseHelper.java:24)
                                                       at mjt.so45722118.MainActivity.onCreate(MainActivity.java:47)
                                                       at android.app.Activity.performCreate(Activity.java:5990)
                                                       at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1106)
                                                       at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2278)
                                                       at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2387) 
                                                       at android.app.ActivityThread.access$800(ActivityThread.java:151) 
                                                       at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303) 
                                                       at android.os.Handler.dispatchMessage(Handler.java:102) 
                                                       at android.os.Looper.loop(Looper.java:135) 
                                                       at android.app.ActivityThread.main(ActivityThread.java:5254) 
                                                       at java.lang.reflect.Method.invoke(Native Method) 
                                                       at java.lang.reflect.Method.invoke(Method.java:372) 
                                                       at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903) 
                                                       at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698) 

As such you need to change :-

+COL_1+" INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT, "

to

+COL_1+" INTEGER PRIMARY KEY AUTOINCREMENT, "

You could even not code AUTOINCREMENT (probably not, as per, The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed. SQLite Autoincrement).

INTEGER PRIMARY KEY implies NOT NULL so that is not needed.

So I'd suggest just coding :-

+COL_1+" INTEGER PRIMARY KEY, "

You could add the following lines to MainActivity, after database = new DatabaseHelper(this);:-

        Cursor csr = database.getWritableDatabase().rawQuery("PRAGMA TABLE_INFO(" + DatabaseHelper.TABLE_NAME + ")",null)
        String rowinfo;
        while (csr.moveToNext()) {
            rowinfo = "Row position=" + Integer.toString(csr.getPosition());
            for (int i=0;i < csr.getColumnCount();i++) {
                rowinfo = rowinfo + " Column=" + csr.getColumnName(i) +
                        " Value=" + csr.getString(i);
            }
            Log.d("TABLEINFO",rowinfo);
        }
        csr.close();

This would then display the following in the log to show you that the Database and table exists:-

08-18 14:09:27.053 7905-7905/? D/TABLEINFO: Row position=0 Column=cid Value=0 Column=name Value=id Column=type Value=INTEGER Column=notnull Value=0 Column=dflt_value Value=null Column=pk Value=1
08-18 14:09:27.053 7905-7905/? D/TABLEINFO: Row position=1 Column=cid Value=1 Column=name Value=full_name Column=type Value=TEXT Column=notnull Value=1 Column=dflt_value Value=null Column=pk Value=0
08-18 14:09:27.053 7905-7905/? D/TABLEINFO: Row position=2 Column=cid Value=2 Column=name Value=address Column=type Value=TEXT Column=notnull Value=1 Column=dflt_value Value=null Column=pk Value=0
08-18 14:09:27.053 7905-7905/? D/TABLEINFO: Row position=3 Column=cid Value=3 Column=name Value=contact Column=type Value=TEXT Column=notnull Value=1 Column=dflt_value Value=null Column=pk Value=0
08-18 14:09:27.053 7905-7905/? D/TABLEINFO: Row position=4 Column=cid Value=4 Column=name Value=password Column=type Value=TEXT Column=notnull Value=1 Column=dflt_value Value=null Column=pk Value=0

---

Related Topics