Unable to commit transaction

[cydside]

Hi to All,
I'm working on a SQLITE database and it returns the following error after submiAll() in void frmUnValore_AME::submit() function when I try to run modelAME->database().commit():

QSqlError(-1, "Unable to commit transaction", "cannot commit transaction -
SQL statements in progress")

it's quite strange cause I use this funtion template to edit other tables in the database (and it works). As anyone help me suggesting why it happens?
Thanks!



#include "frmUnValore_AME.h"

/*----------------------------------------------------------------------------*/

frmUnValore_AME::frmUnValore_AME(QSqlTableModel *model, const int &isItNew,
QWidget *parent) : QDialog(parent)
{
ui.setupUi(this);
this->setWindowFlags(Qt::Dialog | Qt::MSWindowsFixedSizeDialogHint);
this->setAttribute(Qt::WA_DeleteOnClose);

modelAME = new QSqlTableModel(this);
modelAME = model;

if (modelAME->database().transaction())
{
qDebug() << "frmUnValore_AME TRANSACTION database!";
}
else
{
qDebug() << "frmUnValore_AME TRANSACTION FALSE!";
qDebug() << modelAME->database().lastError();
}


if (isItNew < 0)
{
id = modelAME->rowCount();
modelAME->insertRow(id);
}
else
{
id = isItNew;
}

mapper = new QDataWidgetMapper(this);
mapper->setModel(modelAME);
mapper->setSubmitPolicy(QDataWidgetMapper::ManualSubmit);
mapper->addMapping(ui.lnePrimo, 1);
mapper->setCurrentIndex(id);

connect(ui.btnAnnulla, SIGNAL(clicked()), this, SLOT(revert()));
connect(ui.btnOk, SIGNAL(clicked()), this, SLOT(submit()));

qDebug() << "frmUnValore_AME Loaded!";
}

/*----------------------------------------------------------------------------*/

void frmUnValore_AME::revert()
{
mapper->revert();
qDebug() << "->frmUnValore_AME (Aggiunta Rifiutata!";
modelAME->revertAll();
modelAME->database().rollback();

qDebug() << "frmUnValore_AME closed!";
}

/*----------------------------------------------------------------------------*/

void frmUnValore_AME::submit()
{
if (mapper->submit())
{
mapper->setCurrentIndex(id);
qDebug() << "frmUnValore_AME Ok submit!";

if (modelAME->submitAll())
{
if (modelAME->database().commit())
{
qDebug() << "frmUnValore_AME COMMIT database!";
}
else
{
qDebug() << "frmUnValore_AME COMMIT FALSE!";
qDebug() << modelAME->database().lastError();
}
}
else
{
modelAME->revertAll();
modelAME->database().rollback();
QMessageBox::warning(this, tr("Attenzione!"),
tr("Il database ha riportato un errore: %1")
.arg(modelAME->lastError().text()));
}
}
else
{
qDebug() << "No submit!";
QMessageBox::warning(this, tr("Attenzione!"),
tr("Il database ha riportato un errore: %1")
.arg(modelAME->lastError().text()));
}
}

[cydside]

It has been partially solved stopping the QSqlTableModel query (modelAME) then run commit() and re-exec() the QSqlTableModel's query as follow:



void frmUnValore_AME::submit()
{
if (mapper->submit())
{
mapper->setCurrentIndex(id);
qDebug() << "frmUnValore_AME Ok submit!";

if (modelAME->submitAll())
{
modelAME->query().finish(); // Stop the query...

if (modelAME->database().commit()) //Now it works!!!
{
qDebug() << "frmUnValore_AME COMMIT database!";
modelAME->query().exec(); //...Restart the query!
}
else
{
qDebug() << "frmUnValore_AME COMMIT FALSE!";
QMessageBox::critical(this, tr("Attenzione!"),
tr("Il database ha riportato un errore: \n%1")
.arg(modelAME->database().lastError().text()));
}
}
else
{
modelAME->revertAll();
modelAME->database().rollback();
QMessageBox::critical(this, tr("Attenzione!"),
QString("Il database ha riportato un errore: %1")
.arg(modelAME->database().lastError().text()));
}
}
else
{
qDebug() << "No submit!";
QMessageBox::critical(this, tr("Attenzione!"),
QString("Il database ha riportato un errore: \n%1")
.arg(modelAME->database().lastError().text()));
}
}


Have you ever met that behaviour?
Would it depend by SQLITE?

Thanks

[janus]

Hi,

maybe its due to the lazy model population you have to avoid with sqlite (QSqlQueryModel::fetchMore)

[cydside]

Ok, now including that code:



while (model->canFetchMore())
model->fetchMore();


I force fetching all the records, but the error persist if I cut modelAME->query().finish(); // Stop the query...

It's stopping me...:crying:

[janus]

What is the models submit policy? I suppose OnManualSubmit is the best option for sqlite.

[cydside]

Yes, it is. It would be quite strange because SQLITE drivers don't return the query size but support Transactions:



QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(db_path);
qDebug() << "QSQLITE QSqlDriver::QuerySize: " << db.driver()->hasFeature(QSqlDriver::QuerySize); // FALSE
qDebug() << "QSQLITE QSqlDriver::Transactions: " << db.driver()->hasFeature(QSqlDriver::Transactions); // TRUE


I guess it depends, maybe, on SQLITE cache size, I'm investigating...

[Banjo]

Did you manage to solve this?

I find things work OK until the table with the insert/update has more than 256 rows ?