Qt Forum

    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Unsolved

    Call for Presentations - Qt World Summit

    QSqlQuery, Oracle and transactions

    General and Desktop
    3
    8
    4389
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • M
      manu007 last edited by

      I think i found a bug in the oci driver.

      Starting a new transaction, the transaction flag is not correctly updated in the QOCIResultPrivate structure, the following patch to qsql_oci.cpp allow to work with or without any transaction :

      @
      175c175
      < const bool *transaction;

      bool transaction;
      

      1534c1534
      < *d->transaction ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS);

                         d->transaction ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS);
      

      1770c1770
      < sql(0), transaction(&driver->transaction), serverVersion(driver->serverVersion),

        sql(0), transaction(driver->transaction), serverVersion(driver->serverVersion),
      

      1970c1970
      < mode = *d->transaction ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS;

          mode = d->transaction ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS;
      

      @

      [EDIT: code formatting, please wrap in @-tags, Volker]

      1 Reply Last reply Reply Quote 0
      • A
        andre last edited by

        Please file this as a bug on "Jira":http://bugreports.qt.nokia.com, and attach your patch there. It might very well get lost if you just post it here. Better yet, create a merge request for your patch on Gitorious.

        1 Reply Last reply Reply Quote 0
        • M
          michael.goddard last edited by

          Can you also include the test code that shows this issue?

          1 Reply Last reply Reply Quote 0
          • M
            manu007 last edited by

            With pleasure.

            @
            #include <stdio.h>
            #include <QtGui>
            #include <QtSql>

            #include <QString>
            #include <QHostInfo>

            #include <QSqlDatabase>
            #include <QSqlError>
            #include <QSqlQuery>
            #include <QtGui>
            #include <QtSql>

            //
            // Oracle : create table actlist(tag varchar2(40), action varchar2(40));
            // sqlite : create index ix_actlist on actlist (tag);
            //
            // sqlite : create table actlist (tag varchar2(40), action varchar2(40));
            // sqlite : create index ix_actlist on actlist (tag);
            //
            // mysql : create table actlist (tag varchar(40), action varchar(40));
            // mysql : create index ix_actlist on actlist (tag);
            //
            //
            //
            // To execute the program :
            //
            // ./ActList QSQLITE /path/ActList.dbf manu manu 10
            // ./ActList QOCI ora11g2 manu manu 10
            // ./ActList QMYSQL manu manu manu 10
            //

            QSqlDatabase Db;

            bool createConnections(QString &_stHost, QString &_stDriver, QString &_stDbName,
            QString &_stUser, QString &_stPasswd)
            {

            qDebug() << "createConnections Host:" << _stHost;
            qDebug() << "createConnections Driver:" << _stDriver;
            qDebug() << "createConnections DbName:" << _stDbName;
            qDebug() << "createConnections User:" << _stUser;
            qDebug() << "createConnections Passwd:" << _stPasswd;

            Db = QSqlDatabase::addDatabase(_stDriver);
            Db.setDatabaseName( _stDbName );
            Db.setHostName( _stHost );
            Db.setUserName( _stUser );
            Db.setPassword( _stPasswd );

            if ( ! Db.open() )
            {
            qCritical("createconn: Cannot open database: %s (%s)", Db.lastError().text().toLatin1().data(),
            qt_error_string().toLocal8Bit().data());

            return FALSE;
            }

            qDebug() << "createconn: valid:" << Db.isValid(); // Returns false

            qDebug() << "createconn: DriverName:" << Db.driverName(); // Returns false
            qDebug() << "createconn: Driver's:" << Db.drivers(); // Returns false

            if (Db.driver()->hasFeature(QSqlDriver::Transactions) == true)
            qDebug() << "createconn: trans: transactions supported";
            else
            qDebug() << "createconn: trans: transactions NOT supported";

            if (Db.driver()->hasFeature(QSqlDriver::PreparedQueries) == true)
            qDebug() << "createconn: trans: PreparedQueries supported";
            else
            qDebug() << "createconn: trans: PreparedQueries NOT supported";

            if (Db.driver()->hasFeature(QSqlDriver::BLOB) == true)
            qDebug() << "createconn: trans: BLOB supported";
            else
            qDebug() << "createconn: trans: BLOB NOT supported";

            return TRUE;
            

            }

            int main( int argc, char *argv[] )
            {

            QString stCampo1, stCampo2, stHost;
            QHostInfo hiHost;
            int siMany, siWk1, siWk2;
            size_t szSize = 50;
            char scWk1[szSize];

            QString stDriver(argv[1]), stDbName(argv[2]), stUser(argv[3]), stPasswd(argv[4]);

            QApplication app( argc, argv, FALSE );
            

            stHost = hiHost.localHostName();
            siMany = ::QString(argv[5]).toInt();

            if ( createConnections(stHost, stDriver, stDbName, stUser, stPasswd) )
            

            {

            qDebug() << "main: DriverName:" << Db.driverName();
            qDebug() << "main: Driver's:" << Db.drivers();
            qDebug() << "main: Database:" << Db.databaseName();
            qDebug() << "main: Hostname:" << Db.hostName();
            qDebug() << "main: Records:" << siMany;

            QSqlQuery sqIn(Db);

            if (Db.transaction())
            {
            qDebug() << ::QString("Transaction started normally");
            }
            else
            {
            qDebug() << ::QString("Cannot start transaction");
            exit(-1);
            }

            sqIn.prepare("insert into actlist (tag, action) values (:P1, :P2)");

            siWk2 = 5000;

            for (siWk1=1; siWk1<=siMany; siWk1++)
            {
            ::snprintf(scWk1, szSize, "d", siWk1);
            stCampo1 = QString::fromAscii(scWk1);
            stCampo1.append(::QString("-01234567890123456789"));
            stCampo2 = ::QString("Action - ");
            stCampo2.append(::QString().setNum(siWk1));
            sqIn.bindValue(":P1", stCampo1);
            sqIn.bindValue(":P2", stCampo2);
            if (siMany > 10)
            {
            if (siWk1 == siWk2)
            {
            qDebug() << siWk2;
            siWk2 += 5000;
            }
            }
            else
            qDebug() << stCampo1 << stCampo2;

            if (sqIn.exec() != true)
            {
            qDebug() << ::QString("Write actlist error: %1")
            .arg(sqIn.lastError().text());
            exit(-1);
            }
            if (siWk1 > 10000)
            break;
            }

            sqIn.finish();

            if (Db.rollback())
            {
            qDebug() << ::QString("Normal rollback");
            }
            else
            {
            qDebug() << ::QString("Cannot made rollback");
            }
            }
            else
            {
            qDebug() << ::QString("Not connected exit:");
            }

            return 0;
            

            }
            @

            After the rollback all tuples remain on the table, this does not occurs with mysql (sqlite is not working anymore).

            Then i will try to post this patch in bugreports.

            1 Reply Last reply Reply Quote 0
            • M
              manu007 last edited by

              The bug is reported. Hope this will be useful.

              1 Reply Last reply Reply Quote 0
              • A
                andre last edited by

                A link to the report would be useful...

                1 Reply Last reply Reply Quote 0
                • M
                  manu007 last edited by

                  The link :

                  http://bugreports.qt.nokia.com/browse/QTBUG-18272

                  1 Reply Last reply Reply Quote 0
                  • M
                    manu007 last edited by

                    Hi All,

                    Two years after and the bug remains. ;-)

                    Nobody is working with QT and Oracle ?

                    Now for version 5.0.2, just added "case CancelQuery" to avoid a warning message, there is the patch :

                    177c177
                    < const bool *transaction;

                    bool *transaction;
                    

                    1772c1772
                    < sql(0), transaction(&driver->transaction), serverVersion(driver->serverVersion),

                      sql(0), transaction((bool*)(&driver->transaction)), serverVersion(driver->serverVersion),
                    

                    2131a2132,2133

                    case CancelQuery:
                        return false;
                    
                    1 Reply Last reply Reply Quote 0
                    • First post
                      Last post