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

Inserting a QDate into an Oracle DB with a prepared statements fails for timezones like CET

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • P3: Somewhat important
    • 5.15.13, 6.2.8, 6.5.0, 6.6.0
    • 5.15.4, 6.5
    • SQL Support
    • None
    • SLES 15 SP 4 (linux, 32 bit)
    • Linux/X11
    • 6b9977c4a (dev), 9408b03c7 (tqtc/lts-6.2), afc8177e9 (tqtc/lts-5.15), 965c8da45 (6.4), fb004f0f4 (6.5), 79b22bb1f (dev), 42cbf42a7 (6.5)

    Description

      --Create a trivial table in Oracle

      create table cdtest (mydatum date); 

       

      int main(int argc, char **argv)
      {
          QCoreApplication app(argc, argv);
         
          auto db = QSqlDatabase::addDatabase(QStringLiteral("QOCI"), QStringLiteral("DateTest"));
      
          db.setDatabaseName(QStringLiteral("..."));
          db.setUserName(QStringLiteral("user"));
          db.setPassword(QStringLiteral("pwd"));
      
          QDate datum(2033,1,1);
          QDateTime dt(datum);
      
          qDebug() << "timeZone=" << dt.timeZone().displayName(dt, QTimeZone::OffsetName);
      
          QSqlQuery q(db);
          q.prepare(QStringLiteral("insert into CDTEST MYDATUM) values (:mydatum)"));
          q.bindValue(QStringLiteral(":mydatum"), datum);
          qDebug() << "exec: " << q.exec();
      
          return 0;
      }
      

      The call to q.exec() returns true.
      However, sqlplus prints the inserted date as 0000-00-00.

      The error is in qtbase/src/plugins/sqldrivers/oci/qsql_oci.cpp

      // Zone in +hh:mm format (stripping UTC prefix from OffsetName)
      QString timeZone = dt.timeZone().displayName(dt,  QTimeZone::OffsetName).mid(3);

      The code works if the timeZone is something like "UTC+01:00".
      The assumption that timeZone always is of the form "UTC...." is wrong, unfortunately.

      But if timeZone is "CET", then timeZone is an empty string.
      OCIDateTimeConstruct fails with OCI_ERROR.
      (But there is no error handling in the OCI plugin - at least not at this place.)

       

      If I pass the nullptr instead of an empty string, the call to OCIDateTimeConstruct succeeds und sqlplus show 2023-01-01.

      Attachments

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          People

            chehrlic Christian Ehrlicher
            caduel74 Christoph Duelli
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: