Qt中Oracle数据库的DateTime类型

qt

Oracle数据库的DateTime字段无法直接用字符串插入，必须用to_data函数强制转换。还好，Qt提供一种简便的方法。

Qt文档中《Data Types for Qt-supported Database Systems》这篇文章给出不同数据库中推荐使用的数据类型。
其中Oracle数据类型表格如下：
Oracle Call Interface Data Types

<th>
  SQL type description
</th>

<th>
  Recommended input (C++ or Qt data type)
</th>

<td>
  FLOAT, DOUBLE, PRECISIONc REAL
</td>

<td>
  By default mapping to <a href="qstring.html">QString</a>
</td>

<td>
  INTEGER INT SMALLINT
</td>

<td>
  typedef qint8/16/32/64
</td>

<td>
  NUMERIC(p,s) DECIMAL(p,s)a
</td>

<td>
  By default mapping to <a href="qstring.html">QString</a>
</td>

<td>
  Character string (NATIONAL CHARACTER VARYING(n) NATIONAL CHAR VARYING(n) NCHAR VARYING(n))
</td>

<td>
  Mapped to <a href="qstring.html">QString</a>
</td>

<td>
  Character string (NATIONAL CHARACTER(n) NATIONAL CHAR(n) NCHAR(n))
</td>

<td>
  Mapped to <a href="qstring.html">QString</a>
</td>

<td>
  Character string (CHARACTER(n) CHAR(n))
</td>

<td>
  Mapped to <a href="qstring.html">QString</a>
</td>

<td>
  Character large string object
</td>

<td>
  Mapped to <a href="qstring.html">QString</a>
</td>

<td>
  A binary large object
</td>

<td>
  Mapped to <a href="qbytearray.html">QByteArray</a>
</td>

<td>
  Year, month, and day values of date, as well as hour, minute, and second values of time
</td>

<td>
  Mapped to <a href="qdatetime.html">QDateTime</a>
</td>
DateTime类型与TIMESTAMP类型相似，可以用QDateTime来表示。也就是当数据类型为QDateTime时，qt在QSqlQuery::bindValue时自动将其转换成Oracle的日期时间类型，而无需我们手动调用to_date函数。Qt的Oracle数据库插件qsql_oci实现上述功能。
函数
[cpp]
int QOCIResultPrivate::bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, int pos,
const QVariant &val, dvoid *indPtr, ub2 *tmpSize, QList &tmpStorage)
[/cpp]
用于绑定数据。对不同类型的val数据，进行不同的处理。
例如
[cpp]
switch (val.type()) {
case QVariant::ByteArray:
r = OCIBindByPos(sql, hbnd, err,
pos + 1,
isOutValue(pos)
? const_cast(reinterpret_cast(data)->constData())
reinterpret_cast(data)->data(),
reinterpret_cast(data)->size(),
SQLT_BIN, indPtr, 0, 0, 0, 0, OCI_DEFAULT);
break;
case QVariant::Int:
r = OCIBindByPos(sql, hbnd, err,
pos + 1,
// if it’s an out value, the data is already detached
// so the const cast is safe.
const_cast(data),
sizeof(int),
SQLT_INT, indPtr, 0, 0, 0, 0, OCI_DEFAULT);
break;
[/cpp]
对于DateTime类型，有如下的处理：
[cpp]
case QVariant::Time:
case QVariant::Date:
case QVariant::DateTime: {
QByteArray ba = qMakeOraDate(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; }
[/cpp]
调用qMakeOraDate()函数生成Oracle可以识别的日期。函数实现如下：
[cpp]
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;
}
[/cpp]
另外还有从Oracle日期转换到QDateTime的函数。
[cpp]
QDateTime qMakeDate(const char* oraDate)
{
int century = 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();
}
[/cpp]
所以，还是使用QDateTime代表时间较为方便。