QT & Mysql

 

QT5/Creator connecting to Mysql

From mysql website download driver for C.Extract It and and library in bin folder of QT.

On Ubuntu it is usrlib/qt5/bin.In case of window these will be dll.

Now in your Existing program open project_name.pro file search for

QT       += core gui

add sql at end so that it look like below

QT       += core gui sql

For Purpose of Testing I created a MyUser Table as follows

mysql> show columns from MyUser;
    
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int         | NO   | PRI | NULL    | auto_increment |
| shortName | varchar(50) | YES  |     | NULL    |                |
| tel       | varchar(20) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Now on UI i created two textboxes one for name & other for telephone.


 
 
Code for mainWindow.cpp

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QMessageBox>
#include <QtSql>
#include <QSqlDatabase>
MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);
}
MainWindow::~MainWindow()
{
    delete ui;
}
void ShowMsg(QString strMsg)
{
    QMessageBox msgBox;
    msgBox.setText(strMsg);
    msgBox.exec();
}
void MainWindow::on_pbSubmit_clicked()
{
    QSqlDatabase db;
    db = QSqlDatabase::addDatabase("QMYSQL","MyConnect");
    db.setHostName("localhost");
    db.setUserName("root");
    db.setPassword("sangram");
    db.setDatabaseName("sangram");
   QString name = ui->leName->text();
   QString  tel = ui->leTel->text();
    if (db.open()) {
        QMessageBox::information(this,"Connection","Connection Success");
        QSqlQuery query(QSqlDatabase::database("MyConnect"));
        query.prepare("INSERT INTO MyUser(shortName,tel)values(:shortName,:telephone)");
        query.bindValue(":shortName",name);
        query.bindValue(":telephone",tel);
         if(query.exec())
        {
                QMessageBox::information(this,"Insertion","Insertion Success");
        }else
        {
                QMessageBox::information(this,"Insertion","Insertion failed");
        }
    }else{
        QMessageBox::information(this,"Connection","Connection failed");
    }
}

Here code block below do connectivity to mysql

      QSqlDatabase db;

    db = QSqlDatabase::addDatabase("QMYSQL","MyConnect");
    db.setHostName("localhost");
    db.setUserName("root");
    db.setPassword("sangram");
    db.setDatabaseName("sangram");

 MyConnect is connection name,QMYSQL is driver for mysql.
LeName & leTel are two lineEdit means textboxes on form whose values used to insert into database.

In below Code we are building our query of insert

        QSqlQuery query(QSqlDatabase::database("MyConnect"));
        query.prepare("INSERT INTO MyUser(shortName,tel)values(:shortName,:telephone)");
        query.bindValue(":shortName",name);
        query.bindValue(":telephone",tel);

query.exec() execute sql query and return true or false.

Here Messagebox is shown for success & failure of connection & query execution. As follows.

QMessageBox::information(this,"Connection","Connection failed"); 

Code for project is available at https://github.com/gitsangramdesai/qt-mysql