summaryrefslogtreecommitdiffstats
path: root/src/plugins/sqldrivers/oci/qsql_oci.cpp
diff options
context:
space:
mode:
authorAndy Shaw <andy.shaw@qt.io>2017-09-06 01:40:24 +0200
committerAndy Shaw <andy.shaw@qt.io>2017-10-10 08:56:32 +0000
commit0900cf3581be3ff2b2e924ce0d845566c5df841e (patch)
tree6bb0606b3c1e7584ba010beff39f02ddfbed2da6 /src/plugins/sqldrivers/oci/qsql_oci.cpp
parentbbcc2a92d029ea344390466d278b3415d8b8a202 (diff)
OCI: Use TIMESTAMP for datetime data
The TIMESTAMP type has been available in Oracle since Oracle 9i which was released in June 2001 and contains more data than the DATE type so it can be reliably used for the related data types. This adds support for preserving milliseconds and the time zone information if this is passed or in the database. [ChangeLog][QtSql][OCI] Added support for the TIMESTAMP data type. Task-number: QTBUG-23 Change-Id: Icf7a012dda75fb342ce6c6aa34eaa2a52755ff2d Reviewed-by: Edward Welbourne <edward.welbourne@qt.io>
Diffstat (limited to 'src/plugins/sqldrivers/oci/qsql_oci.cpp')
-rw-r--r--src/plugins/sqldrivers/oci/qsql_oci.cpp207
1 files changed, 122 insertions, 85 deletions
diff --git a/src/plugins/sqldrivers/oci/qsql_oci.cpp b/src/plugins/sqldrivers/oci/qsql_oci.cpp
index b6c74b355d..de47df10ce 100644
--- a/src/plugins/sqldrivers/oci/qsql_oci.cpp
+++ b/src/plugins/sqldrivers/oci/qsql_oci.cpp
@@ -55,6 +55,7 @@
#include <qvarlengtharray.h>
#include <qvector.h>
#include <qdebug.h>
+#include <qtimezone.h>
// This is needed for oracle oci when compiling with mingw-w64 headers
#if defined(__MINGW64_VERSION_MAJOR) && defined(_WIN64)
@@ -112,9 +113,6 @@ static const ub2 qOraCharset = OCI_UCS2ID;
typedef QVarLengthArray<sb2, 32> IndicatorArray;
typedef QVarLengthArray<ub2, 32> SizeArray;
-static QByteArray qMakeOraDate(const QDateTime& dt);
-static QDateTime qMakeDate(const char* oraDate);
-
static QByteArray qMakeOCINumber(const qlonglong &ll, OCIError *err);
static QByteArray qMakeOCINumber(const qulonglong& ull, OCIError* err);
@@ -156,6 +154,60 @@ QOCIRowId::~QOCIRowId()
OCIDescriptorFree(id, OCI_DTYPE_ROWID);
}
+class QOCIDateTime
+{
+public:
+ QOCIDateTime(OCIEnv *env, OCIError *err, const QDateTime &dt = QDateTime());
+ ~QOCIDateTime();
+ OCIDateTime *dateTime;
+ static QDateTime fromOCIDateTime(OCIEnv *env, OCIError *err, OCIDateTime *dt);
+};
+
+QOCIDateTime::QOCIDateTime(OCIEnv *env, OCIError *err, const QDateTime &dt)
+ : dateTime(nullptr)
+{
+ OCIDescriptorAlloc(env, reinterpret_cast<void**>(&dateTime), OCI_DTYPE_TIMESTAMP_TZ, 0, 0);
+ if (dt.isValid()) {
+ const QDate date = dt.date();
+ const QTime time = dt.time();
+ // Zone in +hh:mm format (stripping UTC prefix from OffsetName)
+ QString timeZone = dt.timeZone().displayName(dt, QTimeZone::OffsetName).mid(3);
+ const OraText *tz = reinterpret_cast<const OraText *>(timeZone.utf16());
+ OCIDateTimeConstruct(env, err, dateTime, date.year(), date.month(), date.day(), time.hour(),
+ time.minute(), time.second(), time.msec() * 1000000,
+ const_cast<OraText *>(tz), timeZone.length() * sizeof(QChar));
+ }
+}
+
+QOCIDateTime::~QOCIDateTime()
+{
+ if (dateTime != nullptr)
+ OCIDescriptorFree(dateTime, OCI_DTYPE_TIMESTAMP_TZ);
+}
+
+QDateTime QOCIDateTime::fromOCIDateTime(OCIEnv *env, OCIError *err, OCIDateTime *dateTime)
+{
+ sb2 year;
+ ub1 month, day, hour, minute, second;
+ ub4 nsec;
+ sb1 tzHour, tzMinute;
+
+ OCIDateTimeGetDate(env, err, dateTime, &year, &month, &day);
+ OCIDateTimeGetTime(env, err, dateTime, &hour, &minute, &second, &nsec);
+ OCIDateTimeGetTimeZoneOffset(env, err, dateTime, &tzHour, &tzMinute);
+ int secondsOffset = (qAbs(tzHour) * 60 + tzMinute) * 60;
+ if (tzHour < 0)
+ secondsOffset = -secondsOffset;
+ // OCIDateTimeGetTime gives "fractions of second" as nanoseconds
+ return QDateTime(QDate(year, month, day), QTime(hour, minute, second, nsec / 1000000),
+ Qt::OffsetFromUTC, secondsOffset);
+}
+
+struct TempStorage {
+ QList<QByteArray> rawData;
+ QList<QOCIDateTime *> dateTimes;
+};
+
typedef QSharedDataPointer<QOCIRowId> QOCIRowIdPointer;
QT_BEGIN_INCLUDE_NAMESPACE
Q_DECLARE_METATYPE(QOCIRowIdPointer)
@@ -227,11 +279,11 @@ public:
void setStatementAttributes();
int bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, int pos,
- const QVariant &val, dvoid *indPtr, ub2 *tmpSize, QList<QByteArray> &tmpStorage);
+ const QVariant &val, dvoid *indPtr, ub2 *tmpSize, TempStorage &tmpStorage);
int bindValues(QVector<QVariant> &values, IndicatorArray &indicators, SizeArray &tmpSizes,
- QList<QByteArray> &tmpStorage);
+ TempStorage &tmpStorage);
void outValues(QVector<QVariant> &values, IndicatorArray &indicators,
- QList<QByteArray> &tmpStorage);
+ TempStorage &tmpStorage);
inline bool isOutValue(int i) const
{ Q_Q(const QOCIResult); return q->bindValueType(i) & QSql::Out; }
inline bool isBinaryValue(int i) const
@@ -304,7 +356,7 @@ void QOCIResultPrivate::setStatementAttributes()
}
int QOCIResultPrivate::bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, int pos,
- const QVariant &val, dvoid *indPtr, ub2 *tmpSize, QList<QByteArray> &tmpStorage)
+ const QVariant &val, dvoid *indPtr, ub2 *tmpSize, TempStorage &tmpStorage)
{
int r = OCI_SUCCESS;
void *data = const_cast<void *>(val.constData());
@@ -322,14 +374,15 @@ int QOCIResultPrivate::bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, in
case QVariant::Time:
case QVariant::Date:
case QVariant::DateTime: {
- QByteArray ba = qMakeOraDate(val.toDateTime());
+ QOCIDateTime *ptr = new QOCIDateTime(env, err, val.toDateTime());
r = OCIBindByPos(sql, hbnd, err,
pos + 1,
- ba.data(),
- ba.size(),
- SQLT_DAT, indPtr, 0, 0, 0, 0, OCI_DEFAULT);
- tmpStorage.append(ba);
- break; }
+ &ptr->dateTime,
+ sizeof(OCIDateTime *),
+ SQLT_TIMESTAMP_TZ, indPtr, 0, 0, 0, 0, OCI_DEFAULT);
+ tmpStorage.dateTimes.append(ptr);
+ break;
+ }
case QVariant::Int:
r = OCIBindByPos(sql, hbnd, err,
pos + 1,
@@ -356,7 +409,7 @@ int QOCIResultPrivate::bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, in
ba.data(),
ba.size(),
SQLT_VNU, indPtr, 0, 0, 0, 0, OCI_DEFAULT);
- tmpStorage.append(ba);
+ tmpStorage.rawData.append(ba);
break;
}
case QVariant::ULongLong:
@@ -367,7 +420,7 @@ int QOCIResultPrivate::bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, in
ba.data(),
ba.size(),
SQLT_VNU, indPtr, 0, 0, 0, 0, OCI_DEFAULT);
- tmpStorage.append(ba);
+ tmpStorage.rawData.append(ba);
break;
}
case QVariant::Double:
@@ -437,7 +490,7 @@ int QOCIResultPrivate::bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, in
}
if (r == OCI_SUCCESS)
setCharset(*hbnd, OCI_HTYPE_BIND);
- tmpStorage.append(ba);
+ tmpStorage.rawData.append(ba);
break;
} // default case
} // switch
@@ -447,7 +500,7 @@ int QOCIResultPrivate::bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, in
}
int QOCIResultPrivate::bindValues(QVector<QVariant> &values, IndicatorArray &indicators,
- SizeArray &tmpSizes, QList<QByteArray> &tmpStorage)
+ SizeArray &tmpSizes, TempStorage &tmpStorage)
{
int r = OCI_SUCCESS;
for (int i = 0; i < values.count(); ++i) {
@@ -465,27 +518,30 @@ int QOCIResultPrivate::bindValues(QVector<QVariant> &values, IndicatorArray &ind
}
// will assign out value and remove its temp storage.
-static void qOraOutValue(QVariant &value, QList<QByteArray> &storage, OCIError* err)
+static void qOraOutValue(QVariant &value, TempStorage &tmpStorage, OCIEnv *env, OCIError* err)
{
switch (value.type()) {
case QVariant::Time:
- value = qMakeDate(storage.takeFirst()).time();
+ value = QOCIDateTime::fromOCIDateTime(env, err,
+ tmpStorage.dateTimes.takeFirst()->dateTime).time();
break;
case QVariant::Date:
- value = qMakeDate(storage.takeFirst()).date();
+ value = QOCIDateTime::fromOCIDateTime(env, err,
+ tmpStorage.dateTimes.takeFirst()->dateTime).date();
break;
case QVariant::DateTime:
- value = qMakeDate(storage.takeFirst());
+ value = QOCIDateTime::fromOCIDateTime(env, err,
+ tmpStorage.dateTimes.takeFirst()->dateTime);
break;
case QVariant::LongLong:
- value = qMakeLongLong(storage.takeFirst(), err);
+ value = qMakeLongLong(tmpStorage.rawData.takeFirst(), err);
break;
case QVariant::ULongLong:
- value = qMakeULongLong(storage.takeFirst(), err);
+ value = qMakeULongLong(tmpStorage.rawData.takeFirst(), err);
break;
case QVariant::String:
value = QString(
- reinterpret_cast<const QChar *>(storage.takeFirst().constData()));
+ reinterpret_cast<const QChar *>(tmpStorage.rawData.takeFirst().constData()));
break;
default:
break; //nothing
@@ -493,14 +549,14 @@ static void qOraOutValue(QVariant &value, QList<QByteArray> &storage, OCIError*
}
void QOCIResultPrivate::outValues(QVector<QVariant> &values, IndicatorArray &indicators,
- QList<QByteArray> &tmpStorage)
+ TempStorage &tmpStorage)
{
for (int i = 0; i < values.count(); ++i) {
if (!isOutValue(i))
continue;
- qOraOutValue(values[i], tmpStorage, err);
+ qOraOutValue(values[i], tmpStorage, env, err);
QVariant::Type typ = values.at(i).type();
if (indicators[i] == -1) // NULL
@@ -692,11 +748,9 @@ QVariant::Type qDecodeOCIType(int ocitype, QSql::NumericalPrecisionPolicy precis
break;
case SQLT_DAT:
case SQLT_ODT:
-#ifdef SQLT_TIMESTAMP
case SQLT_TIMESTAMP:
case SQLT_TIMESTAMP_TZ:
case SQLT_TIMESTAMP_LTZ:
-#endif
type = QVariant::DateTime;
break;
default:
@@ -723,27 +777,6 @@ static QSqlField qFromOraInf(const OraFieldInfo &ofi)
}
/*!
- \internal
-
- Convert QDateTime to the internal Oracle DATE format NB!
- It does not handle BCE dates.
-*/
-QByteArray qMakeOraDate(const QDateTime& dt)
-{
- QByteArray ba;
- ba.resize(7);
- int year = dt.date().year();
- ba[0]= (year / 100) + 100; // century
- ba[1]= (year % 100) + 100; // year
- ba[2]= dt.date().month();
- ba[3]= dt.date().day();
- ba[4]= dt.time().hour() + 1;
- ba[5]= dt.time().minute() + 1;
- ba[6]= dt.time().second() + 1;
- return ba;
-}
-
-/*!
\internal
Convert qlonglong to the internal Oracle OCINumber format.
@@ -793,22 +826,6 @@ qulonglong qMakeULongLong(const char* ociNumber, OCIError* err)
return qull;
}
-QDateTime qMakeDate(const char* oraDate)
-{
- int century = uchar(oraDate[0]);
- if(century >= 100){
- int year = uchar(oraDate[1]);
- year = ((century-100)*100) + (year-100);
- int month = oraDate[2];
- int day = oraDate[3];
- int hour = oraDate[4] - 1;
- int min = oraDate[5] - 1;
- int sec = oraDate[6] - 1;
- return QDateTime(QDate(year,month,day), QTime(hour,min,sec));
- }
- return QDateTime();
-}
-
class QOCICols
{
public:
@@ -831,7 +848,7 @@ private:
class OraFieldInf
{
public:
- OraFieldInf(): data(0), len(0), ind(0), typ(QVariant::Invalid), oraType(0), def(0), lob(0)
+ OraFieldInf() : data(0), len(0), ind(0), typ(QVariant::Invalid), oraType(0), def(0), lob(0), dataPtr(nullptr)
{}
~OraFieldInf();
char *data;
@@ -841,6 +858,7 @@ private:
ub4 oraType;
OCIDefine *def;
OCILobLocator *lob;
+ void *dataPtr;
};
QVector<OraFieldInf> fieldInf;
@@ -855,6 +873,20 @@ QOCICols::OraFieldInf::~OraFieldInf()
if (r != 0)
qWarning("QOCICols: Cannot free LOB descriptor");
}
+ if (dataPtr) {
+ switch (typ) {
+ case QVariant::Date:
+ case QVariant::Time:
+ case QVariant::DateTime: {
+ int r = OCIDescriptorFree(dataPtr, OCI_DTYPE_TIMESTAMP_TZ);
+ if (r != OCI_SUCCESS)
+ qWarning("QOCICols: Cannot free OCIDateTime descriptor");
+ break;
+ }
+ default:
+ break;
+ }
+ }
}
QOCICols::QOCICols(int size, QOCIResultPrivate* dp)
@@ -901,13 +933,18 @@ QOCICols::QOCICols(int size, QOCIResultPrivate* dp)
switch (ofi.type) {
case QVariant::DateTime:
+ r = OCIDescriptorAlloc(d->env, (void **)&fieldInf[idx].dataPtr, OCI_DTYPE_TIMESTAMP_TZ, 0, 0);
+ if (r != OCI_SUCCESS) {
+ qWarning("QOCICols: Unable to allocate the OCIDateTime descriptor");
+ break;
+ }
r = OCIDefineByPos(d->sql,
&dfn,
d->err,
count,
- create(idx, dataSize+1),
- dataSize+1,
- SQLT_DAT,
+ &fieldInf[idx].dataPtr,
+ sizeof(OCIDateTime *),
+ SQLT_TIMESTAMP_TZ,
&(fieldInf[idx].ind),
0, 0, OCI_DEFAULT);
break;
@@ -1322,11 +1359,10 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector<QVariant> &boundValues, b
fieldTypes.append(tp == QVariant::List ? boundValues.at(i).toList().value(0).type()
: tp);
}
-
- QList<QByteArray> tmpStorage;
SizeArray tmpSizes(columnCount);
QVector<QOCIBatchColumn> columns(columnCount);
QOCIBatchCleanupHandler cleaner(columns);
+ TempStorage tmpStorage;
// figuring out buffer sizes
for (i = 0; i < columnCount; ++i) {
@@ -1363,8 +1399,8 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector<QVariant> &boundValues, b
case QVariant::Time:
case QVariant::Date:
case QVariant::DateTime:
- col.bindAs = SQLT_DAT;
- col.maxLen = 7;
+ col.bindAs = SQLT_TIMESTAMP_TZ;
+ col.maxLen = sizeof(OCIDateTime *);
break;
case QVariant::Int:
@@ -1432,7 +1468,7 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector<QVariant> &boundValues, b
for (uint row = 0; row < col.recordCount; ++row) {
const QVariant &val = boundValues.at(i).toList().at(row);
- if (val.isNull()){
+ if (val.isNull() && !d->isOutValue(i)) {
columns[i].indicators[row] = -1;
columns[i].lengths[row] = 0;
} else {
@@ -1443,9 +1479,8 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector<QVariant> &boundValues, b
case QVariant::Date:
case QVariant::DateTime:{
columns[i].lengths[row] = columns[i].maxLen;
- const QByteArray ba = qMakeOraDate(val.toDateTime());
- Q_ASSERT(ba.size() == int(columns[i].maxLen));
- memcpy(dataPtr, ba.constData(), columns[i].maxLen);
+ QOCIDateTime *date = new QOCIDateTime(d->env, d->err, val.toDateTime());
+ *reinterpret_cast<OCIDateTime**>(dataPtr) = date->dateTime;
break;
}
case QVariant::Int:
@@ -1581,7 +1616,7 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector<QVariant> &boundValues, b
QVariant::Type tp = boundValues.at(i).type();
if (tp != QVariant::List) {
- qOraOutValue(boundValues[i], tmpStorage, d->err);
+ qOraOutValue(boundValues[i], tmpStorage, d->env, d->err);
if (*columns[i].indicators == -1)
boundValues[i] = QVariant(tp);
continue;
@@ -1593,16 +1628,16 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector<QVariant> &boundValues, b
for (uint r = 0; r < columns[i].recordCount; ++r){
if (columns[i].indicators[r] == -1) {
- (*list)[r] = QVariant();
+ (*list)[r] = QVariant(fieldTypes[i]);
continue;
}
switch(columns[i].bindAs) {
- case SQLT_DAT:
- (*list)[r] = qMakeDate(data + r * columns[i].maxLen);
+ case SQLT_TIMESTAMP_TZ:
+ (*list)[r] = QOCIDateTime::fromOCIDateTime(d->env, d->err,
+ *reinterpret_cast<OCIDateTime **>(data + r * columns[i].maxLen));
break;
-
case SQLT_INT:
(*list)[r] = *reinterpret_cast<int*>(data + r * columns[i].maxLen);
break;
@@ -1646,6 +1681,7 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector<QVariant> &boundValues, b
d->q_func()->setAt(QSql::BeforeFirstRow);
d->q_func()->setActive(true);
+ qDeleteAll(tmpStorage.dateTimes);
return true;
}
@@ -1754,7 +1790,8 @@ void QOCICols::getValues(QVector<QVariant> &v, int index)
switch (fld.typ) {
case QVariant::DateTime:
- v[index + i] = QVariant(qMakeDate(fld.data));
+ v[index + i] = QVariant(QOCIDateTime::fromOCIDateTime(d->env, d->err,
+ reinterpret_cast<OCIDateTime *>(fld.dataPtr)));
break;
case QVariant::Double:
case QVariant::Int:
@@ -1984,7 +2021,7 @@ bool QOCIResult::exec()
ub2 stmtType=0;
ub4 iters;
ub4 mode;
- QList<QByteArray> tmpStorage;
+ TempStorage tmpStorage;
IndicatorArray indicators(boundValueCount());
SizeArray tmpSizes(boundValueCount());
@@ -2055,7 +2092,7 @@ bool QOCIResult::exec()
if (hasOutValues())
d->outValues(boundValues(), indicators, tmpStorage);
-
+ qDeleteAll(tmpStorage.dateTimes);
return true;
}