QSqlQuery, Oracle and transactions



  • 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]



  • 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.



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



  • 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.



  • The bug is reported. Hope this will be useful.



  • A link to the report would be useful...





  • 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;
    

Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.