Qt中Oracle数据库的DateTime类型

目录

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

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

Oracle Call Interface data typeSQL type descriptionRecommended input (C++ or Qt data type)
NUMBERFLOAT, DOUBLE, PRECISIONc REALBy default mapping to QString
NUMBER(38)INTEGER INT SMALLINTtypedef qint8/16/32/64
NUMBER(p,s)NUMERIC(p,s) DECIMAL(p,s)aBy default mapping to QString
NVARCHAR2(n)Character string (NATIONAL CHARACTER VARYING(n) NATIONAL CHAR VARYING(n) NCHAR VARYING(n))Mapped to QString
NCHAR(n)Character string (NATIONAL CHARACTER(n) NATIONAL CHAR(n) NCHAR(n))Mapped to QString
CHAR(n)Character string (CHARACTER(n) CHAR(n))Mapped to QString
CLOBCharacter large string objectMapped to QString
BLOBA binary large objectMapped to QByteArray
TIMESTAMPYear, month, and day values of date, as well as hour, minute, and second values of timeMapped to QDateTime
? 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代表时间较为方便。

= 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代表时间较为方便。