Syntax error when creating table in SQLite

Question

I get a Syntax error when my app tries to create a sqlite table.

this is the code that creates the table:

@Override
public void onCreate(SQLiteDatabase db) {
    String SQL = pictureTable();
    db.execSQL(SQL);
}

private String pictureTable() {
    return "CREATE TABLE geophoto_db_pictures ( picid integer,"
            + "name character varying(50),"
            + "city character varying(20) NOT NULL DEFAULT 'Unknown',"
            + "zipcode character varying(20) NOT NULL DEFAULT 'Unknown',"
            + "country character varying(20) NOT NULL DEFAULT 'Unknown',"
            + "picdate datetime NOT NULL DEFAULT DATETIME('now'),"
            + "tags character varying(200)," + "image BLOB NOT NULL,"
            + "uploaded integer NOT NULL DEFAULT 0, PRIMARY KEY (picid))";
}

The error is: android.database.sqlite.SQLiteException: near "(":sytax error (code 1)

Any help is greatly appreciated !

error stays the same after changing it to "varying character" :( — ThomasS, Jan 04 '15 at 12:57
Your picdate default value setting is wrong. [sqlite database default time value 'now'][1] [1]: http://stackoverflow.com/questions/200309/sqlite-database-default-time-value-now — ZakiMak, Jan 04 '15 at 13:00

score 2 · Accepted Answer · answered Jan 04 '15 at 13:00

2

Default values cannot use function calls.

However, you can use the `CURRENT_TIMESTAMP' variable:

CREATE TABLE
    ...
    picdate datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ...

answered Jan 04 '15 at 13:00

CL.

173,858
17
217
259

Syntax error when creating table in SQLite

1 Answers1