Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

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

    1. Creates a table
    2. Inserts one row
    3. 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#bindValue

    Note 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.


Log in to reply