QSqldatabase "correct" usage

Christian Ehrlicher

"SQLite also supports in-memory and temporary databases. Simply pass respectively ":memory:" or an empty string as the database name."

Since you did not provide a db name, an in-memory db is created as written in the documentation: https://doc.qt.io/qt-6/sql-driver.html#qsqlite-for-sqlite-version-3-and-above

sairun

@Christian-Ehrlicher

I don't want in memory databases. I want named SQLITE databases that can be portable. The user should provide a new file name or an already existent file name. Since he/she can use/create several databases during one session, I have to know when a file database is being used to close it before opening/creating the following one. But I can't use any of the methods of QSqlDatabase - except for the fileName() - to infer that a file is being used. As I see it now, db.isValid() and db.isOpen() are redundant.

Christian Ehrlicher

@sairun said in QSqldatabase "correct" usage:

As I see it now, db.isValid() and db.isOpen() are redundant.

Only for sqlite for the reasons wrote above.

sairun

@Christian-Ehrlicher said in QSqldatabase "correct" usage:

Only for sqlite for the reasons wrote above.

I see what you mean! Still, there are things that don't make sense in all this.

It took me a while to reply to you because my post was being flagged as SPAM. I've put the relevant code in PASTEBIN if you want to replicate it. The example in PASTEBIN is very simple but it explains what I want to to achieve. I'm not saying that it is correct, but it works as expected.

During a single run, you can create or open several SQLITE databases. Each time you create one database it inserts a random number in a record. Every time you open it again it inserts another number. The only thing that does not work is overwriting an existent file. Other than that the behavior is exactly what I want, but I'm not sure if it's leaking stuff. Valgrind says that there are probably some lost memory blocks (still reachable) but that's normal with any Qt executable.

Now what puzzles me (related to my original topic) is that if I replace

QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );

with

QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" , "connection" );

in main.c and

QSqlDatabase db = QSqlDatabase::database();

with

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

in void MainWindow::newFile() and void MainWindow::openFile() (both in mainwindow.cpp) the program stops working! It throws the following error:

qt.sql.qsqlquery: QSqlQuery::exec: database not open

I was expecting no problems when using named connections. Somehow the code fails to open the databases that it created or fails to create new ones. The "default" connections works.

Christian Ehrlicher

Please provide a minimal, compilable example of your problem. I don't know what you are doing or what you are trying to achieve...

sairun

This post is deleted!

sairun

Hi @Christian-Ehrlicher

You may have missed it, but I did provide the code for a simple executable in my previous post! The thing is that the original content was flagged as SPAM and I had to move the code to a link in PASTEBIN. Maybe that's the reason you didn't pick it.

Pl45m4

@sairun said in QSqldatabase "correct" usage:

QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );

I don't understand why you want to call this in main.cpp at all... since you don't do anything with the local db variable there...
So why adding a database there and probably messing up the rest of the workflow?

As said before you should only access the currently opened db using for example the static QSqlDatabase::database() locally.

When working with multiple connections, you can pass the connection (connection name) you've specified when adding/creating the database/connection.

QSqlDatabase db;
QSqlDatabase::addDatabase( "QSQLITE", "my.db" );
QSqlDatabase::addDatabase( "QSQLITE", "mySecond.db" );
// db operates on "my.db"
db = QSqlDatabase::database("my.db");
db.open();
db.close();
// switch to second.db
db = QSqlDatabase::database("mySecond.db");

sairun

@Pl45m4 said in QSqldatabase "correct" usage:

@sairun said in QSqldatabase "correct" usage:

QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );

I don't understand why you want to call this in main.cpp at all... since you don't do anything with the local db variable there...

This probably comes from my ignorance in using the QSqlDatabase object. In the Qt Manual one can read

Warning: It is highly recommended that you do not keep a copy of the QSqlDatabase around
as a member of a class, as this will prevent the instance from being correctly cleaned up on
shutdown. If you need to access an existing QSqlDatabase, it should be accessed with database().
If you chose to have a QSqlDatabase member variable, this needs to be deleted before the
QCoreApplication instance is deleted, otherwise it may lead to undefined behavior.

As far as I could understand, you create an instance of the connection with that statement in main. It also allows you to check if the QSQLITE3 driver is available. If by some reason it is not installed or available the program quits. Other than that, I've provided a minimum compilable demo to show what I want to achieve in PASTEBIN. The program allows one user to create "databases" (by this I mean sqlite file based databases) on the fly. Each database gets a record with a random number upon creation or when susequently reopened. What I'm not sure is that whenever you replace an already established connection with a new one for a recently opened file the program leaks something! Other than that it works as expected. The only thing it does not do is to overwrite an already created file (but that I know how to fix).

Christian Ehrlicher

You should close the db instance before creating a new one

QSqlDatabase::removeDatabase("DEFCON");
QSqlDatabase db = QSqlDatabase::database("DEFCON");

Otherwise you will get runtime warnings about an already opened connection.

Pl45m4

@sairun said in QSqldatabase "correct" usage:

Other than that, I've provided a minimum compilable demo to show what I want to achieve in PASTEBIN.

Yes you said that twice :)
I was referring to your main.cpp from your example.

If connectionName is not specified, the new connection becomes the default connection for the application, and subsequent calls to database() without the connection name argument will return the default connection. If a connectionName is provided here, use database(connectionName) to retrieve the connection.
( https://doc.qt.io/qt-6/qsqldatabase.html#addDatabase)

sairun

@Christian-Ehrlicher said in QSqldatabase "correct" usage:

You should close the db instance before creating a new one
QSqlDatabase::removeDatabase("DEFCON");
QSqlDatabase db = QSqlDatabase::database("DEFCON");
Otherwise you will get runtime warnings about an already opened connection.

The funny thing is that with the example I have provided I receive no error or warning messages if I use unnamed connections. If I put an name in QSqlDatabase db = QSqlDatabase::database() the program complains about not being able to open the files already created!

Christian Ehrlicher

Because it's already open and you don't close it...

sairun

@Christian-Ehrlicher

Yes, but even so, the example program refuses to work with named connections! I don't understand this behavior.

The thing is that I am developing a program that must access several database files during a session. The user connects to a remote server to fetch stuff that he then "saves" in different SQLITE files. If the QSqlDatabase should be created in main.c as per documentation, how does one proceed afterwards? How does one knows that a database file is being used to close it and open a new one? The only way I know is that if a sqlite database file is opened the databaseName() method returns a non empty QString.

I understand that this is specific to SQLITE only because the other DB Drivers don't deal with files.

PS: my reputation does not let me publish more than one response each 10 minutes. Sorry for the delay!

Christian Ehrlicher

I don't see any close() call in your code...
And this MRE works perfectly fine for me:

int main(int argc, char* argv[])
{
    QCoreApplication app(argc, argv);
    {
        auto db = QSqlDatabase::addDatabase("QSQLITE", "MyDb");
        db.setDatabaseName("temp1.sql");
        qDebug() << db.open();
        {
            QSqlQuery q(db);
            qDebug() << q.exec("CREATE TABLE example (id TEXT PRIMARY KEY)");
        }
        db.close();
    }
    {
        QSqlDatabase::removeDatabase("MyDb");
        auto db = QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName("temp2.sql");
        qDebug() << db.open();
        {
            QSqlQuery q(db);
            qDebug() << q.exec("CREATE TABLE example (id TEXT PRIMARY KEY)");
        }
        db.close();
    }
    QSqlDatabase::removeDatabase("MyDb");
    return 0;
}

-->
true
true
true
true

sairun

Hi @Christian-Ehrlicher

I'm not totally sure I understand your code. First, the lower block (in the second set of {} inside main) is executed after the QCoreApplication app. Inside app you create an instance of a connection named "MyDb". You then create a database file temp1.sql which is opened, inserted, and closed. After this, the outermost code is executed. You remove the previous connection and create a new one, this time without a name. Then you create, open, insert and close a new database file (temp2.sql). In the end, you remove the connection with QSqlDatabase::removeDatabase("MyDb") and the program exits. I wonder how this works (but it does!), as the last time you created a connection it was unnamed. Does it get the name of the first one? Does this mean that any connection created after app is started gets the default name "MyDb"?

Pl45m4

@sairun said in QSqldatabase "correct" usage:

Inside app

There is no "inside app"...
@Christian-Ehrlicher just devided the code into different scopes by using {... } so it's easier to see where you switch from one DB to another.

I wonder how this works (but it does!), as the last time you created a connection it was unnamed. Does it get the name of the first one?

As I quoted above, if yo don't set a name, the new connection becomes the new default connection. But you can address different connections by using their name when calling addDatabase or removeDatabase

sairun

@Pl45m4 said

There is no "inside app"...

Ok, I see. This is a kind of c++ construct that I've never used (delimiting different scopes by {}).

Anyway, the example of @Christian-Ehrlicher makes sense. Two different instances of QSqlDatabase are created and destroyed sequentially in main (in different scopes). On the other hand, in the program I want to develop the creation/loading of the databases (files) should be made by the user later on the program after the creation of the GUI, using methods newFile() or openFile(). Why?

The program should fetch data from a remote server and store it on a local database. A user can start the program and select an already established database or create a new one for the session. He/she can then edit the database, modify some attributes, delete some records and fetch additional data. So the connection to the database should normally be maintained through he whole duration of the program. It is connected with a QSqlTableModel which in turn feeds a QTableView. But the decision to create a new file or open an existent file during the execution should still be available to the user. He/she may close a connection to a database (saving it) and create a new one during the same session.

So, creating a global QSqlDatabase instance (in main) may be not the best approach (this was the original question). I was hoping that by creating a global QSqlDatabase, I would use QSqlDatabase::database() in MainWindow to access this global resource. The problem is that during a run, the program may need to "destroy" the global QSqlDatabase (saving changes into file) and create a new instance as explained above. This implies that the new instance of QSqlDatabase wouldn't be created on main but in MainWindow. Should it be destroyed on MainWindows destructor or can it be destroyed on main using a named connection?

Perhaps, as @JonB said earlier, I should move the management of SQLITE connections to my MainWindow class, where newFile() and openFile() methods are defined. But this is against the recommendation in the documentation of QSqldatabase!

jsulm

@sairun I don't understand the problem. You create a database or connect to an existing one whenever you need to do so. There is no need for any global QSqlDatabase instances. QSqlDatabase manages all your db connection, which you can get at any time from it.

sairun

@jsulm look at the example I provided at PASTEBIN. It works perfectly but it's wrong. You can open/create multiple SQLITE databases during the run and although you don't close any of them (and overwrite the connections) the sqlite databases will "survive" the whole session with their data intact! I was trying to implement something following the advice in Qt's documentation of QSqlDatabase by defining the QSqlDatabase in main and use it in other classes by calling database()!

The whole "problem" comes from the following line on QSqlDatabase Documentation

Warning: It is highly recommended that you do not keep a copy of the QSqlDatabase around as a member of a class, as this will prevent the instance from being correctly cleaned up on shutdown. If you need to access an existing QSqlDatabase, it should be accessed with database().

In the specific case of the program I'm developing I guess the best approach is to use a QSqlDatabase connection as a member of a class (probably MainWindow) contrary to what is documented. I just have to make sure that the instance of QSqlDatabase is destroyed before QCoreApplication is deleted! I think this is done by puting QSqlDatabase::removeDatabase() in MainWindow's destructor.