sqlite DATETIME Mobile
var db = Titanium.Database.open('mydb');
db.execute('CREATE TABLE IF NOT EXISTS DATABASETEST (ID INTEGER, date_added DATETIME)');
db.execute('DELETE FROM DATABASETEST');
db.execute("INSERT INTO DATABASETEST (ID, date_added ) VALUES (?,DATETIME('NOW'))",1);
I cannot set the datetime stamp, i get the error:
[ERROR] Script Error = invalid SQL statement. Error Domain=com.plausiblelabs.pldatabase Code=3 UserInfo=0x5229c00 "An error occured parsing the provided SQL statement." in -[TiDatabaseProxy execute:] (TiDatabaseProxy.m:132) at main_list.js (line 1).
4 Answers
-
No, DATETIME actually is valid. Actually, you don't even need to pass the current date/time parameter to your execute statement if you design your database well. Try this:
CREATE TABLE DATABASETEST (ID INTEGER, date_added DATETIME DEFAULT CURRENT_TIMESTAMP)Now you can leave out the date_added parameter and every inserted row will have the current time automatically.
insert into DATABASETEST (id) values (1);
-
The problem is that you are trying to define a column of type DATETIME, which is not a supported data type in SQLite. See http://www.sqlite.org/datatype3.html for a discussion about SQLite data types and how dates work with them.
-
Try using CURRENT_TIMESTAMP, which is built into Sqlite, instead of DATETIME('NOW').
db.execute("INSERT INTO DATABASETEST (ID, date_added ) VALUES (?,CURRENT_TIMESTAMP)",1); -
Mark Burggraf,
Do you have a reference for DATETIME being a valid column type? The URL I indicated does not list it as such.
I have code very much like the question that uses TEXT instead of DATETIME and the insert works exactly as Mark Pollard specified it (using datetime('now')).