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

[d2uriel]

Edit: Check "this post":http://qt-project.org/forums/viewreply/83064/ for my solution. Well, not mine to be exact, it was suggested in the docs but I failed to follow the rules thinking I can do it better :P.

Dear Qt programmers,

I have a small issue with QSqlDatabase::removeDatabase() static method. In my application there are a couple of methods where I'm opening and closing DB connections. I do it like that:
@bool backupEngine::getBarcodes() {
QSqlDatabase* rl = new QSqlDatabase(QSqlDatabase::addDatabase("QODBC", DBNAME));
rl->setDatabaseName("DRIVER={SQL SERVER}; SERVER={" + QHostInfo::localHostName() + "}; DATABASE={" + DBNAME + "}");
QString queryRcpStr = "SELECT sth1, sth2 FROM tbl WHERE sth2 IS NOT NULL";

if (rl->open()) {
    QSqlQuery *query = new QSqlQuery(*rl);
    if (query->exec(queryRcpStr)) {
        while (query->next()) {
            // do some work here...
        }
    } else {
        msg = "Failed to exectue query on " + DBNAME + ". Error message: " + query->lastError().text();
        qDebug(&#41; << msg;
        return false;
    }
    delete query;
    rl->close();
} else {
    msg = "Failed connecting to " + DBNAME + ".";
    qDebug() << msg;
    return false;
}
delete rl;
QSqlDatabase::removeDatabase(DBNAME);
return true;

}@

This works perfectly fine.

I have a method which returns the list of all available databases on the server. It looks like that:
@QList<QString> backupEngine::getAvailableDBs() {
QList<QString> databases;
QSqlDatabase* db = new QSqlDatabase(QSqlDatabase::addDatabase("QODBC"));
db->setDatabaseName("DRIVER={SQL SERVER}; SERVER={" + QHostInfo::localHostName() + "}");
if (db->open()) {
QSqlQuery *query = new QSqlQuery();
if (query->exec("SELECT name FROM master.dbo.sysdatabases")) {
while (query->next()) {
databases.append(query->value(0).toString());
}
}
delete query;
db->close();
}
delete db;
QSqlDatabase::removeDatabase(QSqlDatabase::database().connectionName());
if(::debug) {
QList<QString>::iterator it;
for(it = databases.begin(); it != databases.end(); ++it) {
qDebug() << (*it);
}
}
return databases;
}@

This one throws a warning:
@Warning: 2012-04-17 10:39:06: QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.@

What am I doing wrong here?

Thank you in advance for your reply.

PS: I found "this":http://qt-project.org/forums/viewthread/328 thread and a couple of others but for some reason I cannot get rid of this warning message.

[d2uriel]

Since it's been over 3 days now and this thread went down to the third page I'd like to bump it up a bit.

What I forgot to add is that DBMS we're using is MSDE 2000.

Is there any other way of obtaining a list of databases without getting this warning message? It's resource leakage which I'd like to avoid. The application is using this method only once and doesn't have a GUI so it starts, works and ends without the user doing anything. But still I'd like to learn the proper way of achieving the goal of getting all database names from the DBMS.

[d2uriel]

Just a quick update:

I solved the problem (kind of). I followed "suggestions":http://qt-project.org/doc/qt-4.8/qsqldatabase.html#removeDatabase found in the docs. In my main application class I created private objects of QSqlDatabase class and created a method to connect to all required databases.
@
bool backupEngine::connectToDBs() {
rl->setDatabaseName("DRIVER={SQL SERVER}; SERVER={" + QHostInfo::localHostName() + "}; DATABASE={" + DBNAME1 + "}");
if(!rl->open()) {
qDebug() << "Error opening " + DBNAME1 + " databse.";
return false;
}

rcp->setDatabaseName("DRIVER={SQL SERVER}; SERVER={" + resourceServerIP + "}; DATABASE={" + DBNAME2 + "}");
if(!rcp->open()) {
    qDebug() << "Error opening " + DBNAME2 + " databse.";
    return false;
}

dbms->setDatabaseName("DRIVER={SQL SERVER}; SERVER={" + QHostInfo::localHostName() + "}");
if(!dbms->open()) {
    qDebug() << "Error connecting to SQL Server.";
    return false;
}

return true;

}
@

Then before every query I added an if statement like this one:
@
if(dbms->isValid() && dbms->isOpen()) {
QSqlQuery *query = new QSqlQuery(*dbms);
// do some work
delete query;
} else {
qDebug() << "Lost connection to local database.";
}
@

And finally, the destructor of my class takes care of removing the databases showing no warnings:
@
backupEngine::~backupEngine() {
if(rl) {
if(rl->isOpen()) {
rl->close();
}
delete rl;
}
if(rcp) {
if(rcp->isOpen()) {
rcp->close();
}
delete rcp;
}
if(dbms) {
if(dbms->isOpen()) {
dbms->close();
}
delete dbms;
}
QStringList list = QSqlDatabase::connectionNames();
for(int i = 0; i < list.count(); ++i) {
QSqlDatabase::removeDatabase(list[i]);
}
}
@

Sorry for 3 posts one after another. I just hope these snippets of code will become useful one day to someone ;-).

[goetz]

No problem. Thanks for sharing the solution - i'm sure it will be of use for someone else in the future.

[d2uriel]

@Volker: Do you think it's a good idea to add some doc notes to QtSql module regarding it's use, like:
#. Connecting to a SQL database through ODBC.
#. Executing queries (as an example retrieving list of databases from MSDE2000).
#. Closing and removing connections.
?

I'd like to make a small instruction because it really took me a while before I managed to find out how to make a connection, run a query, etc. Had to google around to find some info because the docs didn't explain everything (IMHO of course).

[goetz]

Oh, yes, go ahead! That's exactly what we're looking for in the doc notes. See Alexandras's "blog post":/blog/view/doc-notes-we-have-a-vision.

I would suggest to add the connection to ODBC note to the [[Doc:QSqlDatabase]] docs, as this has some basic examples on that topic already. Executing queries in general would better fit into [[doc:QSqlQuery]], the list of databases best into QSqlDatabase. Closing and removing connections fit into QSqlDatabase too.

Best would be some small snippets with an explanation (probably as inline comments in the code).

[d2uriel]

Umm, I guess I messed it up a bit then. It came out quite big. Have a look "here":http://qt-project.org/doc/note_revisions/172/284/view. You can probably edit it if needed. Anyway, thanks for your reply!

[goetz]

I'll have a look at it and give feedback here.

[d2uriel]

So Volker, any comments? :-)

[xvision]

I recently had a similar issue when using multiple SQLite databases.

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

The solution that worked for me was to hold the connection name variable as a member of my database manager class. I obtain the relevant database from the connection name which is then used as a parameter for the SQL query as shown in the basic example below.

@
int databaseManager::nonquery(const QString &query)
{
int rowsaffected = 0;

// obtain reference to relevant database based on connection name.
QSqlDatabase db = QSqlDatabase::database(m_ConnectionName,true);

if (db.isOpen())
{
    QSqlQuery sqlquery(db);
    bool status = sqlquery.exec(query);

    if (!status) syslog(LOG_ERR,"db nonquery error: %s",db.lastError().text().toStdString().c_str());

    rowsaffected = sqlquery.numRowsAffected();
}

db.close();

return rowsaffected;

}@

May be of help others...

[th.thielemann]

This works for me:
@
bool openDb(QSqlDatabase* db)
{
if(! db->open())
{
// print error
return false;
}
// Other open stuff
}

void insertRows(QSqlDatabase* db)
{
QSqlQuery query(*db);
query.exec("my sql statement");
query.finish();
}

void closeDb(QSqlDatabase* db)
{
db->close();
// other stuff
}

...
{
// db has scope within the brakets
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "mydb");
db.setDatabaseName("/path/to/my/db/file");
bool openDb(&db);
...
insertRows(&db);
...
closeDb(&db);
}
QSqlDatabase::removeDatabase("mydb");
...
@