QSqlQuery UPDATE/INSERT DateTime with server's time (eg CURRENT_TIMESTAMP)

Question

I am using QSqlQuery to insert data into a MySQL database. Currently all I care about is getting this to work with MySQL, but ideally I'd like to keep this as platform-independent as possible.

What I'm after, in the context of MySQL, is to end up with code that effectively executes something like the following query:

UPDATE table SET time_field=CURRENT_TIMESTAMP() WHERE id='5'

The following code is what I have attempted, but it fails:

QSqlQuery query;
query.prepare("INSERT INTO table SET time_field=? WHERE id=?");
query.addBindValue("CURRENT_TIMESTAMP()");
query.addBindValue(5);
query.exec();

The error I get is: Incorrect datetime value: 'CURRENT_TIMESTAMP()' for column 'time_field' at row 1 QMYSQL3: Unable to execute statement. I am not surprised as I assume Qt is doing some type checking when it binds values.

I have dug through the Qt documentation as well as I know how, but I can't find anything in the API designed specifically for supporting MySQL's CURRENT_TIMESTAMP() function, or that of any other DBMS.

Any suggestions?

Answer 1

I don't see a way to get the database time command. I think it would be in QSqlDriver as it is specific to the database.

The only way I can think of :

QString timeCommand("CURRENT_TIMESTAMP()");
query.prepare(QString("INSERT INTO table SET time_field=%1 WHERE id=?").arg(timeCommand));

Edit : In fact, I'm not sure CURRENT_TIMESTAMP() is DB specific ...

Answer 2

I have no SQL server to test here, but addBindValue() binds Qt's data types. You should put the timestamp function direct into the query

QSqlQuery query;
query.prepare("INSERT INTO table SET time_field=CURRENT_TIMESTAMP() WHERE id=?");
query.addBindValue(5);
query.exec();

should do the trick.