From 190f64aab3fc8bb8e325bf48326c7b09d62b6419 Mon Sep 17 00:00:00 2001 From: Julien Blanc Date: Tue, 18 Mar 2014 11:58:49 +0100 Subject: Added timezone support for datetime fields in PSQL This patch adds correct timezone support in PSQL plugin. Prior to this patch, no timezone support was provided, so only the following case worked : * using local time in both client application and postgresql server * datetime were using second precision This patch tries to take care that postgresql has two different datatypes for date time, respectively : * timestamp with time zone * timestamp without time zone Both are internally stored as UTC values, but are not parsed the same. * timestamp with time zone assumes that there is a time zone information and will parse date time accordingly, and then, convert into UTC before storing them * timestamp without time zone assumes that there is no time zone information and will silently ignore any, unless the datetime is explicitly specified as having a time zone, in case it will convert it into UTC before storing it Both are retrieved as local time values, with the following difference * timestamp with time zone includes the timezone information (2014-02-12 10:20:12+0100 for example) * timestamp without time zone does not include it The patch does the following : * parse the date retrieved by postgresql server using QDateTime functions, which work correctly * always convert the date to UTC before giving it to postgresql * force time zone so that timezone information is taken into account by postgresql * also adds the milliseconds when storing QDateTime values The following configurations are tested to work : * client and server using same timezone, timestamp with or without tz * client and server using different timezone, timestamp with tz The following configuration will *not* work : * client and server using different timezones, timestamp without tz Because data will be converted to local time by the postgresql server, so when returned it will be different from what had been serialized. Prior to this patch, it gave the illusion to work because since TZ information was lost, time was stored as local time from postgresql. Lots of inconsistencies occurred, though, in case client tz changes... I don't expect this to be an issue since having different TZ in server and client and *not* handling this is a broken setup anyway. Almost based on changes proposed by julien.blanc@nmc-company.fr [ChangeLog][QtSql] Added timezone support for datetime fields in PSQL Task-number: QTBUG-36211 Change-Id: I5650a5ef60cb3f14f0ab619825612831c7e90c12 Reviewed-by: Mark Brand --- src/sql/drivers/psql/qsql_psql.cpp | 43 +++++++++++++++----------------------- 1 file changed, 17 insertions(+), 26 deletions(-) (limited to 'src/sql') diff --git a/src/sql/drivers/psql/qsql_psql.cpp b/src/sql/drivers/psql/qsql_psql.cpp index e76dcc26a0..4268ea06f6 100644 --- a/src/sql/drivers/psql/qsql_psql.cpp +++ b/src/sql/drivers/psql/qsql_psql.cpp @@ -426,11 +426,8 @@ QVariant QPSQLResult::data(int i) #ifndef QT_NO_DATESTRING if (str.isEmpty()) return QVariant(QTime()); - if (str.at(str.length() - 3) == QLatin1Char('+') || str.at(str.length() - 3) == QLatin1Char('-')) - // strip the timezone - // TODO: fix this when timestamp support comes into QDateTime - return QVariant(QTime::fromString(str.left(str.length() - 3), Qt::ISODate)); - return QVariant(QTime::fromString(str, Qt::ISODate)); + else + return QVariant(QTime::fromString(str, Qt::ISODate)); #else return QVariant(str); #endif @@ -438,19 +435,13 @@ QVariant QPSQLResult::data(int i) case QVariant::DateTime: { QString dtval = QString::fromLatin1(val); #ifndef QT_NO_DATESTRING - if (dtval.length() < 10) - return QVariant(QDateTime()); - // remove the timezone - // TODO: fix this when timestamp support comes into QDateTime - if (dtval.at(dtval.length() - 3) == QLatin1Char('+') || dtval.at(dtval.length() - 3) == QLatin1Char('-')) - dtval.chop(3); - // milliseconds are sometimes returned with 2 digits only - if (dtval.at(dtval.length() - 3).isPunct()) - dtval += QLatin1Char('0'); - if (dtval.isEmpty()) + if (dtval.length() < 10) { return QVariant(QDateTime()); - else - return QVariant(QDateTime::fromString(dtval, Qt::ISODate)); + } else { + QChar sign = dtval[dtval.size() - 3]; + if (sign == QLatin1Char('-') || sign == QLatin1Char('+')) dtval += QLatin1String(":00"); + return QVariant(QDateTime::fromString(dtval, Qt::ISODate).toLocalTime()); + } #else return QVariant(dtval); #endif @@ -536,6 +527,11 @@ QSqlRecord QPSQLResult::record() const int precision = PQfmod(d->result, i); switch (ptype) { + case QTIMESTAMPOID: + case QTIMESTAMPTZOID: + precision = 3; + break; + case QNUMERICOID: if (precision != -1) { len = (precision >> 16); @@ -1263,15 +1259,10 @@ QString QPSQLDriver::formatValue(const QSqlField &field, bool trimStrings) const case QVariant::DateTime: #ifndef QT_NO_DATESTRING if (field.value().toDateTime().isValid()) { - QDate dt = field.value().toDateTime().date(); - QTime tm = field.value().toDateTime().time(); - // msecs need to be right aligned otherwise psql interprets them wrong - r = QLatin1Char('\'') + QString::number(dt.year()) + QLatin1Char('-') - + QString::number(dt.month()).rightJustified(2, QLatin1Char('0')) + QLatin1Char('-') - + QString::number(dt.day()).rightJustified(2, QLatin1Char('0')) + QLatin1Char(' ') - + tm.toString() + QLatin1Char('.') - + QString::number(tm.msec()).rightJustified(3, QLatin1Char('0')) - + QLatin1Char('\''); + // we force the value to be considered with a timezone information, and we force it to be UTC + // this is safe since postgresql stores only the UTC value and not the timezone offset (only used + // while parsing), so we have correct behavior in both case of with timezone and without tz + r = QLatin1String("TIMESTAMP WITH TIME ZONE ") + QLatin1Char('\'') + field.value().toDateTime().toUTC().toString(QLatin1String("yyyy-MM-ddThh:mm:ss.zzz")) + QLatin1Char('Z') + QLatin1Char('\''); } else { r = QLatin1String("NULL"); } -- cgit v1.2.3 From a44749855e381a6f856b7b89ecd42aadcc2797bf Mon Sep 17 00:00:00 2001 From: Maximilian Hrabowski Date: Sat, 11 Oct 2014 13:44:28 +0200 Subject: QODBC: fix converted string values empty SQLServer 2012 SQL Server 2012 Native Client (version 11.0.2100.60) or later introduced a change in the behavior of the SQLGetData method when converted string values are involved. In older version a (sometimes wrong) size was returned. Now always SQL_NO_TOTAL is returned which signals to read as much data as available. SQL_NO_TOTAL was handled like SQL_NULL_DATA in the code before which indicates a NULL value so the returned string was empty. See link for more info: http://msdn.microsoft.com/en-us/library/jj219209.aspx Change-Id: Ia0d2296caf593890b301ee1848d1bf3eb8d7b6fe Reviewed-by: Mark Brand --- src/sql/drivers/odbc/qsql_odbc.cpp | 12 +++++++++++- 1 file changed, 11 insertions(+), 1 deletion(-) (limited to 'src/sql') diff --git a/src/sql/drivers/odbc/qsql_odbc.cpp b/src/sql/drivers/odbc/qsql_odbc.cpp index f95fb8868e..2b14809943 100644 --- a/src/sql/drivers/odbc/qsql_odbc.cpp +++ b/src/sql/drivers/odbc/qsql_odbc.cpp @@ -373,10 +373,20 @@ static QString qGetStringData(SQLHANDLE hStmt, int column, int colSize, bool uni colSize*sizeof(SQLTCHAR), &lengthIndicator); if (r == SQL_SUCCESS || r == SQL_SUCCESS_WITH_INFO) { - if (lengthIndicator == SQL_NULL_DATA || lengthIndicator == SQL_NO_TOTAL) { + if (lengthIndicator == SQL_NULL_DATA) { fieldVal.clear(); break; } + // starting with ODBC Native Client 2012, SQL_NO_TOTAL is returned + // instead of the length (which sometimes was wrong in older versions) + // see link for more info: http://msdn.microsoft.com/en-us/library/jj219209.aspx + // if length indicator equals SQL_NO_TOTAL, indicating that + // more data can be fetched, but size not known, collect data + // and fetch next block + if (lengthIndicator == SQL_NO_TOTAL) { + fieldVal += fromSQLTCHAR(buf, colSize); + continue; + } // if SQL_SUCCESS_WITH_INFO is returned, indicating that // more data can be fetched, the length indicator does NOT // contain the number of bytes returned - it contains the -- cgit v1.2.3 From c28718b88b0cfc712a5177f04475813045c45119 Mon Sep 17 00:00:00 2001 From: Eric Lemanissier Date: Fri, 16 Jan 2015 11:44:59 +0100 Subject: Correction on bound values in case of repeated QSqlQuery::execBatch Until now, QSqlQuery::execBatch did not call resetBindCount, which lead the next call to QSqlQuery::addBindValue to start at non zero index. This is problematic in case of a prepared query which is called several times. Task-number: QTBUG-43874 Change-Id: I1a0f46e39b74d9538009967fd98a269e05aac6f2 Reviewed-by: Mark Brand --- src/sql/kernel/qsqlquery.cpp | 1 + 1 file changed, 1 insertion(+) (limited to 'src/sql') diff --git a/src/sql/kernel/qsqlquery.cpp b/src/sql/kernel/qsqlquery.cpp index 98e262a7e2..2808587d96 100644 --- a/src/sql/kernel/qsqlquery.cpp +++ b/src/sql/kernel/qsqlquery.cpp @@ -1060,6 +1060,7 @@ bool QSqlQuery::exec() */ bool QSqlQuery::execBatch(BatchExecutionMode mode) { + d->sqlResult->resetBindCount(); return d->sqlResult->execBatch(mode == ValuesAsColumns); } -- cgit v1.2.3