Uploaded image for project: 'Qt'
  1. Qt
  2. QTBUG-59524

QSqlQuery::addbindValue()/QSqlQuery::bindValue() fail on PSQL driver, when they are supplied QDateTime objects

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • P3: Somewhat important
    • 5.6.3, 5.9.0 Beta 2
    • 5.4.0 Alpha
    • SQL Support
    • None
    • Any operating system with a locale that does not use arabic numerals.
    • 0f03296391b58edbaa7e779f580adc3075aff831, ac74abdf50f7047cf43b3577a70d0995e197659d

    Description

      After adding timezone support for datetime fields in PSQL driver, the function formatValue is using the function QDateTime::toString(QString format) to convert the provided QDateTime value to a string.

      Since QDateTime::toString(QString format) function currently uses system's locale to do string conversion. PostgreSQL fails to read the serialized value when the system's locale is set to use any numerals other than arabic numerals.

      In summary, this causes QSqlQuery::addBindValue() and QSqlQuery::bindValue() to fail when they are passed a QDateTime object on systems having locales with non arabic numerals.

      Here is an example that demonstrates the issue:

      #include <QtCore>
      #include <QtSql>
      
      int main(int argc, char* argv[]){
          QCoreApplication a(argc, argv);
          Q_UNUSED(a)
      
          QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
          db.setHostName("127.0.0.1");
          db.setPort(5432);
          db.setDatabaseName("Test"); //This is an empty database
          db.setUserName("postgres");
          db.setPassword("123456");
          if(!db.open()) return 1;
      
          QSqlQuery query;
          //create a table to hold timestamp values
          if(!query.exec("CREATE TABLE IF NOT EXISTS t ( timestamp_col TIMESTAMP WITH TIME ZONE );"))
              return 2;
          //try to insert an arbitrary QDateTime to the previous table
          query.prepare("INSERT INTO t (timestamp_col) VALUES ( ? );");
          query.addBindValue(QDateTime::currentDateTime());
          //when setting system's locale to one that doesn't use arabic numerals,
          //the next exec() call fails.
          if(!query.exec()){
              qCritical() << "Database Insert Error: ";
              qCritical().noquote() << query.lastError().databaseText();
              return 3;
          }
      
          return 0;
      }
      

      The example results in the following error:

      Starting C:\Users\PC\Documents\build-psql_mcve-Desktop_Qt_5_8_0_MinGW_32bit-Debug\debug\psql_mcve.exe...
      Database Insert Error: 
      ERROR:  invalid input syntax for type timestamp with time zone: "????-??-??T??:??:??.???Z"
      LINE 1: EXECUTE qpsqlpstmt_1 (TIMESTAMP WITH TIME ZONE '????-??-??T?...
                                                             ^
      (22007)
      C:\Users\PC\Documents\build-psql_mcve-Desktop_Qt_5_8_0_MinGW_32bit-Debug\debug\psql_mcve.exe exited with code 3
      

      P.S. It seems that this will get fixed automatically in Qt 6 (when the function QDateTime::toString(QString format) is changed to use C locale instead of the system's locale). But It would be better to get that fixed in Qt5 releases (especially Qt5.6 LTS).

      Attachments

        Activity

          People

            micjabbour Michael Jabbour
            micjabbour Michael Jabbour
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: