PDA

View Full Version : Releasing database file with QSqlDatabase



JPNaude
19th August 2008, 12:42
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();
}

To release the file I am trying:

dbProject.close();

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

Thanks
Jaco

jacek
21st August 2008, 01:55
Maybe you also need to call QSqlDatabase::removeDatabase()?

wysota
21st August 2008, 08:20
What is the error you are getting? Something about queries still being active?

JPNaude
21st August 2008, 08:40
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
21st August 2008, 08:50
Until you have released the database using removeDatabase() a lock is held onto the file, that's correct.

JPNaude
21st August 2008, 09:14
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
21st August 2008, 09:29
No, this is wrong. Call removeDatabase() without parameters.

JPNaude
21st August 2008, 09:46
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
21st August 2008, 11:58
Blah, sorry. Should be:

QSqlDatabase::removeDatabase(QSqlDatabase::databas e().connectionName()); or


QSqlDatabase::removeDatabase(QSqlDatabase::connect ionNames().first()); if you use Qt older than Qt 4.4.

JPNaude
21st August 2008, 12:57
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
31st July 2010, 00:01
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
31st July 2010, 00:21
actually you shouldnt replace database file. you can just drop all database's tables, and voila u have new shiny and clean database.

asvil
31st July 2010, 16:53
Use ":memory:" instead of file name. http://osdir.com/ml/db.sqlite.general/2004-02/msg00257.html.
Or remove all instance of SQL objects.

garethf
10th August 2010, 23:03
Ahhh yes. I should probably use the :memory: option which would make sense. Thankyou for the advice.

Regards,
Gareth.

cia.michele
16th May 2011, 11:05
GoodMorning to everybody,
I have the same problem but... I can't use ":memory:" database.
I need to create one database for each test make on a special machine; I start a thread that manage the test, I create an sqlite db to store the data of the test and when the test end, I close the the db, zip the file and store it into MYSQL DB (blob field). Now the file sqlite is old, and I need to delete it at the end of the test, before create a new test.

There is no way to do this?

Thanks for your time

Michele

DanH
16th May 2011, 14:15
Be sure you don't have any active queries. Execute finish() on any existing queries.

cia.michele
20th May 2011, 14:45
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();
}


Is there something wrong?

Thanks for your time

Michele

wysota
20th May 2011, 14:53
Yes. You need to destroy the db object before calling removeDatabase().

cia.michele
20th May 2011, 15:11
Thanks wysota but... I called

db.~QSqlDatabase();
before removeDatabase(), so I've destroy the object isn't it?

Thanks for your time

Michele

wysota
20th May 2011, 15:26
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
20th May 2011, 15:46
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


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



If I understood your suggest, I should use

QSqlDatabase db = new QSqlDatabase
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
20th May 2011, 18:44
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
24th May 2011, 09:30
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();

}

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

wysota
24th May 2011, 09:33
QSqlDatabase::database() retrieves a handler to the database.

QSqlDatabase::addDatabase("QSQLITE", nomeDB);
{
// ...
QSqlDatabase db = QSqlDatabase::database(nomeDB); // a local variable, not a class member
db.exec(cmd);
db.close();
}
QSqlDatabase::removeDatabase(nomeDB);

cia.michele
24th May 2011, 10:33
Thanks a lot wysota!
Now it's RUN! :)

This is the definitive code :)



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){

QSqlDatabase::database(nomeDB).exec(cmd);

}

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


Now I use the QSqlDatabase db object only for set the database name, as variable and not as class member.

It run great.

Thanks

Michele