QSqldatabase "correct" usage

[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.

[Pl45m4]

@sairun said in QSqldatabase "correct" usage:

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

There is no problem... just do it correctly and it's fine.

look at the example I provided at PASTEBIN.

3rd time? You don't have to explain everything again and again.

I guess the best approach is to use a QSqlDatabase connection as a member of a class (probably MainWindow) contrary to what is documented.

No! Why you think so?

I think this is done by puting QSqlDatabase::removeDatabase() in MainWindow's destructor.

This is tricky when handling more than just the default connection.

Btw:
Your code on PasteBin hasn't changed... so still the "weird" creation of DB in your main.cpp where the retrieved db handle is not used and does absolutely nothing.... and still the same "hard coded" connections

// in main you add default connection
QSqlDatabase::addDatabase( "QSQLITE" );

// in "newFile()" you try to retrieve a connection named "DEFCON"
// which does not exist AFAICS
QSqlDatabase db = QSqlDatabase::database("DEFCON");

// in "openFile()" you also retrieve the default connection
QSqlDatabase db = QSqlDatabase::database();

Manage your connection(s) properly and you don't need any member to hold your connection inside your class.

[sairun]

@Pl45m4 said

There is no problem... just do it correctly and it's fine.

Well, I'm trying but apparently to no avail :-)

I guess the best approach is to use a QSqlDatabase connection as a member of a class (probably MainWindow) contrary to what is documented.

No! Why you think so?

Ok, if not, should I keep instantiating QSqlDatabase in main? Or should I instantiate it when I need the connection (e.g., inside newFile)? Is this what you mean? Because in the comment below

Btw:
Your code on PasteBin hasn't changed... so still the "weird" creation of DB in your main.cpp where the retrieved db handle is not used and does absolutely nothing.... and still the same "hard coded" connections

// in main you add default connection
QSqlDatabase::addDatabase( "QSQLITE" );

you say that the instantiation of a default connection in main does absolutely nothing. And you are probably right! Regarding the lines below

// in "newFile()" you try to retrieve a connection named "DEFCON"
// which does not exist AFAICS
QSqlDatabase db = QSqlDatabase::database("DEFCON");

it is obviously my fault. It wa a leftover of a version that used named connections. I've removed it from the code

Manage your connection(s) properly and you don't need any member to hold your connection inside your class.

This may be easy for you to say, but it's not clear to me! In Blanchette and Summerfield C++ GUI programming with Qt 4 (which I own) they say

Typically, database connections are set up in a separate function that we call at application startup. For example:

bool createConnection() {
   QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
   db.setHostName("mozart.konkordia.edu");
   db.setDatabaseName("musicdb");
   db.setUserName("gbatstone");
   db.setPassword("T17aV44");
   if (!db.open()) {
      QMessageBox::critical(0, QObject::tr("Database Error"),
      db.lastError().text());
      return false;
   }
   return true;
}

Of course, this is OK for a connection which is going to be made to a known host. But in my case, I'm dealing with a program that creates/loads files that are named by the user haphazardly. I cannot establish the names of databases beforehand, unless I opt to create databases in memory (:memory:).

I'll try to find a simple example that shows how to create/open sqlite files and show them on a QTableView using qt6. So far I have found none...

[Pl45m4]

should I keep instantiating QSqlDatabase in main? Or should I instantiate it when I need the connection (e.g., inside newFile)?

All you need to do is use the static functions, that's why they are static... so that you can retrieve the current (or any) active connection from anywhere in your code / class. They will return the database connection instance.

@sairun said in QSqldatabase "correct" usage:

In Blanchette and Summerfield C++ GUI programming with Qt 4 (which I own) they say

Even though the Book and Qt4 itself is veery old, the code (createConnection()) is still correct and different from yours.

I cannot establish the names of databases beforehand, unless I opt to create databases in memory (:memory:).

Database != Connection
You can open mydb.sqlite using "Database1" as connection name.
As far as I understand, you want to select the file "mydb.sqlite" using your file dialog. If you leave the connection name empty it will create or replace the default connection...
If you plan to access multiple db files at the same time, you should provide a name for each connection and then get the handle from QSqlDatabase::database("connectionName") wherever you might need it in some local function

I'll try to find a simple example that shows how to create/open sqlite files and show them on a QTableView using qt6. So far I have found none...

How you show the data has nothing to do with how you manage the database connection(s).

Ask yourself if you want to open one file/db at a time or should your program be able to load and write to different databases (i.e. sqlite files) at the same time.
However, make sure to close a connection before opening a new one.

There is nothing more to say.

Check the Qt Examples for related stuff.

[sairun]

@Pl45m4 said in QSqldatabase "correct" usage:

Check the Qt Examples for related stuff.

Yep, I'm looking at qsqlbrowser from Qt5 Examples' tree (I still have this old one around) and I think I'm beginning to understand the whole thing! We simply use the QSqlDatabase::addDatabase( driver, conn_name ) wherever we need to start the connection and keep using QSqlDatabase::database(conn_name) whenever we need to access the database! To close the database we do db = QSqlDatabase::database(conn_name) followed by db.close() and to destry the whole thing we do a QSqlDatabase::removeDatabase(conn_name).

I think I'll close the thread and mark it as solved.