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

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 2392a17792..1bf59bf8c0 100644
--- a/src/sql/drivers/oci/qsql_oci.cpp
+++ b/src/sql/drivers/oci/qsql_oci.cpp
@@ -112,7 +112,15 @@ 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);
+
+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<void *>(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<QVariant> &values, IndicatorArray &ind
 }
 
 // will assign out value and remove its temp storage.
-static void qOraOutValue(QVariant &value, QList<QByteArray> &storage)
+static void qOraOutValue(QVariant &value, QList<QByteArray> &storage, OCIError* err)
 {
     switch (value.type()) {
     case QVariant::Time:
@@ -406,6 +428,12 @@ static void qOraOutValue(QVariant &value, QList<QByteArray> &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<const QChar *>(storage.takeFirst().constData()));
@@ -423,7 +451,7 @@ void QOCIResultPrivate::outValues(QVector<QVariant> &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<OCINumber*>(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<OCINumber*>(ba.data()));
+    return ba;
+}
+
+qlonglong qMakeLongLong(const char* ociNumber, OCIError* err)
+{
+    qlonglong qll = 0;
+    OCINumberToInt(err, reinterpret_cast<const OCINumber *>(ociNumber), sizeof(qlonglong),
+                   OCI_NUMBER_SIGNED, &qll);
+    return qll;
+}
+
+qulonglong qMakeULongLong(const char* ociNumber, OCIError* err)
+{
+    qulonglong qull = 0;
+    OCINumberToInt(err, reinterpret_cast<const OCINumber *>(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<QVariant> &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<QVariant> &boundValues, b
                         *reinterpret_cast<uint*>(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<double*>(dataPtr) = val.toDouble();
@@ -1445,7 +1549,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);
+            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<QVariant> &boundValues, b
                     (*list)[r] =  *reinterpret_cast<uint*>(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<double*>(data + r * columns[i].maxLen);
                     break;
diff --git a/tests/auto/qsqldatabase/tst_databases.h b/tests/auto/qsqldatabase/tst_databases.h
index 5837719ca9..80535df8cb 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 5367bd65ca..b4a3e08748 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()