Problems with SQLite database, error when creating

Question

i got this error when i start my app:

12-20 22:27:01.447: ERROR/Database(716): Failure 1 (near ":00": syntax error) on 0x1a4338 when preparing 'CREATE TABLE permission ( fk_email1 varchar(100) NOT NULL, fk_email2 varchar(100) NOT NULL, validated tinyint(4) default 0, hour1 time default 08:00:00, hour2 time default 20:00:00, date1 date default NULL, date2 date default NULL, weekend tinyint(4) default 0, fk_type varchar(45) default NULL, PRIMARY KEY (fk_email1,fk_email2))'.

here is the code where i am creating the database:

private static final String PERMISSION_TABLE_CREATE = 
"CREATE TABLE permission (" 
"fk_email1 varchar(100) NOT NULL, fk_email2 varchar(100) NOT NULL, "
"validated tinyint(4) default 0, hour1 time default 08:00:00, "
"hour2 time default 20:00:00, date1 date default NULL, "
"date2 date default NULL, weekend tinyint(4) default 0, "
"fk_type varchar(45) default NULL, PRIMARY KEY  (fk_email1,fk_email2))";

private static final String USER_TABLE_CREATE = "CREATE TABLE user ( "
"email varchar(100) NOT NULL, password varchar(45) default NULL, "
"fullName varchar(80) default NULL, "
"mobilePhone varchar(14) default NULL, "
"mobileOperatingSystem varchar(20) default NULL, PRIMARY KEY  (email))";

what i am doing bad?

Larry Lustig · Accepted Answer · 2010-12-20 22:46:26Z

2

Check the SQLLite documentation. I don't believe it has a type affinity for the "TIME" datatype. If it does, the defaults will probably have to be in quotes.

SQLite is very funny about datatypes (essentially, all data is untyped) and you can substantially shorten your DDL by getting rid of VARCHAR lengths (or even VARCHAR in favor of TEXT).

edited Dec 20, 2010 at 22:46

answered Dec 20, 2010 at 22:40

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kzotin · Accepted Answer · 2010-12-20 22:43:57Z

1

Obviously definition of your time row is wrong

Please check SQLite docs

You can try "YYYY-MM-DD HH:MM:SS.SSS" format, but better store value as an INTEGER (Unix Time)

answered Dec 20, 2010 at 22:43

kzotin

5,3553 gold badges32 silver badges36 bronze badges

Comments

Vit Khudenko · Accepted Answer · 2010-12-20 22:44:43Z

1

http://www.sqlite.org/faq.html#q2

(2) What datatypes does SQLite support?

SQLite uses dynamic typing. Content can be stored as INTEGER, REAL, TEXT, BLOB, or as NULL.

Use INTEGER, for instance, it can store Java's long.

answered Dec 20, 2010 at 22:44

Vit Khudenko

28.4k10 gold badges66 silver badges96 bronze badges

Collectives™ on Stack Overflow

Problems with SQLite database, error when creating

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related