QSqlQuery, Oracle and transactions

[manu007]

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]

[andre]

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.

[michael.goddard]

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

[manu007]

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.

[manu007]

The bug is reported. Hope this will be useful.

[andre]

A link to the report would be useful...

[manu007]

The link :

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

[manu007]

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;