Releasing database file with QSqlDatabase

[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

[wysota]

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

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

To copy to clipboard, switch view to plain text mode 

[cia.michele]

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

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

To copy to clipboard, switch view to plain text mode 

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