PDA

View Full Version : QT & Sqlite problem: "database is locked"



xfurrier
15th July 2009, 18:43
Qt 4.5.0 & Sqlite 3.6.10

I'm using an SQLite database with two tables, EMPLOYEES & UPDATES. UPDATES is used as a queue for any changes to EMPLOYEES table.

The idea is to scan UPDATES table and - provided that the flag for that particular row is switched on - do the following: i) make a change in EMPLOYEES table, ii) delete that entry in UPDATES table.

Unfortunately, I keep getting "db is locked" error and I don't know how to resolve the issue. I really tried a lot, but to no avail.

Any help would be greatly appreciated. Attaching the complete application, database and error.

Thanks.



/*
* dbtest.cpp
*/
#include "dbtest.h"
#include <QDebug>

void DBTest::scanForUpdates() {
qDebug() << "SCAN: open connections" << QSqlDatabase::connectionNames();

{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE",
"main_connection");
db.setDatabaseName("dbtest.sqlite");
qDebug() << "SCAN: open connections" << QSqlDatabase::connectionNames();
if (!db.open()) {
qDebug() << " ***ERROR - cannot open local database";
return;
}

// get all updates
QSqlQuery querySelect(db);
querySelect.prepare(
"SELECT UPDATE_ID, PERSON_ID, NOTE, TO_CHANGE_NOW FROM UPDATES");
if (!querySelect.exec()) {
qDebug() << " ***ERROR - select: "
<< querySelect.lastError().text();
return;
}

while (querySelect.next()) {
QSqlRecord rec = querySelect.record();
int nUpdateID = rec.value("UPDATE_ID").toInt();
int nEmployeeID = rec.value("PERSON_ID").toInt();
QString strNote = rec.value("NOTE").toString();
bool bChangeNow = rec.value("TO_CHANGE_NOW").toBool();
qDebug() << "SCAN: update entry" << nUpdateID << nEmployeeID
<< strNote << bChangeNow;

if (bChangeNow) {
bool bResult = DBTest::changeDepartment(nEmployeeID, strNote);

if (bResult) {
// employee table successfully updated, delete in updates
QSqlQuery queryDelete(db);
queryDelete.prepare(
"DELETE FROM UPDATES WHERE UPDATE_ID=:nUpdateID");
queryDelete.bindValue(":nUpdateID", nUpdateID);
if (!queryDelete.exec()) {
qDebug() << " ***ERROR - delete:"
<< queryDelete.lastError().text();
qDebug() << " ***query"
<< queryDelete.executedQuery();
return;
}
} // end if (bResult)
} // end if (bChangeNow)
} // end while
}
QSqlDatabase::removeDatabase("main_connection");
}

bool DBTest::changeDepartment(int nEmployeeID, QString strDepartment) {
qDebug() << "DEPT:" << nEmployeeID << strDepartment;
bool bResult = true;

{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE",
"temp_connection");
db.setDatabaseName("dbtest.sqlite");
qDebug() << "DEPT: open connections" << QSqlDatabase::connectionNames();

if (!db.open()) {
qDebug() << " ***ERROR - cannot open local database";
bResult = false;
}

if (bResult) {
QSqlQuery queryUpdate(db);
queryUpdate.prepare(
"UPDATE EMPLOYEES SET DEPARTMENT=:strDepartment "
"WHERE PERSON_ID=:nEmployeeID");
queryUpdate.bindValue(":strDepartment", strDepartment);
queryUpdate.bindValue(":nEmployeeID", nEmployeeID);
if (!queryUpdate.exec()) {
qDebug() << " ***ERROR - update: "
<< queryUpdate.lastError().text();
qDebug() << " ***query" << queryUpdate.executedQuery();
bResult = false;
}
}
}
QSqlDatabase::removeDatabase("temp_connection");

return bResult;
}

Lykurg
15th July 2009, 19:02
Don't open a new database connection in changeDepartment(), just use:
bool DBTest::changeDepartment(int nEmployeeID, QString strDepartment) {
qDebug() << "DEPT:" << nEmployeeID << strDepartment;
bool bResult = true;
QSqlDatabase db = QSqlDatabase::database("main_connection"); // it's already open!
QSqlQuery queryUpdate(db);
queryUpdate.prepare(
"UPDATE EMPLOYEES SET DEPARTMENT=:strDepartment "
"WHERE PERSON_ID=:nEmployeeID");
queryUpdate.bindValue(":strDepartment", strDepartment);
queryUpdate.bindValue(":nEmployeeID", nEmployeeID);
if (!queryUpdate.exec()) {
qDebug() << " ***ERROR - update: "
<< queryUpdate.lastError().text();
qDebug() << " ***query" << queryUpdate.executedQuery();
bResult = false;
}

return bResult;
}

xfurrier
17th July 2009, 08:06
Don't open a new database connection in changeDepartment(), just use:
bool DBTest::changeDepartment(int nEmployeeID, QString strDepartment) {
qDebug() << "DEPT:" << nEmployeeID << strDepartment;
bool bResult = true;
QSqlDatabase db = QSqlDatabase::database("main_connection"); // it's already open!
QSqlQuery queryUpdate(db);
...


Absolutely right - it's amazing how one can`t see the forest for the trees. The solution is so obvious and so simple - once you know it. Thanks.