SQLite - QSqlDatabase::transaction()

[whitefurrows]

Hi,

how can i test if a TRANSACTION is ok?

I try this:

QSqlDatabase db = QSqlDatabase::database();
QSqlQuery q(db);
qDebug() << db.driver()->hasFeature(QSqlDriver::Transactions); //true
so SQLite aided transactions

But the COMMIT for a TRANSACTION is always TRUE. Here a example:

QSqlDatabase db = QSqlDatabase::database();
db.transaction();

QSqlQuery q;
qDebug() << q.exec("SELECT;"); //false

q.clear();
if(!db.commit()){
db.rollback();
}
The COMMIT is TRUE and i try this:

QSqlDatabase db = QSqlDatabase::database();
QSqlQuery q(db);
qDebug() << db.driver()->beginTransaction(); //true
qDebug() << q.exec("SELECT;"); //false
qDebug() << db.driver()->commitTransaction();//true
and

qDebug() << q.exec("BEGIN TRANSACTION"); //true
qDebug() << q.exec("SELECT;"); //false
qDebug() << q.exec("COMMIT");//true
The ROLLBACK works fine, but i cant verifying the COMMIT respectively the TRANSACTION!

[wysota]

An incorrect select statement won't fail a transaction. Only if you have another connection to the database and do an insert or update there on any of the rows modified within the transaction will cause it to auto-abort. In other cases you have to roll it back yourself (for instance if the user cancels the task in progress).

[whitefurrows]

Now the COMMIT fail and i show a message and do nothing. Is that OK?

QSqlDatabase db = QSqlDatabase::database();
db.transaction();

QSqlQuery q;
if ( !q.exec("SELECT;") )
db.rollback();

q.clear();
if(!db.commit())
QMessageBox();

[wysota]

I don't know what you are asking for but you can't commit a transaction that has already been rolled back.

[whitefurrows]

I want read a sql-script and rollback if a sql-statement fails. I'm not sure how can i do that right. That's my code:


QSqlDatabase db = QSqlDatabase::database();
db.transaction();

bool sql_ok=TRUE;
QSqlQuery q;
QFile file(:/script.sql);
QString script=file.readAll();
QStringList sql=script.split(";", QString::SkipEmptyParts);

for (int i=0; i<sql.count(); i++)
{
sql_ok=q.exec(sql[i]);
if (!sql_ok)
{
QMessageBox::critical(0, "Error", q.lastError().text());
db.rollback();
break;
}
}

q.clear();
if(sql_ok)
{
if(!db.commit()){
QMessageBox::critical(0, "Error", db.lastError().text());
db.rollback();
}
}
Please help me and give me a example how that works right. Thanks in advance.

[Lesiok]

Just simply like this

QSqlDatabase db = QSqlDatabase::database();
db.transaction();

bool sql_ok=TRUE;
QSqlQuery q;
QFile file(:/script.sql);
QString script=file.readAll();
QStringList sql=script.split(";", QString::SkipEmptyParts);

for (int i=0; i<sql.count() && sql_ok; i++)
{
sql_ok=q.exec(sql[i]);
}

q.clear();
if(sql_ok)
{
sql_ok = db.commit();
}

if(!sql_ok)
{
QMessageBox::critical(0, "Error", q.lastError().text());
db.rollback();
}

[whitefurrows]

Thanks for the help, the problem is now solved.