QSqlDatabase connection timeout?

[joseprl89]

I'm developing an application which lets you choose the database connection you want to create (including the SQL Driver).

It works fine, but i would like to set a connection timeout, because I use to connect to a database in a VPN host, and when I don't have the VPN ON, it just hangs there for 30 secs, and I would like to set a different connection timeout.

I've seen that the documentation says to use "setConnectOptions(QString)", but on the Qt Docs i can't find the option to set the timeout.

Any help would be appreciated.

Thanks

[Rhayader]

What you are looking for is in QSqlDatabase

The options are database specific. For SQLite the busy timeout is "QSQLITE_BUSY_TIMEOUT"

[joseprl89]

Thanks for your reply,

I have seen the QSqlDatabase manual and the setConnectOptions() function, and even if it shows the correct timeout option for some of the SQL plugins, it doesn't for some others (include MySQL which i am using right now), so I don't know the correct option string to use.

Right now I have this piece of code:





QString timeout;
DB.driver();
QString driver = DB.driverName();
if(driver=="QMYSQL"){
timeout = "MYSQL_OPT_CONNECT_TIMEOUT";
}
else if(driver=="QDB2"){
timeout="SQL_ATTR_LOGIN_TIMEOUT";
}
else if(driver=="QPSQL"){
timeout="connect_timeout";
}
else if(driver.startsWith("QSQLITE")){
timeout="QSQLITE_BUSY_TIMEOUT";
}
else if(driver == "QOCI"){
timeout="Connection Timeout";
}
else if(driver == "QTDS"){
timeout="TimeOut";
}
else{ //Q ODBC (open database connector (vendria a ser el general).
timeout="SQL_ATTR_CONNECTION_TIMEOUT";
}

if(timeout!=""){
DB.setConnectOptions(timeout+"="+QString::number(secs));
}



At which I decide (depending on the SQL driver), which connection string should I use. I haven't found the MySQL timeout option, and I keep getting this message:

QMYSQLDriver::open: Illegal connect option value 'MYSQL_OPT_CONNECT_TIMEOUT=3'

[BalaQT]

QMYSQLDriver:pen: Illegal connect option value 'MYSQL_OPT_CONNECT_TIMEOUT=3'
hi,
check your mysql settings,
bala

[joseprl89]

So, it's the SQL server who sets the connection timeout, and there is nothing i can do from my application to change it???

[Rhayader]

If you read the qsql_mysql.cpp you will notice that the only options you can pass as argument to setConnectOptions are the ones that are documented in Qtdocs. There are various suggestions in QtBugs but are currently unresolved eg http://bugreports.qt.nokia.com/browse/QTBUG-321 (I haven't checked it in depth).
If you are feeling adventurous you can create your own SqlDriver that implements the timeout, but I guess it is not gonna be easy.

[Rhayader]

Since I found it quite interesting and easier that I first though here follows the
Instructions for the creation of QMYSQLmodDriver.
I implemented only the MYSQL_OPT_CONNECT_TIMEOUT variable. On Linux this variable is also used for waiting for the first answer from the server (<--from MySQL reference guide), something that is not true for windows where the wait_timeout variable is responsible for the first connection ( setting the CLIENT_INTERACTIVE might help with the right value - untested by me)

QMYSQLmodDRIVER made and tested:
-on kubuntu linux (x86) with Qt 4.7.1 and MySQL 5.1.
-on windows 7 (x86) with Qt 4.7.2(mingw) and MySQL 5.5.

If you have compiled your driver of QMYSQL the process that follows is pretty much the same. Download the sources if you haven't already.
First a statement "I hope this is helpful, blah, blah, blah... THERE IS NO WARRANTY [GPL]:confused: never understood it completely"
So let's create copies of the folders that we will need.


cp -R $QTDIR/src/plugins/sqldrivers/mysql/ $QTDIR/src/plugin/sqldrivers/mysqlmod/
cp -R $QTDIR/src/sql/drivers/mysql/ $QTDIR/src/sql/sqldrivers/mysqlmod/


Inside .../sql/drivers/mysqlmod open the qsql_mysql.cpp and
a)replace the include just before QT_BEGIN_NAMESPACE

#include "../../../sql/drivers/mysqlmod/qsql_mysql.h"
b)replace the void QMYSQLDriver::open with the following..


bool QMYSQLDriver::open(const QString& db,
const QString& user,
const QString& password,
const QString& host,
int port,
const QString& connOpts)
{
if (isOpen())
close();

/* This is a hack to get MySQL's stored procedure support working.
Since a stored procedure _may_ return multiple result sets,
we have to enable CLIEN_MULTI_STATEMENTS here, otherwise _any_
stored procedure call will fail.
*/
unsigned int optionFlags = Q_CLIENT_MULTI_STATEMENTS;
const QStringList opts(connOpts.split(QLatin1Char(';'), QString::SkipEmptyParts));
QString unixSocket;
// -------------------code added here----------------------- //
unsigned int connectTimeout=0;
//------------------------------------------------------------------- //
#if MYSQL_VERSION_ID >= 50000
my_bool reconnect=false;
#endif

// extract the real options from the string
for (int i = 0; i < opts.count(); ++i) {
QString tmp(opts.at(i).simplified());
int idx;
if ((idx = tmp.indexOf(QLatin1Char('='))) != -1) {
QString val = tmp.mid(idx + 1).simplified();
QString opt = tmp.left(idx).simplified();
if (opt == QLatin1String("UNIX_SOCKET"))
unixSocket = val;
// -------------------code added here----------------------- //
else if( opt == QLatin1String("MYSQL_OPT_CONNECT_TIMEOUT"))
{
bool ok;
connectTimeout=val.toUInt(&ok);
if (!ok) {
connectTimeout=0;
qWarning("QMYSQLmodDriver Invalid timeout value %s", val.toLocal8Bit().constData());
}
}
//------------------------------------------------------------------- //
#if MYSQL_VERSION_ID >= 50000
else if (opt == QLatin1String("MYSQL_OPT_RECONNECT")) {
if (val == QLatin1String("TRUE") || val == QLatin1String("1") || val.isEmpty())
reconnect = true;
}
#endif
else if (val == QLatin1String("TRUE") || val == QLatin1String("1"))
setOptionFlag(optionFlags, tmp.left(idx).simplified());
else
qWarning("QMYSQLDriver::open: Illegal connect option value '%s'",
tmp.toLocal8Bit().constData());
} else {
setOptionFlag(optionFlags, tmp);
}
}
// -------------------code added here----------------------- //
if (!(d->mysql = mysql_init((MYSQL*) 0)))
{
setLastError(qMakeError(tr("Unable to initialize connection"),
QSqlError::ConnectionError, d));
mysql_close(d->mysql);
d->mysql = NULL;
setOpenError(true);
return false;
}
if (connectTimeout>0)
mysql_options(d->mysql, MYSQL_OPT_CONNECT_TIMEOUT , &connectTimeout);
//------------------------------------------------------------------- //
//--------------------------modified----------------------------- //
// if ((d->mysql = mysql_init((MYSQL*) 0)) &&
// mysql_real_connect(d->mysql,
if(mysql_real_connect(d->mysql,
host.isNull() ? static_cast<const char *>(0)
: host.toLocal8Bit().constData(),
user.isNull() ? static_cast<const char *>(0)
: user.toLocal8Bit().constData(),
password.isNull() ? static_cast<const char *>(0)
: password.toLocal8Bit().constData(),
db.isNull() ? static_cast<const char *>(0)
: db.toLocal8Bit().constData(),
(port > -1) ? port : 0,
unixSocket.isNull() ? static_cast<const char *>(0)
: unixSocket.toLocal8Bit().constData(),
optionFlags))
{
if (!db.isEmpty() && mysql_select_db(d->mysql, db.toLocal8Bit().constData())) {
setLastError(qMakeError(tr("Unable to open database '") + db +
QLatin1Char('\''), QSqlError::ConnectionError, d));
mysql_close(d->mysql);
setOpenError(true);
return false;
}
#if MYSQL_VERSION_ID >= 50000
if(reconnect)
mysql_options(d->mysql, MYSQL_OPT_RECONNECT, &reconnect);
#endif
} else {
setLastError(qMakeError(tr("Unable to connect"),
QSqlError::ConnectionError, d));
mysql_close(d->mysql);
d->mysql = NULL;
setOpenError(true);
return false;
}

#if (MYSQL_VERSION_ID >= 40113 && MYSQL_VERSION_ID < 50000) || MYSQL_VERSION_ID >= 50007
// force the communication to be utf8
mysql_set_character_set(d->mysql, "utf8");
#endif
#ifndef QT_NO_TEXTCODEC
d->tc = codec(d->mysql);
#endif

#if MYSQL_VERSION_ID >= 40108
d->preparedQuerysEnabled = mysql_get_client_version() >= 40108
&& mysql_get_server_version(d->mysql) >= 40100;
#else
d->preparedQuerysEnabled = false;
#endif

#ifndef QT_NO_THREAD
mysql_thread_init();
#endif


setOpen(true);
setOpenError(false);
return true;
}

In .../src/plugins/sqldrivers/mysqlmod/main.cpp change the code to


#include <qsqldriverplugin.h>
#include <qstringlist.h>
#include "../../../sql/drivers/mysqlmod/qsql_mysql.h"

QT_BEGIN_NAMESPACE

class QMYSQLmodDriverPlugin : public QSqlDriverPlugin
{
public:
QMYSQLmodDriverPlugin();

QSqlDriver* create(const QString &);
QStringList keys() const;
};

QMYSQLmodDriverPlugin::QMYSQLmodDriverPlugin()
: QSqlDriverPlugin()
{
}

QSqlDriver* QMYSQLmodDriverPlugin::create(const QString &name)
{
if (name == QLatin1String("QMYSQLmod") || name == QLatin1String("QMYSQL3mod")) {
QMYSQLDriver* driver = new QMYSQLDriver();
return driver;
}
return 0;
}

QStringList QMYSQLmodDriverPlugin::keys() const
{
QStringList l;
l << QLatin1String("QMYSQL3mod");
l << QLatin1String("QMYSQLmod");
return l;
}

Q_EXPORT_STATIC_PLUGIN(QMYSQLmodDriverPlugin)
Q_EXPORT_PLUGIN2(qsqlmysql, QMYSQLmodDriverPlugin)

QT_END_NAMESPACE


Rename mysql.pro to mysqlmod.pro and change the code to


TARGET = qsqlmysqlmod

SOURCES = main.cpp
INCLUDEPATH+=/usr/include/mysql
LIBS+=-L/usr/lib -lmysqlclient_r

include(../../../sql/drivers/mysqlmod/qsql_mysql.pri)

include(../qsqldriverbase.pri)

The above INCLUDEPATH and LIBS are the default for ubuntu. I included them here to write less in qmake.
It's time to build the driver plugin as known:
qmake , make and sudo make install.

And a little test to see that the driver is working

#include <QtCore/QCoreApplication>
#include <QtSql>
#include <QDebug>
#include <QElapsedTimer>

int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
qDebug()<<QSqlDatabase::drivers();
QSqlDatabase db=QSqlDatabase::addDatabase("QMYSQLmod");
QElapsedTimer timer;
timer.start();
db.setHostName("www.google.com");
db.setPort(3306);
db.setUserName("someone");
db.setPassword("pass");
db.setConnectOptions("MYSQL_OPT_CONNECT_TIMEOUT=4");
if (!db.open())
qDebug()<<db.lastError().text();
qDebug()<<"time elapsed: "<<timer.elapsed()/1000;

return 0;
}


That's it. I hope I haven't forgot anything.
P.S. Thanks to Lykurg for the SQLCipher wiki. I used it as a guide for this one.