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