From c32ec4e928895a85aa10ce47a73c2bd439e7fc89 Mon Sep 17 00:00:00 2001 From: Charles Yin Date: Thu, 4 Nov 2010 12:30:46 +1000 Subject: More fix for QTBUG-14640:oci performance problem with qlonglong 1. OCINumber must be declared & initialized seperately, and the bound OCINumber values must be hold until the QOCIResultPrivate object is deleted. 2. More auto tests for this bug. 3. Add qulonglong support as well 4. Make the execBatch() works with longlong and ulonglong Task-number:QTBUG-14640 Reviewed-by: Michael Goddard Change-Id: I7d8bf1c44ce3aaa15ee85be325a5c98dc3ed3ce1 --- src/sql/drivers/oci/qsql_oci.cpp | 133 ++++++++++++++++++++++++++++++-- tests/auto/qsqldatabase/tst_databases.h | 2 +- tests/auto/qsqlquery/tst_qsqlquery.cpp | 121 ++++++++++++++++++++++++----- 3 files changed, 227 insertions(+), 29 deletions(-) diff --git a/src/sql/drivers/oci/qsql_oci.cpp b/src/sql/drivers/oci/qsql_oci.cpp index 2392a17..1bf59bf 100644 --- a/src/sql/drivers/oci/qsql_oci.cpp +++ b/src/sql/drivers/oci/qsql_oci.cpp @@ -112,7 +112,15 @@ typedef QVarLengthArray 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); + +static qlonglong qMakeLongLong(const char* ociNumber, OCIError* err); +static qulonglong qMakeULongLong(const char* ociNumber, OCIError* err); + static QString qOraWarn(OCIError *err, int *errorCode = 0); + #ifndef Q_CC_SUN static // for some reason, Sun CC can't use qOraWarning when it's declared static #endif @@ -292,13 +300,27 @@ int QOCIResultPrivate::bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, in SQLT_UIN, indPtr, 0, 0, 0, 0, OCI_DEFAULT); break; case QVariant::LongLong: + { + QByteArray ba = qMakeOCINumber(val.toLongLong(), err); 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(OCINumber), + ba.data(), + ba.size(), SQLT_VNU, indPtr, 0, 0, 0, 0, OCI_DEFAULT); + tmpStorage.append(ba); + break; + } + case QVariant::ULongLong: + { + QByteArray ba = qMakeOCINumber(val.toULongLong(), err); + r = OCIBindByPos(sql, hbnd, err, + pos + 1, + ba.data(), + ba.size(), + SQLT_VNU, indPtr, 0, 0, 0, 0, OCI_DEFAULT); + tmpStorage.append(ba); + break; + } case QVariant::Double: r = OCIBindByPos(sql, hbnd, err, pos + 1, @@ -394,7 +416,7 @@ int QOCIResultPrivate::bindValues(QVector &values, IndicatorArray &ind } // will assign out value and remove its temp storage. -static void qOraOutValue(QVariant &value, QList &storage) +static void qOraOutValue(QVariant &value, QList &storage, OCIError* err) { switch (value.type()) { case QVariant::Time: @@ -406,6 +428,12 @@ static void qOraOutValue(QVariant &value, QList &storage) case QVariant::DateTime: value = qMakeDate(storage.takeFirst()); break; + case QVariant::LongLong: + value = qMakeLongLong(storage.takeFirst(), err); + break; + case QVariant::ULongLong: + value = qMakeULongLong(storage.takeFirst(), err); + break; case QVariant::String: value = QString( reinterpret_cast(storage.takeFirst().constData())); @@ -423,7 +451,7 @@ void QOCIResultPrivate::outValues(QVector &values, IndicatorArray &ind if (!isOutValue(i)) continue; - qOraOutValue(values[i], tmpStorage); + qOraOutValue(values[i], tmpStorage, err); QVariant::Type typ = values.at(i).type(); if (indicators[i] == -1) // NULL @@ -683,6 +711,56 @@ QByteArray qMakeOraDate(const QDateTime& dt) return ba; } +/*! + \internal + + Convert qlonglong to the internal Oracle OCINumber format. + */ +QByteArray qMakeOCINumber(const qlonglong& ll, OCIError* err) +{ + QByteArray ba(sizeof(OCINumber), 0); + + OCINumberFromInt(err, + &ll, + sizeof(qlonglong), + OCI_NUMBER_SIGNED, + reinterpret_cast(ba.data())); + return ba; +} + +/*! + \internal + + Convert qulonglong to the internal Oracle OCINumber format. + */ +QByteArray qMakeOCINumber(const qulonglong& ull, OCIError* err) +{ + QByteArray ba(sizeof(OCINumber), 0); + + OCINumberFromInt(err, + &ull, + sizeof(qlonglong), + OCI_NUMBER_UNSIGNED, + reinterpret_cast(ba.data())); + return ba; +} + +qlonglong qMakeLongLong(const char* ociNumber, OCIError* err) +{ + qlonglong qll = 0; + OCINumberToInt(err, reinterpret_cast(ociNumber), sizeof(qlonglong), + OCI_NUMBER_SIGNED, &qll); + return qll; +} + +qulonglong qMakeULongLong(const char* ociNumber, OCIError* err) +{ + qulonglong qull = 0; + OCINumberToInt(err, reinterpret_cast(ociNumber), sizeof(qulonglong), + OCI_NUMBER_UNSIGNED, &qull); + return qull; +} + QDateTime qMakeDate(const char* oraDate) { int century = uchar(oraDate[0]); @@ -1267,6 +1345,16 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector &boundValues, b col.maxLen = sizeof(uint); break; + case QVariant::LongLong: + col.bindAs = SQLT_VNU; + col.maxLen = sizeof(OCINumber); + break; + + case QVariant::ULongLong: + col.bindAs = SQLT_VNU; + col.maxLen = sizeof(OCINumber); + break; + case QVariant::Double: col.bindAs = SQLT_FLT; col.maxLen = sizeof(double); @@ -1338,6 +1426,22 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector &boundValues, b *reinterpret_cast(dataPtr) = val.toUInt(); break; + case QVariant::LongLong: + { + columns[i].lengths[row] = columns[i].maxLen; + const QByteArray ba = qMakeOCINumber(val.toLongLong(), d->err); + Q_ASSERT(ba.size() == int(columns[i].maxLen)); + memcpy(dataPtr, ba.constData(), columns[i].maxLen); + break; + } + case QVariant::ULongLong: + { + columns[i].lengths[row] = columns[i].maxLen; + const QByteArray ba = qMakeOCINumber(val.toULongLong(), d->err); + Q_ASSERT(ba.size() == int(columns[i].maxLen)); + memcpy(dataPtr, ba.constData(), columns[i].maxLen); + break; + } case QVariant::Double: columns[i].lengths[row] = columns[i].maxLen; *reinterpret_cast(dataPtr) = val.toDouble(); @@ -1445,7 +1549,7 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector &boundValues, b QVariant::Type tp = boundValues.at(i).type(); if (tp != QVariant::List) { - qOraOutValue(boundValues[i], tmpStorage); + qOraOutValue(boundValues[i], tmpStorage, d->err); if (*columns[i].indicators == -1) boundValues[i] = QVariant(tp); continue; @@ -1475,6 +1579,21 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector &boundValues, b (*list)[r] = *reinterpret_cast(data + r * columns[i].maxLen); break; + case SQLT_VNU: + { + switch (boundValues.at(i).type()) { + case QVariant::LongLong: + (*list)[r] = qMakeLongLong(data + r * columns[i].maxLen, d->err); + break; + case QVariant::ULongLong: + (*list)[r] = qMakeULongLong(data + r * columns[i].maxLen, d->err); + break; + default: + break; + } + break; + } + case SQLT_FLT: (*list)[r] = *reinterpret_cast(data + r * columns[i].maxLen); break; diff --git a/tests/auto/qsqldatabase/tst_databases.h b/tests/auto/qsqldatabase/tst_databases.h index 5837719..80535df 100644 --- a/tests/auto/qsqldatabase/tst_databases.h +++ b/tests/auto/qsqldatabase/tst_databases.h @@ -208,7 +208,7 @@ public: // addDb( "QOCI8", "//horsehead.nokia.troll.no:1521/ustest.troll.no", "scott", "tiger", "" ); // Oracle 9i on horsehead // addDb( "QOCI8", "//iceblink.nokia.troll.no:1521/ice.troll.no", "scott", "tiger", "" ); // Oracle 8 on iceblink (not currently working) // addDb( "QOCI", "//silence.nokia.troll.no:1521/testdb", "scott", "tiger" ); // Oracle 10g on silence -// addDb( "QOCI", "//oracle10g-nokia.trolltech.com.au:1521/XE", "scott", "tiger" ); // Oracle 10gexpress on xen +// addDb( "QOCI", "//bq-oracle10g.apac.nokia.com:1521/XE", "scott", "tiger" ); // Oracle 10gexpress // This requires a local ODBC data source to be configured( pointing to a MySql database ) // addDb( "QODBC", "mysqlodbc", "troll", "trond" ); diff --git a/tests/auto/qsqlquery/tst_qsqlquery.cpp b/tests/auto/qsqlquery/tst_qsqlquery.cpp index 5367bd6..b4a3e08 100644 --- a/tests/auto/qsqlquery/tst_qsqlquery.cpp +++ b/tests/auto/qsqlquery/tst_qsqlquery.cpp @@ -139,6 +139,8 @@ private slots: void oraClob(); void oraLong_data() { generic_data("QOCI"); } void oraLong(); + void oraOCINumber_data() { generic_data("QOCI"); } + void oraOCINumber(); void outValuesDB2_data() { generic_data("QDB2"); } void outValuesDB2(); void storedProceduresIBase_data() {generic_data("QIBASE"); } @@ -211,8 +213,7 @@ private slots: void QTBUG_5765(); void QTBUG_14132_data() { generic_data("QOCI"); } void QTBUG_14132(); - void QTBUG_14640_data() { generic_data("QOCI"); } - void QTBUG_14640(); + void sqlite_constraint_data() { generic_data("QSQLITE"); } void sqlite_constraint(); @@ -332,7 +333,7 @@ void tst_QSqlQuery::dropTestTables( QSqlDatabase db ) << qTableName( "task_250026", __FILE__ ) << qTableName( "task_234422", __FILE__ ) << qTableName("test141895", __FILE__) - << qTableName("qtest_QTBUG14640", __FILE__); + << qTableName("qtest_oraOCINumber", __FILE__); if ( db.driverName().startsWith("QPSQL") ) tablenames << qTableName("task_233829", __FILE__); @@ -2950,11 +2951,11 @@ void tst_QSqlQuery::QTBUG_14132() begin \n\ outStr := 'OUTSTRING'; \n\ end;")); - QString outValue = "XXXXXXXXX"; - q.prepare("CALL "+procedureName+"(?)"); - q.addBindValue(outValue, QSql::Out); + QString placeholder = "XXXXXXXXX"; + QVERIFY(q.prepare("CALL "+procedureName+"(?)")); + q.addBindValue(placeholder, QSql::Out); QVERIFY_SQL(q, exec()); - QCOMPARE(outValue, QLatin1String("OUTSTRING")); + QCOMPARE(q.boundValue(0).toString(), QLatin1String("OUTSTRING")); } void tst_QSqlQuery::QTBUG_5251() @@ -3104,30 +3105,108 @@ void tst_QSqlQuery::QTBUG_5765() QCOMPARE(q.value(0).toInt(), 123); } -void tst_QSqlQuery::QTBUG_14640() +void tst_QSqlQuery::oraOCINumber() { QFETCH( QString, dbName ); QSqlDatabase db = QSqlDatabase::database( dbName ); CHECK_DATABASE( db ); - const QString qtest_QTBUG14640(qTableName("qtest_QTBUG14640", __FILE__)); + const QString qtest_oraOCINumber(qTableName("qtest_oraOCINumber", __FILE__)); QSqlQuery q( db ); q.setForwardOnly( true ); - QVERIFY_SQL( q, exec( "create table " + qtest_QTBUG14640 + - " (col1 number, col2 number)" ) ); - QVERIFY_SQL( q, exec( "insert into " + qtest_QTBUG14640 + " values (1, 1111)" ) ); - QVERIFY_SQL( q, exec( "insert into " + qtest_QTBUG14640 + " values (2, 2222)" ) ); - QVERIFY_SQL( q, exec( "insert into " + qtest_QTBUG14640 + " values (3, 3333)" ) ); - - QString sqlStr = "select * from " + qtest_QTBUG14640 + " where col1 == :bindValue0 AND col2 == :bindValue1"; - q.prepare(sqlStr); - q.bindValue(":bindValue0", qlonglong(1), QSql::In); - q.bindValue(":bindValue1", qlonglong(1111), QSql::In); + QVERIFY_SQL( q, exec( "create table " + qtest_oraOCINumber + + " (col1 number(20), col2 number(20))" ) ); + QVERIFY(q.prepare("insert into " + qtest_oraOCINumber + " values (?, ?)")); + QVariantList col1Values; + QVariantList col2Values; + col1Values << (qulonglong)(1) + << (qulonglong)(0) + << (qulonglong)(INT_MAX) + << (qulonglong)(UINT_MAX) + << (qulonglong)(LONG_MAX) + << (qulonglong)(ULONG_MAX) + << (qulonglong)(LLONG_MAX) + << (qulonglong)(ULLONG_MAX); + + col2Values << (qlonglong)(1) + << (qlonglong)(0) + << (qlonglong)(-1) + << (qlonglong)(LONG_MAX) + << (qlonglong)(LONG_MIN) + << (qlonglong)(ULONG_MAX) + << (qlonglong)(LLONG_MAX) + << (qlonglong)(LLONG_MIN); + + q.addBindValue(col1Values); + q.addBindValue(col2Values); + QVERIFY(q.execBatch()); + QString sqlStr = "select * from " + qtest_oraOCINumber + " where col1 = :bindValue0 AND col2 = :bindValue1"; + QVERIFY(q.prepare(sqlStr)); + + q.bindValue(":bindValue0", (qulonglong)(1), QSql::InOut); + q.bindValue(":bindValue1", (qlonglong)(1), QSql::InOut); + + QVERIFY_SQL( q, exec() ); + QVERIFY( q.next() ); + QCOMPARE(q.boundValue( 0 ).toULongLong(), qulonglong(1)); + QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(1)); + + q.bindValue(":bindValue0", (qulonglong)(0), QSql::InOut); + q.bindValue(":bindValue1", (qlonglong)(0), QSql::InOut); + QVERIFY_SQL( q, exec() ); + + QVERIFY( q.next() ); + QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(0)); + QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(0)); + + q.bindValue(":bindValue0", (qulonglong)(INT_MAX), QSql::InOut); + q.bindValue(":bindValue1", (qlonglong)(-1), QSql::InOut); + QVERIFY_SQL( q, exec() ); + + QVERIFY( q.next() ); + QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(INT_MAX)); + QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(-1)); + + q.bindValue(":bindValue0", (qulonglong)(UINT_MAX), QSql::InOut); + q.bindValue(":bindValue1", (qlonglong)(LONG_MAX), QSql::InOut); + QVERIFY_SQL( q, exec() ); + + QVERIFY( q.next() ); + QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(UINT_MAX)); + QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(LONG_MAX)); + + q.bindValue(":bindValue0", (qulonglong)(LONG_MAX), QSql::InOut); + q.bindValue(":bindValue1", (qlonglong)(LONG_MIN), QSql::InOut); + QVERIFY_SQL( q, exec() ); + + QVERIFY( q.next() ); + QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(LONG_MAX)); + QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(LONG_MIN)); + + q.bindValue(":bindValue0", (qulonglong)(ULONG_MAX), QSql::InOut); + q.bindValue(":bindValue1", (qlonglong)(ULONG_MAX), QSql::InOut); QVERIFY_SQL( q, exec() ); QVERIFY( q.next() ); - QCOMPARE(q.boundValue( 0 ).toLongLong(), qlonglong(1)); - QCOMPARE(q.boundValue( 1 ).toLongLong(), qlonglong(1111)); + QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(ULONG_MAX)); + QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(ULONG_MAX)); + + q.bindValue(":bindValue0", (qulonglong)(LLONG_MAX), QSql::InOut); + q.bindValue(":bindValue1", (qlonglong)(LLONG_MAX), QSql::InOut); + QVERIFY_SQL( q, exec() ); + + QVERIFY( q.next() ); + QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(LLONG_MAX)); + QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(LLONG_MAX)); + + q.bindValue(":bindValue0", (qulonglong)(ULLONG_MAX), QSql::InOut); + q.bindValue(":bindValue1", (qlonglong)(LLONG_MIN), QSql::InOut); + QVERIFY_SQL( q, exec() ); + + QVERIFY( q.next() ); + QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(ULLONG_MAX)); + QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(LLONG_MIN)); + } void tst_QSqlQuery::sqlite_constraint() -- cgit v0.12