how to reconnect CORRECTLY qmysql database?

[yaseminyilmaz]

Hi all,

I've installed qt-sdk-win-opensource-2009.05.exe and then wrote a simple program to test whether QMYSQL plugin was successfully built and connected. As of now there is no problem but I need mysql_ping functionality. How to try to reconnect correctly if isOpen function returns false?

I've attached a part of code. Before executing a query, I want to test connectivity and reconnect if it's broken. In order to accomplish this, must removeDatabase be firstly called? Without definition of another a QSqlDatabase instance, can existing qtafx_db variable be usable? I'll pleasure so much if you can clarify this matter.

Wish you all the best,

int main (int argc, char** argv)
{
	QCoreApplication qtafx_app (argc, argv);
	bool qStatus;
	{
		QSqlDatabase qtafx_db = QSqlDatabase::addDatabase ("QMYSQL");
		qtafx_db.setHostName ("");
		qtafx_db.setDatabaseName ("");
		qtafx_db.setUserName ("");
		qtafx_db.setPassword ("");
 
		qStatus = qtafx_db.open();
 
                ....
                // before executing a query, I want to test connectivity and reconnect if it's broken 
                ....
 
		qtafx_db.close ();
		qtafx_db.removeDatabase ();
	}
}

int main (int argc, char** argv)
{
	QCoreApplication qtafx_app (argc, argv);
	bool qStatus;
	{
		QSqlDatabase qtafx_db = QSqlDatabase::addDatabase ("QMYSQL");
		qtafx_db.setHostName ("");
		qtafx_db.setDatabaseName ("");
		qtafx_db.setUserName ("");
		qtafx_db.setPassword ("");
		
		qStatus = qtafx_db.open();

                ....
                // before executing a query, I want to test connectivity and reconnect if it's broken 
                ....

		qtafx_db.close ();
		qtafx_db.removeDatabase ();
	}
}

To copy to clipboard, switch view to plain text mode 

[JD2000]

You could try enclosing the query in an if block:

if (!qtafx_db.open()){
 
// DB not open - create connection 
 
}else 
{
 
// Submit query
// and inspect QSqlQuery::lastError() to ensure it worked.
 
}

if (!qtafx_db.open()){

// DB not open - create connection 

}else 
{

// Submit query
// and inspect QSqlQuery::lastError() to ensure it worked.

}

To copy to clipboard, switch view to plain text mode 

[Tanuki-no Torigava]

You can try this:

int retries = 5;
int delay = 1000 * 3; // time in ms
QEventLoop loop;
QTimer delayTimer;
while (!qtafx_db.open() || retries)
{
     delayTimer.start(delay);
     loop.exec();
     retries--;
}
 
if (!qtafx_db.isOpen())
{
    qtafx_db.close ();
    qtafx_db.removeDatabase ();
    return 1; // Or your prefered code here or popup message
}

int retries = 5;
int delay = 1000 * 3; // time in ms
QEventLoop loop;
QTimer delayTimer;
while (!qtafx_db.open() || retries)
{
     delayTimer.start(delay);
     loop.exec();
     retries--;
}

if (!qtafx_db.isOpen())
{
    qtafx_db.close ();
    qtafx_db.removeDatabase ();
    return 1; // Or your prefered code here or popup message
}

To copy to clipboard, switch view to plain text mode 

In this code sample you 5 times retries to connect to the database with 3 seconds delay between retries. Hope that helps.

[JD2000]

Small point but you are closing a connection that is not open in line 14.

Out of interest why would you want to poll a connection like this?

If you open a connection, you may as well try to action the query.
Test the lasterror value to ensure that the query executed properly.
You may wish to use transactions to guarantee that the whole query was executed if your DB supports them.

If the connection is so intermittent that it needs to be tested before each query, there is no gaurantee that the connection will still be there immediately after the test has completed.

[Tanuki-no Torigava]

If you mean my code snippet then please take into consideration that it shows a piece of code. I don't know the parameters
yaseminyilmaz will use to open database. This is what's going on after you establish database connection.

[JD2000]

No, I was wondeing about the general approach of opening a connection and testing it before submitting any queries (that may still fail regardless), not your answer.

[graciano]

I use the typical connection.h:

#ifndef CONNECTION_H
#define CONNECTION_H
 
#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
static bool createConnection(const QString newHostName, const QString newDatabase, const QString newUserName, const QString newPassword){
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName(newHostName);
    db.setDatabaseName(newDatabase);
    db.setUserName(newUserName);
    db.setPassword(newPassword);
    if (!db.open()) {
        QMessageBox::critical(0, qApp->trUtf8("ERROR!"),
            qApp->trUtf8("Error message\n"
                     ""
                     "info..."), QMessageBox::Cancel);
        return false;
    }
    return true;
}
#endif

#ifndef CONNECTION_H
#define CONNECTION_H

#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
static bool createConnection(const QString newHostName, const QString newDatabase, const QString newUserName, const QString newPassword){
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName(newHostName);
    db.setDatabaseName(newDatabase);
    db.setUserName(newUserName);
    db.setPassword(newPassword);
    if (!db.open()) {
        QMessageBox::critical(0, qApp->trUtf8("ERROR!"),
            qApp->trUtf8("Error message\n"
                     ""
                     "info..."), QMessageBox::Cancel);
        return false;
    }
    return true;
}
#endif

To copy to clipboard, switch view to plain text mode 

One question about this.
Every time i reconnect i get a

QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.

QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.

To copy to clipboard, switch view to plain text mode 

Is there a problem with this?

[JD2000]

It sounds as though you are trying to open multiple connections to your database without closing the previous ones.

It is normal to reuse the already open connection rather than open a new one each time you want to access your database.

In your case try calling the createConnection() just once at the start of the programme and submit your all queries using this.

If you need multiple connections for some reason give them different names.