Results 1 to 7 of 7

Thread: QSqlDatabase connection timeout?

  1. #1
    Join Date
    Nov 2010
    Posts
    14
    Thanks
    1
    Thanked 1 Time in 1 Post
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default QSqlDatabase connection timeout?

    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

  2. #2
    Join Date
    Aug 2009
    Location
    Greece
    Posts
    69
    Thanks
    2
    Thanked 14 Times in 14 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlDatabase connection timeout?

    What you are looking for is in QSqlDatabase
    The options are database specific. For SQLite the busy timeout is "QSQLITE_BUSY_TIMEOUT"

  3. #3
    Join Date
    Nov 2010
    Posts
    14
    Thanks
    1
    Thanked 1 Time in 1 Post
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlDatabase connection timeout?

    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:


    Qt Code:
    1. QString timeout;
    2. DB.driver();
    3. QString driver = DB.driverName();
    4. if(driver=="QMYSQL"){
    5. timeout = "MYSQL_OPT_CONNECT_TIMEOUT";
    6. }
    7. else if(driver=="QDB2"){
    8. timeout="SQL_ATTR_LOGIN_TIMEOUT";
    9. }
    10. else if(driver=="QPSQL"){
    11. timeout="connect_timeout";
    12. }
    13. else if(driver.startsWith("QSQLITE")){
    14. timeout="QSQLITE_BUSY_TIMEOUT";
    15. }
    16. else if(driver == "QOCI"){
    17. timeout="Connection Timeout";
    18. }
    19. else if(driver == "QTDS"){
    20. timeout="TimeOut";
    21. }
    22. else{ //Q ODBC (open database connector (vendria a ser el general).
    23. timeout="SQL_ATTR_CONNECTION_TIMEOUT";
    24. }
    25.  
    26. if(timeout!=""){
    27. DB.setConnectOptions(timeout+"="+QString::number(secs));
    28. }
    To copy to clipboard, switch view to plain text mode 

    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:pen: Illegal connect option value 'MYSQL_OPT_CONNECT_TIMEOUT=3'

  4. The following user says thank you to joseprl89 for this useful post:

    emobemo (20th January 2016)

  5. #4
    Join Date
    Aug 2009
    Location
    coimbatore,India
    Posts
    314
    Thanks
    37
    Thanked 47 Times in 43 Posts
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: QSqlDatabase connection timeout?

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

  6. #5
    Join Date
    Nov 2010
    Posts
    14
    Thanks
    1
    Thanked 1 Time in 1 Post
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlDatabase connection timeout?

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

  7. #6
    Join Date
    Aug 2009
    Location
    Greece
    Posts
    69
    Thanks
    2
    Thanked 14 Times in 14 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlDatabase connection timeout?

    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.

  8. #7
    Join Date
    Aug 2009
    Location
    Greece
    Posts
    69
    Thanks
    2
    Thanked 14 Times in 14 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlDatabase connection timeout?

    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] never understood it completely"
    So let's create copies of the folders that we will need.
    Qt Code:
    1. cp -R $QTDIR/src/plugins/sqldrivers/mysql/ $QTDIR/src/plugin/sqldrivers/mysqlmod/
    2. cp -R $QTDIR/src/sql/drivers/mysql/ $QTDIR/src/sql/sqldrivers/mysqlmod/
    To copy to clipboard, switch view to plain text mode 

    Inside .../sql/drivers/mysqlmod open the qsql_mysql.cpp and
    a)replace the include just before QT_BEGIN_NAMESPACE
    Qt Code:
    1. #include "../../../sql/drivers/mysqlmod/qsql_mysql.h"
    To copy to clipboard, switch view to plain text mode 
    b)replace the void QMYSQLDriver::open with the following..
    Qt Code:
    1. bool QMYSQLDriver::open(const QString& db,
    2. const QString& user,
    3. const QString& password,
    4. const QString& host,
    5. int port,
    6. const QString& connOpts)
    7. {
    8. if (isOpen())
    9. close();
    10.  
    11. /* This is a hack to get MySQL's stored procedure support working.
    12.   Since a stored procedure _may_ return multiple result sets,
    13.   we have to enable CLIEN_MULTI_STATEMENTS here, otherwise _any_
    14.   stored procedure call will fail.
    15.   */
    16. unsigned int optionFlags = Q_CLIENT_MULTI_STATEMENTS;
    17. const QStringList opts(connOpts.split(QLatin1Char(';'), QString::SkipEmptyParts));
    18. QString unixSocket;
    19. // -------------------code added here----------------------- //
    20. unsigned int connectTimeout=0;
    21. //------------------------------------------------------------------- //
    22. #if MYSQL_VERSION_ID >= 50000
    23. my_bool reconnect=false;
    24. #endif
    25.  
    26. // extract the real options from the string
    27. for (int i = 0; i < opts.count(); ++i) {
    28. QString tmp(opts.at(i).simplified());
    29. int idx;
    30. if ((idx = tmp.indexOf(QLatin1Char('='))) != -1) {
    31. QString val = tmp.mid(idx + 1).simplified();
    32. QString opt = tmp.left(idx).simplified();
    33. if (opt == QLatin1String("UNIX_SOCKET"))
    34. unixSocket = val;
    35. // -------------------code added here----------------------- //
    36. else if( opt == QLatin1String("MYSQL_OPT_CONNECT_TIMEOUT"))
    37. {
    38. bool ok;
    39. connectTimeout=val.toUInt(&ok);
    40. if (!ok) {
    41. connectTimeout=0;
    42. qWarning("QMYSQLmodDriver Invalid timeout value %s", val.toLocal8Bit().constData());
    43. }
    44. }
    45. //------------------------------------------------------------------- //
    46. #if MYSQL_VERSION_ID >= 50000
    47. else if (opt == QLatin1String("MYSQL_OPT_RECONNECT")) {
    48. if (val == QLatin1String("TRUE") || val == QLatin1String("1") || val.isEmpty())
    49. reconnect = true;
    50. }
    51. #endif
    52. else if (val == QLatin1String("TRUE") || val == QLatin1String("1"))
    53. setOptionFlag(optionFlags, tmp.left(idx).simplified());
    54. else
    55. qWarning("QMYSQLDriver::open: Illegal connect option value '%s'",
    56. tmp.toLocal8Bit().constData());
    57. } else {
    58. setOptionFlag(optionFlags, tmp);
    59. }
    60. }
    61. // -------------------code added here----------------------- //
    62. if (!(d->mysql = mysql_init((MYSQL*) 0)))
    63. {
    64. setLastError(qMakeError(tr("Unable to initialize connection"),
    65. QSqlError::ConnectionError, d));
    66. mysql_close(d->mysql);
    67. d->mysql = NULL;
    68. setOpenError(true);
    69. return false;
    70. }
    71. if (connectTimeout>0)
    72. mysql_options(d->mysql, MYSQL_OPT_CONNECT_TIMEOUT , &connectTimeout);
    73. //------------------------------------------------------------------- //
    74. //--------------------------modified----------------------------- //
    75. // if ((d->mysql = mysql_init((MYSQL*) 0)) &&
    76. // mysql_real_connect(d->mysql,
    77. if(mysql_real_connect(d->mysql,
    78. host.isNull() ? static_cast<const char *>(0)
    79. : host.toLocal8Bit().constData(),
    80. user.isNull() ? static_cast<const char *>(0)
    81. : user.toLocal8Bit().constData(),
    82. password.isNull() ? static_cast<const char *>(0)
    83. : password.toLocal8Bit().constData(),
    84. db.isNull() ? static_cast<const char *>(0)
    85. : db.toLocal8Bit().constData(),
    86. (port > -1) ? port : 0,
    87. unixSocket.isNull() ? static_cast<const char *>(0)
    88. : unixSocket.toLocal8Bit().constData(),
    89. optionFlags))
    90. {
    91. if (!db.isEmpty() && mysql_select_db(d->mysql, db.toLocal8Bit().constData())) {
    92. setLastError(qMakeError(tr("Unable to open database '") + db +
    93. QLatin1Char('\''), QSqlError::ConnectionError, d));
    94. mysql_close(d->mysql);
    95. setOpenError(true);
    96. return false;
    97. }
    98. #if MYSQL_VERSION_ID >= 50000
    99. if(reconnect)
    100. mysql_options(d->mysql, MYSQL_OPT_RECONNECT, &reconnect);
    101. #endif
    102. } else {
    103. setLastError(qMakeError(tr("Unable to connect"),
    104. QSqlError::ConnectionError, d));
    105. mysql_close(d->mysql);
    106. d->mysql = NULL;
    107. setOpenError(true);
    108. return false;
    109. }
    110.  
    111. #if (MYSQL_VERSION_ID >= 40113 && MYSQL_VERSION_ID < 50000) || MYSQL_VERSION_ID >= 50007
    112. // force the communication to be utf8
    113. mysql_set_character_set(d->mysql, "utf8");
    114. #endif
    115. #ifndef QT_NO_TEXTCODEC
    116. d->tc = codec(d->mysql);
    117. #endif
    118.  
    119. #if MYSQL_VERSION_ID >= 40108
    120. d->preparedQuerysEnabled = mysql_get_client_version() >= 40108
    121. && mysql_get_server_version(d->mysql) >= 40100;
    122. #else
    123. d->preparedQuerysEnabled = false;
    124. #endif
    125.  
    126. #ifndef QT_NO_THREAD
    127. mysql_thread_init();
    128. #endif
    129.  
    130.  
    131. setOpen(true);
    132. setOpenError(false);
    133. return true;
    134. }
    To copy to clipboard, switch view to plain text mode 
    In .../src/plugins/sqldrivers/mysqlmod/main.cpp change the code to
    Qt Code:
    1. #include <qsqldriverplugin.h>
    2. #include <qstringlist.h>
    3. #include "../../../sql/drivers/mysqlmod/qsql_mysql.h"
    4.  
    5. QT_BEGIN_NAMESPACE
    6.  
    7. class QMYSQLmodDriverPlugin : public QSqlDriverPlugin
    8. {
    9. public:
    10. QMYSQLmodDriverPlugin();
    11.  
    12. QSqlDriver* create(const QString &);
    13. QStringList keys() const;
    14. };
    15.  
    16. QMYSQLmodDriverPlugin::QMYSQLmodDriverPlugin()
    17. {
    18. }
    19.  
    20. QSqlDriver* QMYSQLmodDriverPlugin::create(const QString &name)
    21. {
    22. if (name == QLatin1String("QMYSQLmod") || name == QLatin1String("QMYSQL3mod")) {
    23. QMYSQLDriver* driver = new QMYSQLDriver();
    24. return driver;
    25. }
    26. return 0;
    27. }
    28.  
    29. QStringList QMYSQLmodDriverPlugin::keys() const
    30. {
    31. l << QLatin1String("QMYSQL3mod");
    32. l << QLatin1String("QMYSQLmod");
    33. return l;
    34. }
    35.  
    36. Q_EXPORT_STATIC_PLUGIN(QMYSQLmodDriverPlugin)
    37. Q_EXPORT_PLUGIN2(qsqlmysql, QMYSQLmodDriverPlugin)
    38.  
    39. QT_END_NAMESPACE
    To copy to clipboard, switch view to plain text mode 

    Rename mysql.pro to mysqlmod.pro and change the code to
    Qt Code:
    1. TARGET = qsqlmysqlmod
    2.  
    3. SOURCES = main.cpp
    4. INCLUDEPATH+=/usr/include/mysql
    5. LIBS+=-L/usr/lib -lmysqlclient_r
    6.  
    7. include(../../../sql/drivers/mysqlmod/qsql_mysql.pri)
    8.  
    9. include(../qsqldriverbase.pri)
    To copy to clipboard, switch view to plain text mode 
    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
    Qt Code:
    1. #include <QtCore/QCoreApplication>
    2. #include <QtSql>
    3. #include <QDebug>
    4. #include <QElapsedTimer>
    5.  
    6. int main(int argc, char *argv[])
    7. {
    8. QCoreApplication a(argc, argv);
    9. qDebug()<<QSqlDatabase::drivers();
    10. QSqlDatabase db=QSqlDatabase::addDatabase("QMYSQLmod");
    11. QElapsedTimer timer;
    12. timer.start();
    13. db.setHostName("www.google.com");
    14. db.setPort(3306);
    15. db.setUserName("someone");
    16. db.setPassword("pass");
    17. db.setConnectOptions("MYSQL_OPT_CONNECT_TIMEOUT=4");
    18. if (!db.open())
    19. qDebug()<<db.lastError().text();
    20. qDebug()<<"time elapsed: "<<timer.elapsed()/1000;
    21.  
    22. return 0;
    23. }
    To copy to clipboard, switch view to plain text mode 

    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.
    Last edited by Rhayader; 27th March 2011 at 03:52. Reason: syntax

Similar Threads

  1. QSqlDatabase Connection Close on Destruction
    By Sanuden in forum Qt Programming
    Replies: 1
    Last Post: 1st September 2011, 16:32
  2. QSqlDatabase PSQL connection options
    By leknarf in forum Qt Programming
    Replies: 0
    Last Post: 17th March 2010, 17:06
  3. Replies: 1
    Last Post: 16th March 2010, 16:46
  4. QSqlDatabase Mysql timeout versus PortScanner
    By patrik08 in forum Qt Programming
    Replies: 1
    Last Post: 25th May 2008, 01:08
  5. Replies: 3
    Last Post: 22nd June 2006, 17:27

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.