MySQL Archives | LnRSoft

QSqlDatabase connection testing tool

By Roland Ihasz / 28th August 2017 16th May 2018

QSqlDatabase connection access

A simple command line tool to test connection between Qt client applications and a remote Mysql Server. The code includes Secure Sockets Layer (SSL) support to test secure connection. The QSqlDatabase class provides an interface for accessing a database through a connection. An instance of QSqlDatabase represents the connection. The connection provides access to the database via one of the supported database drivers (MySQL in our case), which are derived from QSqlDriver. Alternatively, we can subclass your own database driver from QSqlDriver. I’ll post an another article about how to write your own database driver in Qt for Unix, OSX and Windows platforms. It’s recommend to write your own database driver especially when you planning to deploy your application in different platforms. It’s simply because there could be surprises especially on client deploying your application that using one of the supported database drivers due to missing dependencies or environmental variables.

Software prerequisites:
Remote MySQL server version: 5.7 or above
Qt version: Qt_5_7_1 or above
OpenSSL 1.0.x or above the for SSL feature

How to use it:
1. I simply run the mysql_conn_test_database.sql SQL script file to create a test database on the remote server.
2. Set the hostname or IP, port number, username and password. You migth need to create a new database user for this purpose.
[You will need to create SSL certificates for the database server and client in order to test SSL Feature on the MySQL Server.]
3. If everthing is set just simple build and run the project in your QtCreator.

If everything goes well you will recieve the following feedback from your terminal:

QSqlDatabase connection test tool was created and written by Roland Ihasz and it is licensed under the GNU General Public License (GPL) Version 2 or above.

Check out on GitHub. https://github.com/lnrsoft/QSqlDatabase-connection-test-tool

mysql_db_connection_test.pro

# (c)  Roland Ihasz - https://github.com/lnrsoft   

QT += core
QT -= gui
QT += sql
QT += sql widgets

CONFIG += c++11
CONFIG += console       
#CONFIG -= console      
CONFIG -= app_bundle

TEMPLATE = app

TARGET = mysql_db_connection_test

SOURCES += main.cpp

QTPLUGIN += sqldrivers

QMAKE_CXXFLAGS += -std=c++11

# (c) Roland Ihasz - https://github.com/lnrsoft

QT += core

QT -= gui

QT += sql

QT += sql widgets

CONFIG += c++11

CONFIG += console

#CONFIG -= console

CONFIG -= app_bundle

TEMPLATE = app

TARGET = mysql_db_connection_test

SOURCES += main.cpp

QTPLUGIN += sqldrivers

QMAKE_CXXFLAGS += -std=c++11

main.cpp

// (c)  Roland Ihasz - https://github.com/lnrsoft

#include <QSqlDatabase>
#include <QApplication>
#include <QSqlRecord>
#include <QSqlError>
#include <QSqlQuery>
#include <QDebug>
#include <QtCore>
#include <QtSql>

int main(int argc, char* argv[])
{
    QApplication a(argc, argv);
    {
        QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
        db.setHostName("192.168.1.100");        // your host or ip name
        db.setPort(3306);                       // port number
        db.setDatabaseName("mysql_conn_test");  // db name
        db.setUserName("username");             // db username
        db.setPassword("password");             // db password
        //----------Additional-SSL-Feature-for-secure-database-connection----------
        //  db.setConnectOptions(
        //    "SSL_KEY=/Users/lnrsoft/repository/mysql_db_conn_test/client-key.pem;"
        //    "SSL_CERT=/Users/lnrsoft/repository/mysql_db_conn_test/client-cert.pem;"
        //    "SSL_CA=/Users/lnrsoft/repository/mysql_db_conn_test/ca-cert.pem;"
        //    "SSL_CIPHER=DHE-RSA-AES256-SHA;");
        //----------Additional-SSL-Feature-for-secure-database-connection----------
        if(!db.open()) {
            qDebug() << "\nDATABASE CONNECTION UNSUCCESSFUL."
                     << "\nlastError: " << db.lastError().text() <<"\n";
            return 0;
        }
        if(db.open()) {
            qDebug() << "\nSUCCESSFULLY CONNECTED TO THE DATABASE SERVER.";
            QSqlQuery query(
                "SELECT "
                "dataTable.ID, "
                "dataTable.x1, "
                "dataTable.y1, "
                "dataTable.z1, "
                "dataTable.t "
                "FROM mysql_conn_test.dataTable");
            QSqlRecord rec1 = query.record();
            QSqlRecord rec2 = query.record();
            QSqlRecord rec3 = query.record();
            QSqlRecord rec4 = query.record();
            QSqlRecord rec5 = query.record();
            qDebug() << "NUMBER OF ROWS: " << query.size();   // Return number of rows
            int productCode1 = rec1.indexOf("ID");
            int productCode2 = rec2.indexOf("x1");
            int productCode3 = rec3.indexOf("y1");
            int productCode4 = rec4.indexOf("z1");
            int productCode5 = rec5.indexOf("t");
            qDebug() << "====================================================================================";
            while(query.next()) {
                qDebug() << "ROW NUMBER"
                         << query.value(productCode1).toString() << ""
                         << query.value(productCode2).toString() << ""
                         << query.value(productCode3).toString() << ""
                         << query.value(productCode4).toString() << ""
                         << query.value(productCode5).toString();
            }
            qDebug() << "====================================================================================";
            qDebug() << "COMPLETE...";
            return 0;
        }
    }
}

// (c) Roland Ihasz - https://github.com/lnrsoft

#include <QSqlDatabase>

#include <QApplication>

#include <QSqlRecord>

#include <QSqlError>

#include <QSqlQuery>

#include <QDebug>

#include <QtCore>

#include <QtSql>

int main(int argc, char* argv[])

{

QApplication a(argc, argv);

{

QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");

db.setHostName("192.168.1.100"); // your host or ip name

db.setPort(3306); // port number

db.setDatabaseName("mysql_conn_test"); // db name

db.setUserName("username"); // db username

db.setPassword("password"); // db password

//----------Additional-SSL-Feature-for-secure-database-connection----------

// db.setConnectOptions(

// "SSL_KEY=/Users/lnrsoft/repository/mysql_db_conn_test/client-key.pem;"

// "SSL_CERT=/Users/lnrsoft/repository/mysql_db_conn_test/client-cert.pem;"

// "SSL_CA=/Users/lnrsoft/repository/mysql_db_conn_test/ca-cert.pem;"

// "SSL_CIPHER=DHE-RSA-AES256-SHA;");

//----------Additional-SSL-Feature-for-secure-database-connection----------

if(!db.open()) {

qDebug() << "\nDATABASE CONNECTION UNSUCCESSFUL."

<< "\nlastError: " << db.lastError().text() <<"\n";

return 0;

}

if(db.open()) {

qDebug() << "\nSUCCESSFULLY CONNECTED TO THE DATABASE SERVER.";

QSqlQuery query(

"SELECT "

"dataTable.ID, "

"dataTable.x1, "

"dataTable.y1, "

"dataTable.z1, "

"dataTable.t "

"FROM mysql_conn_test.dataTable");

QSqlRecord rec1 = query.record();

QSqlRecord rec2 = query.record();

QSqlRecord rec3 = query.record();

QSqlRecord rec4 = query.record();

QSqlRecord rec5 = query.record();

qDebug() << "NUMBER OF ROWS: " << query.size(); // Return number of rows

int productCode1 = rec1.indexOf("ID");

int productCode2 = rec2.indexOf("x1");

int productCode3 = rec3.indexOf("y1");

int productCode4 = rec4.indexOf("z1");

int productCode5 = rec5.indexOf("t");

qDebug() << "====================================================================================";

while(query.next()) {

qDebug() << "ROW NUMBER"

<< query.value(productCode1).toString() << ""

<< query.value(productCode2).toString() << ""

<< query.value(productCode3).toString() << ""

<< query.value(productCode4).toString() << ""

<< query.value(productCode5).toString();

}

qDebug() << "====================================================================================";

qDebug() << "COMPLETE...";

return 0;

}

mysql_conn_test_database.sql

/*	(c)  Roland Ihasz - https://github.com/lnrsoft	*/

DROP DATABASE IF EXISTS `mysql_conn_test`;

CREATE DATABASE IF NOT EXISTS `mysql_conn_test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `mysql_conn_test`;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

DROP TABLE IF EXISTS `dataTable`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dataTable` (
  `ID` int(1) NOT NULL,
  `x1` double NOT NULL,
  `y1` double NOT NULL,
  `z1` double NOT NULL,
  `t` int(16) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES `dataTable` WRITE;
/*!40000 ALTER TABLE `dataTable` DISABLE KEYS */;
INSERT INTO `dataTable` VALUES (1,0.3352925219759345,0.336742962207645,0.3117983819916844,2),(2,0.3351856799423695,0.334428142439574,0.3127960397861898,5),(3,0.3592007360234857,0.314406356215476,0.3792905477248132,9);
/*!40000 ALTER TABLE `dataTable` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

/* (c) Roland Ihasz - https://github.com/lnrsoft */

DROP DATABASE IF EXISTS `mysql_conn_test`;

CREATE DATABASE IF NOT EXISTS `mysql_conn_test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `mysql_conn_test`;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

DROP TABLE IF EXISTS `dataTable`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `dataTable` (

`ID` int(1) NOT NULL,

`x1` double NOT NULL,

`y1` double NOT NULL,

`z1` double NOT NULL,

`t` int(16) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES `dataTable` WRITE;

/*!40000 ALTER TABLE `dataTable` DISABLE KEYS */;

INSERT INTO `dataTable` VALUES (1,0.3352925219759345,0.336742962207645,0.3117983819916844,2),(2,0.3351856799423695,0.334428142439574,0.3127960397861898,5),(3,0.3592007360234857,0.314406356215476,0.3792905477248132,9);

/*!40000 ALTER TABLE `dataTable` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Alternatively, use the following shorter source code to check the connection without any sql query.
QSqlDatabase connection test tool without query

// (c)  Roland Ihasz - https://github.com/lnrsoft   

#include <QCoreApplication>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QDebug>
#include <QDebug>
#include <QtCore>
#include <QtSql>

int main(int argc, char* argv[])
{
    QCoreApplication a(argc, argv);
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("192.168.1.100");        // host or ip name
    db.setPort(3306);                       // port number
    db.setDatabaseName("mysql_conn_test");  // db name
    db.setUserName("username");             // db username
    db.setPassword("password");             // db password
    if(!db.open()) {
        qDebug() << "\nDATABASE CONNECTION UNSUCCESSFUL."
                 << "\nlastError: " << db.lastError().text() <<"\n";
        return 0;
    }
    if(db.open()) {
        qDebug() << "\nSUCCESSFULLY CONNECTED TO THE DATABASE SERVER.\n";
        return 0;
    }
}

// (c) Roland Ihasz - https://github.com/lnrsoft

#include <QCoreApplication>

#include <QSqlDatabase>

#include <QSqlError>

#include <QSqlQuery>

#include <QDebug>

#include <QtCore>

#include <QtSql>

int main(int argc, char* argv[])

{

QCoreApplication a(argc, argv);

QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");

db.setHostName("192.168.1.100"); // host or ip name

db.setPort(3306); // port number

db.setDatabaseName("mysql_conn_test"); // db name

db.setUserName("username"); // db username

db.setPassword("password"); // db password

if(!db.open()) {

qDebug() << "\nDATABASE CONNECTION UNSUCCESSFUL."

<< "\nlastError: " << db.lastError().text() <<"\n";

return 0;

}

if(db.open()) {

qDebug() << "\nSUCCESSFULLY CONNECTED TO THE DATABASE SERVER.\n";

return 0;

}