Releasing database file with QSqlDatabase

[JPNaude]

Hi

I am struggling to release a database file I use with QSqlDatabase. I need the ability to close the connection and then delete the database file before replacing it with a new database. I can't delete it because the my program is holding onto it. I create my connection using the code below and dbProject is define globally for now.

bool createDBConnection() {
	if (QSqlDatabase::connectionNames().isEmpty()) {
    	  dbProject = QSqlDatabase::addDatabase("QSQLITE");
        }
	dbProject.setDatabaseName(active_db_file);
	if (!dbProject.open()) {
	  // Error messages		
	}	
	return dbProject.isValid();
}

bool createDBConnection() {
	if (QSqlDatabase::connectionNames().isEmpty()) {
    	  dbProject = QSqlDatabase::addDatabase("QSQLITE");
        }
	dbProject.setDatabaseName(active_db_file);
	if (!dbProject.open()) {
	  // Error messages		
	}	
	return dbProject.isValid();
}

To copy to clipboard, switch view to plain text mode 

To release the file I am trying:

dbProject.close();

dbProject.close();

To copy to clipboard, switch view to plain text mode 

According to the QT docs it seems like this should be good enough. Does anybody know why it does not work?

Thanks
Jaco

[jacek]

Maybe you also need to call QSqlDatabase::removeDatabase()?

[wysota]

What is the error you are getting? Something about queries still being active?

[JPNaude]

Thanks for the replies.

I'll try ::removeDatabase() as well. Regarding an error message, I don't get any error message, the QFile remove() just fails since the file is opened by the program. I've verified this by trying to delete the file using an external application like windows explorer, which can't delete it because is being used by another application, my program.

I'll give removeDatabase() a go.

[wysota]

Until you have released the database using removeDatabase() a lock is held onto the file, that's correct.

[JPNaude]

No unfortunately that did not solve the problem. I've called removeDatabase() but it still keeps a lock on the file. To verify that I'm using it correctly,

If I add a database like this:
QSqlDatabase dbProject = QSqlDatabase::addDatabase("QSQLITE");
dbProject.setDatabaseName(active_db_file);

I remove it like this:
dbProject.close();
QSqlDatabase::removeDatabase(active_db_file);

Is this correct? If so there is something holding onto the database that I need to find...

[wysota]

No, this is wrong. Call removeDatabase() without parameters.

[JPNaude]

I tried that but it does not seem to work:

error: no matching function for call to 'QSqlDatabase::removeDatabase()'
note: candidates are: static void QSqlDatabase::removeDatabase(const QString&)

It needs a database name, but for this I've tried both active_db_file and "QSQLITE" without any effect. I've also tried to give a specific name to my connection but this does not seem possible.

[wysota]

Blah, sorry. Should be:

QSqlDatabase::removeDatabase(QSqlDatabase::database().connectionName());

QSqlDatabase::removeDatabase(QSqlDatabase::database().connectionName());

To copy to clipboard, switch view to plain text mode 

or

QSqlDatabase::removeDatabase(QSqlDatabase::connectionNames().first());

QSqlDatabase::removeDatabase(QSqlDatabase::connectionNames().first());

To copy to clipboard, switch view to plain text mode 

if you use Qt older than Qt 4.4.

[JPNaude]

Hi, thanks again for the reply.

I've tried your suggestions and checked to see if the connectionNames() list is empty after the command which is the case. Thus it removes the database. However it still keeps lock of the database. As I said before, I verify this by using windows explorer to try and delete it. Also, the QFile remove command fails on the file.

Any idea why the program is not losing the lock on the file?

Thanks
Jaco

[garethf]

I'm bumping this thread. Has anyone found a way to resolve this problem as I am having the same issue. If not I'll have to delve into the source code.

Basically I am doing all of the above in order to release the lock on the file. I have even dynamically created QApplication and my main window which is then deleted after use in main() and then tried to delete the database file from there without success.

Anyone else have the same problem? It's only an issue on Windows as far as I can tell (ok under Linux).

[GreenScape]

actually you shouldnt replace database file. you can just drop all database's tables, and voila u have new shiny and clean database.

[DanH]

Be sure you don't have any active queries. Execute finish() on any existing queries.

[cia.michele]

Dear DanH,
No Way I added the finish() function before to close database connection, but no way, the file is still lock. These are my functions:

[...]
 
void myThread1::clearDBConn(){
    qry->finish();
    db.close();
    db.~QSqlDatabase();
    QSqlDatabase::removeDatabase(nomeDB);
 
 
}
 
void myThread1::zipDB(){
    QString zipName,delName;
    zipName=nomeDB;
    clearDBConn();
    archive(zipName.replace(".s3db",".zip"),nomeDB,true);
    emit zipFileName(idProva, zipName);
    //delName=QDir().absoluteFilePath(nomeDB);
    QFile(nomeDB).remove();
}

[...]

void myThread1::clearDBConn(){
    qry->finish();
    db.close();
    db.~QSqlDatabase();
    QSqlDatabase::removeDatabase(nomeDB);


}

void myThread1::zipDB(){
    QString zipName,delName;
    zipName=nomeDB;
    clearDBConn();
    archive(zipName.replace(".s3db",".zip"),nomeDB,true);
    emit zipFileName(idProva, zipName);
    //delName=QDir().absoluteFilePath(nomeDB);
    QFile(nomeDB).remove();
}

To copy to clipboard, switch view to plain text mode 

Is there something wrong?

Thanks for your time

Michele

[wysota]

Yes. You need to destroy the db object before calling removeDatabase().

[cia.michele]

Thanks wysota but... I called

db.~QSqlDatabase();

db.~QSqlDatabase();

To copy to clipboard, switch view to plain text mode 

before removeDatabase(), so I've destroy the object isn't it?

Thanks for your time

Michele

[wysota]

If you do that, the application will crash when you quit it. Why are you storing the db object in the first place?

[cia.michele]

Thanks wysota but, I don't understand what you say with "in frist place".
I'd like to open only one connection per thread, so the thread can create and manage one DB, and when the test end, it should close the database, zip the file and store it into a MYSQL db archive, and delete the original db file and the zip file.

This is the code of my thread class; the Header:

#ifndef MYTHREAD1_H
#define MYTHREAD1_H
 
#include <QThread>
#include <QDataStream>
#include <QTextStream>
#include <QMutex>
#include <QReadWriteLock>
#include <QSettings>
#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
 
#include <QDir>
#include <QFile>
#include "quazip/quazip.h"
#include "quazip/quazipfile.h"
 
class myThread1 : public QThread
{
    Q_OBJECT
 
public:
    myThread1(int nrampa, int sleep);
    void stop();
 
public slots:
    void generateID();
    void execCmd(QString cmd);
    void clearDBConn();
    void zipDB();
 
protected:
    void run();
 
private:
    volatile bool stopped;
    QMessageBox msg;
    QMutex mutex;
    QReadWriteLock lock;
    QString prefix;
    int sleep,nrampa,idProva;
    QSettings *set;
    QString nomeDB;
    QSqlDatabase db;
    QSqlQuery *qry;
 
    void createDB();
    bool extract(const QString & filePath, const QString & extDirPath, const QString & singleFileName);
    bool archive(const QString & filePath, const QString & path, bool isFile, const QString & comment=QString(""));
 
 
signals:
    void newId(int rampa, QString newId);
    void zipFileName(int idProva,QString zipName);
 
 
};
 
#endif // MYTHREAD1_H

#ifndef MYTHREAD1_H
#define MYTHREAD1_H

#include <QThread>
#include <QDataStream>
#include <QTextStream>
#include <QMutex>
#include <QReadWriteLock>
#include <QSettings>
#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>

#include <QDir>
#include <QFile>
#include "quazip/quazip.h"
#include "quazip/quazipfile.h"

class myThread1 : public QThread
{
    Q_OBJECT

public:
    myThread1(int nrampa, int sleep);
    void stop();

public slots:
    void generateID();
    void execCmd(QString cmd);
    void clearDBConn();
    void zipDB();

protected:
    void run();

private:
    volatile bool stopped;
    QMessageBox msg;
    QMutex mutex;
    QReadWriteLock lock;
    QString prefix;
    int sleep,nrampa,idProva;
    QSettings *set;
    QString nomeDB;
    QSqlDatabase db;
    QSqlQuery *qry;

    void createDB();
    bool extract(const QString & filePath, const QString & extDirPath, const QString & singleFileName);
    bool archive(const QString & filePath, const QString & path, bool isFile, const QString & comment=QString(""));


signals:
    void newId(int rampa, QString newId);
    void zipFileName(int idProva,QString zipName);


};

#endif // MYTHREAD1_H

To copy to clipboard, switch view to plain text mode 

and the CODE:

#include "mythread1.h"
 
myThread1::myThread1(int nrampa, int sleep)
{
    stopped=true;
  [...]
}
 
void myThread1::run(){
//    stopped=false;
//    while(!stopped){
//        //int val = qrand();
//        if (genID){
 
//        }
//        msleep(sleep);
//    }
}
 
bool myThread1::extract(const QString & filePath, const QString & extDirPath, const QString & singleFileName) {
 
[...]
 
}
 
bool myThread1::archive(const QString & filePath, const QString & path, bool isFile, const QString & comment) {
    [...]
}
 
 
void myThread1::stop(){
    stopped=true;
}
 
void myThread1::generateID(){
[...]
 
}
 
void myThread1::createDB(){
    nomeDB=QString("R%2_Prova%1.s3db").arg(idProva).arg(nrampa);
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE",nomeDB);
    db.setDatabaseName(nomeDB);
    if (!db.open()){
        msg.setText(db.lastError().text());
        msg.show();
    }else{
        db.exec("create table test (campo int)");
    }
}
 
void myThread1::execCmd(QString cmd){
    qry = new  QSqlQuery(db);
    qry->exec(cmd);
}
 
void myThread1::clearDBConn(){
    qry->finish();
    db.close();
    db.~QSqlDatabase();
    QSqlDatabase::removeDatabase(nomeDB);
}
 
void myThread1::zipDB(){
    QString zipName,delName;
    zipName=nomeDB;
    clearDBConn();
    archive(zipName.replace(".s3db",".zip"),nomeDB,true);
    emit zipFileName(idProva, zipName);
    QFile(nomeDB).remove();
}

#include "mythread1.h"

myThread1::myThread1(int nrampa, int sleep)
{
    stopped=true;
  [...]
}

void myThread1::run(){
//    stopped=false;
//    while(!stopped){
//        //int val = qrand();
//        if (genID){

//        }
//        msleep(sleep);
//    }
}

bool myThread1::extract(const QString & filePath, const QString & extDirPath, const QString & singleFileName) {

[...]

}

bool myThread1::archive(const QString & filePath, const QString & path, bool isFile, const QString & comment) {
    [...]
}


void myThread1::stop(){
    stopped=true;
}

void myThread1::generateID(){
[...]
    
}

void myThread1::createDB(){
    nomeDB=QString("R%2_Prova%1.s3db").arg(idProva).arg(nrampa);
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE",nomeDB);
    db.setDatabaseName(nomeDB);
    if (!db.open()){
        msg.setText(db.lastError().text());
        msg.show();
    }else{
        db.exec("create table test (campo int)");
    }
}

void myThread1::execCmd(QString cmd){
    qry = new  QSqlQuery(db);
    qry->exec(cmd);
}

void myThread1::clearDBConn(){
    qry->finish();
    db.close();
    db.~QSqlDatabase();
    QSqlDatabase::removeDatabase(nomeDB);
}

void myThread1::zipDB(){
    QString zipName,delName;
    zipName=nomeDB;
    clearDBConn();
    archive(zipName.replace(".s3db",".zip"),nomeDB,true);
    emit zipFileName(idProva, zipName);
    QFile(nomeDB).remove();
}

To copy to clipboard, switch view to plain text mode 

If I understood your suggest, I should use

QSqlDatabase db = new QSqlDatabase

QSqlDatabase db = new QSqlDatabase

To copy to clipboard, switch view to plain text mode 

isn't it? So the db is a local object and at the end of the method it free the heap, isn't it?
But so, I should create one connection for every time I call the execCmd method, is right?

No other way to close and re-open the db connection?

Thanks for your time

[wysota]

Thanks wysota but, I don't understand what you say with "in frist place".

It's a figure of speech. I meant to ask why are you storing the database object (db) at all. QSqlDatabase objects are not meant to be stored as class member variables. At any point when you need the db object, you can retrieve it using QSqlDatabase::database() passing it the connection name passed to addDatabase() or cloneDatabase().

[cia.michele]

Thanks wysota, I trying to do what you say, but I think didn't understand so much.
I try to do it in this way, is the correct way you sayd?

void myThread1::createDB(){
    nomeDB=QString("R%2_Prova%1.s3db").arg(idProva).arg(nrampa);
    QSqlDatabase::addDatabase("QSQLITE",nomeDB);
    QSqlDatabase(nomeDB).setDatabaseName(nomeDB);
    if (!QSqlDatabase(nomeDB).open()){
        msg.setText(QSqlDatabase(nomeDB).lastError().text());
        msg.show();
    }else{
        QSqlDatabase(nomeDB).exec("create table test (campo int)");
    }
}
 
void myThread1::execCmd(QString cmd){
 
    if (!QSqlDatabase(nomeDB).open()){
        msg.setText(QSqlDatabase(nomeDB).lastError().text());
        msg.show();
    }else{
        QSqlDatabase(nomeDB).exec(cmd);
    }
    QSqlDatabase(nomeDB).close();
 
}

void myThread1::createDB(){
    nomeDB=QString("R%2_Prova%1.s3db").arg(idProva).arg(nrampa);
    QSqlDatabase::addDatabase("QSQLITE",nomeDB);
    QSqlDatabase(nomeDB).setDatabaseName(nomeDB);
    if (!QSqlDatabase(nomeDB).open()){
        msg.setText(QSqlDatabase(nomeDB).lastError().text());
        msg.show();
    }else{
        QSqlDatabase(nomeDB).exec("create table test (campo int)");
    }
}

void myThread1::execCmd(QString cmd){

    if (!QSqlDatabase(nomeDB).open()){
        msg.setText(QSqlDatabase(nomeDB).lastError().text());
        msg.show();
    }else{
        QSqlDatabase(nomeDB).exec(cmd);
    }
    QSqlDatabase(nomeDB).close();

}

To copy to clipboard, switch view to plain text mode 

I got this error:

'QSqlDatabase::QSqlDatabase(const QString&)' is protected

In which way I could address the database passing the connection name?

Thanks for your time

Michele