QT & Sqlite problem: "database is locked"

**xfurrier** · 15th July 2009, 17: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.

Qt Code:

Switch view

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

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

To copy to clipboard, switch view to plain text mode

**Lykurg** · 15th July 2009, 18:02

Don't open a new database connection in changeDepartment(), just use:

Qt Code:

Switch view

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

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

To copy to clipboard, switch view to plain text mode

**xfurrier** · 17th July 2009, 07:06

Originally Posted by Lykurg

Don't open a new database connection in changeDepartment(), just use:

Qt Code:

Switch view

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

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

To copy to clipboard, switch view to plain text mode

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.