SQLite - QSqlDatabase::transaction()

Hi,

how can i test if a TRANSACTION is ok?

I try this:

Code:

---

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:

Code:

---

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:

Code:

---

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

---

and

Code:

---

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!

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

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

Code:

---

QSqlDatabase db = QSqlDatabase::database();
db.transaction();
 
QSqlQuery q;
if ( !q.exec("SELECT;") )
   db.rollback();
 
q.clear();
if(!db.commit())
   QMessageBox();

---

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

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:

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.

Just simply like this

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() && 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();
}

---

Thanks for the help, the problem is now solved.