QSqlDatabase with MYSQL management in the app

[Cobra91151]

@jsulm

MyDatabase class is where all connections/queries execute with QSqlDatabase to better manage it across all app. So I will keep the connection alive until the application closes. I will try it. Thanks.

[Cobra91151]

@jsulm

I have tried it, but when the object has been deleted, I no longer have access to close the database or remove connection when app closes. So there are two options:

1. Close the DB connection after the work is completed every time and remove the connection
2. Make static instance of the class (like singleton) and use it until the app closes and then close the DB connection and remove it.

What do you advice me to do? Thanks.

[jsulm]

@Cobra91151 You should read http://doc.qt.io/qt-5/qsqldatabase.html
And the code example there.
QSqlDatabase keeps all open connections and you can get them at any time. Whether you deleted your own MyDatabase or not doesn't matter at all. There is really no need to reinvent the wheel here.
Whether you open/close db connection everytime you need to use the DB or keep open connection until app finishes depends on your use case.

[Cobra91151]

@jsulm

I think I figured it out. I will reply soon. Thank you.

[JonB]

@Cobra91151
As @jsulm says.

When you use QSqlDatabase::addDatabase() (http://doc.qt.io/qt-5/qsqldatabase.html#addDatabase) you can give it a connectionName (or use the default), and you can always get back it at (to close etc.) via that name/default. It is QSqlDatabase that is maintaining a "pool" of connections for you, so you don't have to do it. (Not a model I was used from my background, but that's how it works from Qt, so get used to it!)

[Cobra91151]

@jsulm

When removing the DB connection I get the following error:

QSqlDatabasePrivate::removeDatabase: connection 'TestConnection' is still in use, all queries will cease to work.

Code:

bool Database::dbConnectionExists()
{
    if (database.contains("TestConnection")) {
        database = QSqlDatabase::database("TestConnection");
        return true;
    }

    return false;
}

void Database::closeConnection()
{
    if (dbConnectionExists() && database.isOpen()) {
        QString dbConnection = database.connectionName();
        closeDB();
        database = QSqlDatabase();
        qDebug() << dbConnection;
        database.removeDatabase(dbConnection);
        qDebug() << database.isOpen();
    }

    emit finished();
}

void Database::closeDB()
{
    database.close();
}

I think it still holds a reference to the database.
How to fix it? Thanks.

[SGaist]

removeDatabase is a static method.

Take a look at the its documentation to see how to avoid having that warning message.

[Cobra91151]

@SGaist

I have tried the static method also, the same warning message still exists.

Code (call to close db connection when app closes):

void Test2::closeDBConnection()
{
    Database *db = new Database();
    connect(db, &Database::finished, db, &Database::deleteLater);
    db->closeConnection();
}

void Test2::closeEvent(QCloseEvent *event)
{
    closeDBConnection();
    event->accept();
    QWidget::closeEvent(event);
}

[SGaist]

Because you have somewhere in your code an instance of QSqlDatabase stored.

From your code, I'd say you have a QSqlDatabase database member variable in your Database class. Remove that.

[Cobra91151]

@SGaist

What do you mean by removing QSqlDatabase database member variable? When I removed it, I will no longer open/restore or use QSqlDatabase connection.

I have changed code to:

   QSqlDatabase db = QSqlDatabase::database("TestConnection");

   if (db.isOpen()) {
       db.close();
       qDebug() << db.connectionName();
       QSqlDatabase::removeDatabase(db.connectionName());
   }

The issue still exists, I think you are right about removing QSqlDatabase member variable, I still get it by calling QSqlDatabase::database("TestConnection") and then all checks will proceed. I will try it.

[Cobra91151]

@SGaist

I have deleted the QSqlDatabase database member variable, but the issue still exists. But I think I figured it out. I will test it and reply.

Yes, it's finally working.

Code:

   QSqlDatabase db = QSqlDatabase::database("TestConnection");

   if (db.isOpen()) {
       QString dbConnectionName = db.connectionName();
       db.close();
       qDebug() << dbConnectionName;
       db = QSqlDatabase();
       db.removeDatabase(dbConnectionName); // or QSqlDatabase::removeDatabase(dbConnectionName);
   }

  emit finished();

And now I use the connection to the DB until app closes or close it whenever I want. Also I would like to add, that I add check in the constructor and use database connection - QSqlDatabase db = QSqlDatabase::database("TestConnection"); in the methods because different methods can execute in different situations, so it will always create or use the connection to database.

Database::Database(QObject *parent) : QObject(parent)
{
    if (!checkDBConnection()) {
         //Create db connection with the name "TestConnection"
    }
}

bool Database::checkDBConnection()
{
    if (QSqlDatabase::contains("TestConnection")) {
        return true;
    }

    return false;
}

Thank you all.

[SGaist]

Did you read the example code from the removeDatabase documentation I linked to earlier ?

[Cobra91151]

@SGaist

Yes, but for some reason I need to dereference the database object (db = QSqlDatabase();) to actually succeed with removing the DB connection even with the static method, otherwise I get error QSqlDatabasePrivate::removeDatabase: connection 'TestConnection' is still in use, all queries will cease to work..

[SGaist]

Please show the complete code you are currently using.

You should also check if you have other QSqlDatabase objects lying around.

[Cobra91151]

@SGaist

I don't have any member variable, only local variable QSqlDatabase db = QSqlDatabase::database("TestConnection"); to get the DB connection and use it to execute queries. It should deletes itself when it goes out of scope. So I think no QSqlDatabase objects are lying around.

More info available - warning remove database

[SGaist]

The correct way is described in the documentation of the function:

void someFunction()
{
    {
        QSqlDatabase db = QSqlDatabase::database("sales");
        QSqlQuery query("SELECT NAME, DOB FROM EMPLOYEES", db);
    }
    // Both "db" and "query" are destroyed because they are out of scope 
    QSqlDatabase::removeDatabase("sales"); // correct
}