How to quickly check if a database is available? (Qt, QML, C ++) - Linux
I am using qt with qml and c ++. In my application, I am using a database. It all works if the database is reachable.
My problem is that I would like to check if the database is available (e.g. ping).
I tried
db.setDatabaseName(dsn);
if(db.isValid())
{
if(db.open())
{
//std::cout <<"Offene Datenbank";
connected=true;
}
else
{
connected=false;
}
}
else
{
connected=false;
}
and the result is the associated value. But it takes a very long time (maybe 30 seconds) if there is no connection. How can I check quickly if I have a database connection?
Could there be a way to break the .open command after 5 seconds not connected?
source to share
I did some research on this question. Here's what I learned.
The problem is db's default connection timeout - it's too long. Each db allows you to change it to an acceptable value using its own API. Qt has one common db interface - QSqlDatabase
. And it doesn't have such a method. You can set connection parameters by calling the method QSqlDatabase::setConnectOptions
, but it only accepts a predefined list of parameters (which you can read in the Qt help).
An PostgreSQL
option exists for connect_timeout
, so you can write:
db.setConnectOptions("connect_timeout=5"); // Set to 5 seconds
There is no such parameter for other databases. The connection parameters of each db are parsed in the "driver" class, which is output QSqlDriver
and stored in the "driver" library.
So what can you do:
- You can rewrite your database driver to accept a timeout parameter.
- You can write separate code for each db using its own API.
UPDATE
It turns out to ODBC
have a parameter SQL_ATTR_CONNECTION_TIMEOUT
.
UPDATE 2
qsql_odbc.cpp: 713
} else if (opt.toUpper() == QLatin1String("SQL_ATTR_CONNECTION_TIMEOUT")) {
v = val.toUInt();
r = SQLSetConnectAttr(hDbc, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER) v, 0);
https://msdn.microsoft.com/en-us/library/ms713605(v=vs.85).aspx
SQL_ATTR_CONNECTION_TIMEOUT (ODBC 3.0)
The SQLUINTEGER value corresponding to the number of seconds to wait for any connection request to complete before returning to the application. The driver SHOULD return SQLSTATE HYT00 (timed out) any time it might time out in a situation unrelated to the execution or logon request.
If ValuePtr is 0 (default), there is no timeout.
Should work fine ...
source to share
I suggest having some separate thread / class where you check the connection and emit a signal after some timeout if nothing happens (with check - knowConnection - if we found out already if it is connected). This code is untested and written from scratch on my head .. may contain some bugs.
/// db connection validator in separate thread
void validator::doValidate() {
this->knowConnection = false;
db.setDatabaseName(dsn);
if(db.isValid())
{
QTimer::singleShot(1000, [this]() {
if (!this->knowConnection) {
emit connected(false);dm->connected=false;
}
});
if(db.open())
{
//std::cout <<"Offene Datenbank";
this->knowConnection = true;
dm->connected=true;
emit connected(true);
}
else
{
dm->connected=false;
this->knowConnection = true;
emit connected(false);
}
}
else
{
dm->connected=false;
this->knowConnection = true;
emit connected(false);
}
}
/// db manager in different thread
void dm::someDbFunction() {
if (connected) {
/// db logic
}
}
/// in gui or whatever
MainWindow::MainWindow() : whatever, val(new validator(..), .. {
connect(val, SIGNAL(connected(bool)), this, SLOT(statusSlot(bool));
....
}
void MainWindow::statusSlot(bool connected) {
ui->statusBar->setText((connected?"Connected":"Disconnected"));
}
source to share