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
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.