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

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

To copy to clipboard, switch view to plain text mode 

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

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

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

q.clear();
if(!db.commit()){
db.rollback();
}

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

and

qDebug() << q.exec("BEGIN TRANSACTION"); //true
qDebug() << q.exec("SELECT;"); //false
qDebug() << q.exec("COMMIT");//true

qDebug() << q.exec("BEGIN TRANSACTION"); //true
qDebug() << q.exec("SELECT;"); //false
qDebug() << q.exec("COMMIT");//true

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

[whitefurrows]

Thanks for the help, the problem is now solved.