Optimal Approach to Using QSqlDatabase and QSqlQuery

The document states that the Query object is capable of being used for several queries. However, it is typically more practical to employ separate QSqlQuery objects for each query.

Question:

I’m uncertain about the instructions in the manual. Is this the correct approach to take?

{
 QSqlDatabase db = QSqlDatabase::addDatabase (...);
 QSqlQuery query (db);
 query.exec (...);
}
QSqlDatabase::removeDatabase (...);

The document highlights that either

query

or

db

will undergo automatic deconstruction. However, the effectiveness of this approach is questionable.

If I were to enclose

db

within a class, as demonstrated below, it could be cached.

class Dummy {
  Dummy() { 
    db = QSqlDatabase::addDatabase (...);
  }
  ~Dummy() {
    db.close();
  }
  bool run() {
    QSqlQuery query (db);
    bool retval = query.exec (...);
    blabla ...
  }
  private:
    QSqlDatabase db;
};

On occasion, there are warnings that come into view, such as:

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


run()

wasn’t called by me.


Solution 1:

By creating an object using

QSqlDatabase

and calling

removeDatabase

, you are linking or unlinking a tuple consisting of the driver, hostname:port, database name, and username/password with a specific name or the default connection name. The SQL driver is initialized at this point, but the database will not be accessible until

QSqlDatabase::open

is called.

The connection name is defined for the entire application. Thus, modifying

addDatabase

in any of the related objects will impact all other

QSqlDatabase

objects that share the same connection name, causing all active queries to become invalid.

The initial code snippet you mentioned exhibits the accurate method to detach the connection name, by making certain that:

  • Upon closing the database, the

    QSqlQuery

    instances are disconnected from

    QSqlDatabase

    through the automatic invocation of

    QSqlQuery::finish()

    when the

    QSqlQuery

    object is out of scope.
  • When

    QSqlDatabase::removeDatabase

    is called,

    QSqlDatabase

    instances with identical connection names will be affected by

    close()

    . Additionally,

    close()

    is automatically invoked when the

    QSqlDatabase

    object goes out of scope.

Upon the creation of
qsqldatabase
, you have the option to either establish a persistent connection that lasts for the entire lifetime of the application (1) or create a connection that is only utilized when necessary (2).

  1. To avoid redundancy, it is recommended to keep a single instance of <code>
    QSqlDatabase
    </code> in a specific class, such as the main window, and use it in other objects by either passing the <code>
    QSqlDatabase
    </code> directly or providing the connection name to <code>
    QSqlDatabase::database
    </code> to retrieve the <code>
    QSqlDatabase
    </code> instance. <code>
    QSqlDatabase::database
    </code> retrieves <code>
    QSqlDatabase
    </code> from its name using <code>
    QHash
    </code>, which may be slightly slower than passing the <code>
    QSqlDatabase
    </code> object directly between objects and functions. If you use the default connection, you don’t need to pass any parameter; just call <code>
    QSqlDatabase::database()
    </code>.

    // In an object that has the same lifetime as your application
    // (or as a global variable, since it has almost the same goal here)
    QSqlDatabase db;
    // In the constructor or initialization function of that object       
    db = QSqlDatabase::addDatabase("QSQLDRIVER", "connection-name"); 
    db.setHostname(...);
    // ...
    if(!this->db.open())  // open it and keep it opened
    {
        // Error handling...
    }
    // --------
    // Anywhere you need it, you can use the "global" db object 
    // or get the database connection from the connection name        
    QSqlDatabase db = QSqlDatabase::database("connection-name"); 
    QSqlQuery query(db);             
     
  2. Set up <code>
    QSqlDatabase
    </code> configuration, verify the parameters, and discard the instance. Although the connection name remains available, the database must be re-established later.

    {
        // Allocated on the stack
        QSqlDatabase db = QSqlDatabase::addDatabase("QSQLDRIVER", "connection-name"); 
        db.setHostname(...);
        // ...
        if(!this->db.open()) // test the connection
        {
           // Error handling
        }
    // db is closed when it goes out of scope
    } 
    {
        // Same thing as for (1), but by default database() opens 
        // the connection if it isn't already opened 
        QSqlDatabase db = QSqlDatabase::database("connection-name"); 
        QSqlQuery query(db);
    // if there is no other connection open with that connection name,
    // the connection is closed when db goes out of scope
    } 
     

    If you have a situation where multiple functions are using the same database connection, it is important to avoid closing the connection explicitly. This is because if one function closes the connection before returning control to another function, the connection will be closed for both functions, which could lead to undesirable consequences.


Solution 2:


The QSqlDatabase and QSqlQuery classes are thin layers over specific implementations. Therefore, in your initial example, using ‘QSqlDatabase db(name)’ to obtain the database object incurs minimal overhead. This applies whether you add the connection with a name or use the default database.

The function removeDatabase is akin to shutting down the file (in the case of sqlite) or disconnecting (in the case of ODBC/MySql/Postgres). Hence, it is usually performed when the program comes to a halt. It is crucial to keep in mind the warning that reminds you to destroy all database and query objects that relate to that database beforehand, or else undesirable things might occur.


Solution 3:


To avoid encountering problems with the database connection or query, it is important to follow the instructions in the exact order as presented below. This process has been tested and found to be effective in Qt5.

QSqlQueryModel *model = new QSqlQueryModel;
db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(fileName);
if (db.isValid())
{
    db.open();
    if (db.isOpen())
    {
        QSqlQuery searchQuery(db);
        searchQuery.prepare("SELECT * FROM myTable");
        searchQuery.exec();
        if(searchQuery.isActive())
        {
            model->setQuery(searchQuery);
            sui->DBDisplay->setModel(model);
            db.close();
        } else {
            qDebug() << "query is not active";
        }
    } else {
        qDebug() << "DB is not open";
    }
} else {
    qDebug() << "DB is not valid";
}

Frequently Asked Questions