updating DATETIME causes sqlite error on BB10

Question

I created my table with this query

CREATE TABLE SETTINGS(NAME VARCHAR(1050), VALUE VARCHAR(1550),CREATE_DATE_TIME DATETIME,UPDATE_DATE_TIME DATETIME, PRIMARY KEY(NAME))

Then I inserted data like this

INSERT INTO SETTINGS(NAME, VALUE ,CREATE_DATE_TIME ,UPDATE_DATE_TIME) VALUES('CellIDKey','[email protected]',DATETIME('NOW'), DATETIME('NOW')) 

At this point it works fine. Now if I want to run an update query like this,

UPDATE SETTINGS SET VALUE='[email protected]' CREATE_DATE_TIME=DATETIME('NOW')  WHERE NAME='CellIDKey'

It shows the following error on console

QSqlError::type= "QSqlError::ConnectionError" , QSqlError::number= -1 , databaseText= "No query" , driverText= "Unable to fetch row" 

But if I run this update query like this,

UPDATE SETTINGS SET VALUE='[email protected]' WHERE NAME='CellIDKey'

Now it works fine. I don't know what is wrong with the DATETIME('NOW') statement on update query.

Answer 1

This is not valid SQL:

UPDATE SETTINGS SET VALUE='[email protected]' CREATE_DATE_TIME=DATETIME('NOW')  WHERE NAME='CellIDKey'
-- ---------------------------------------------------^ Missing comma!

The individual assignments in a SET need to be separated by commas like this:

UPDATE SETTINGS
SET VALUE='[email protected]', -- This comma is needed
    CREATE_DATE_TIME=DATETIME('NOW')
WHERE NAME='CellIDKey'