Solved QDateTime binding problem in QSqlQuery+QMYSQL on Qt4 vs Qt5
-
The following piece of code works as expected in Qt4, but not in Qt5:
#include <QtSql> int main(int argc, char *argv[]) { QCoreApplication app(argc, argv); QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL"); db.setHostName("localhost"); db.setDatabaseName("test"); db.setUserName("root"); db.setPassword("XXXXXXXXX"); Q_ASSERT( db.open() ); QSqlQuery query; Q_ASSERT( query.exec("CREATE TABLE test ( datetime datetime )") ); Q_ASSERT( query.exec("INSERT INTO test VALUES ( NOW() )") ); Q_ASSERT( query.exec("SELECT * FROM test WHERE datetime != '1990/1/1 0:0:0'") ); qDebug() << query.size(); query.clear(); Q_ASSERT( query.prepare("SELECT * FROM test WHERE datetime != :start") ); query.bindValue("start", "1990/1/1 0:0:0"); Q_ASSERT( query.exec() ); qDebug() << query.size(); query.clear(); Q_ASSERT( query.prepare("SELECT * FROM test WHERE datetime != :start") ); query.bindValue("start", QDateTime(QDate(1990,1,1), QTime(0,0,0))); Q_ASSERT( query.exec() ); qDebug() << query.size(); query.clear(); Q_ASSERT( query.exec("DROP TABLE test") ); return 0; }
The code just
- Creates a table
- Inserts one row
- Tries to retrieve that row comparing its content with a date/time obviously different to the one the row contains, in three ways:
- Comparing the date/time with an inline string
- Comparing the date/time with a bound string
- Comparing the date/time with a bound QDateTime
Compiling and running for Qt4:
20:43:37 ~$ g++ -I/usr/include/qt4 -I/usr/include/qt4/QtCore -I/usr/include/qt4/QtSql -lQtCore -lQtSql sql.cpp && ./a.out 1 1 1
Compiling and running for Qt5:
20:44:29 ~$ g++ -I/usr/include/qt -I/usr/include/qt/QtCore -I/usr/include/qt/QtSql -lQt5Core -lQt5Sql sql.cpp && ./a.out 1 0 0
For some reason, every time I compare a date/time with a bound parameter, the query returns no results.
Is this an error in the QMYSQL driver that somehow went past Qt's test suites? Is this PEBCAK?
For the record:
- System: Archlinux
- Qt4 version: 4.8.7
- Qt5 version: 5.5.1
- MariaDB version: 10.0.22
-
More info: these two variations work well both in Qt4 and Qt5
Q_ASSERT( query.prepare("SELECT * FROM test WHERE datetime != :start") ); query.bindValue(0, QDateTime(QDate(1990,1,1), QTime(0,0,0))); Q_ASSERT( query.exec() ); qDebug() << query.size(); query.clear(); Q_ASSERT( query.prepare("SELECT * FROM test WHERE datetime != ?") ); query.addBindValue(QDateTime(QDate(1990,1,1), QTime(0,0,0))); Q_ASSERT( query.exec() ); qDebug() << query.size(); query.clear();
-
Okay, found the problem: this
query3.bindValue("start", "1990/1/1 0:0:0");
needs to be changed to
query3.bindValue(":start", "1990/1/1 0:0:0");
That works in both Qt4 and Qt5. This is actually consistent with the documentacion of both Qt4 and Qt5
http://doc.qt.io/qt-4.8/qsqlquery.html#bindValue
http://doc.qt.io/qt-5/qsqlquery.html#bindValueNote that the placeholder mark (e.g :) must be included when specifying the placeholder name.
But looks like Qt5 enforces the restriction while Qt4 doesn't.