Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

How to Read From sql Database



  • Hi;
    I Have written a code to read values from a slave Using Modbus, show the data on UI and store them in a table in database. here is the code and works good:

    #include "mainwindow.h"
    #include "ui_mainwindow.h"
    #include <QModbusDevice>
    #include <QModbusTcpClient>
    #include <QModbusRtuSerialMaster>
    #include <QSerialPort>
    #include <QtDebug>
    #include <QStatusBar>
    
    #include <QCoreApplication>
    #include <QtSql>
    #include <QSqlQuery>
    
    
    MainWindow::MainWindow(QWidget *parent)  :
        QMainWindow(parent),
        ui(new Ui::MainWindow),
        modbusDevice(nullptr)
    {
        ui->setupUi(this);
        modbusDevice = new QModbusRtuSerialMaster(this);
        ui->Port_lineEdit->setText("COM4");
        ui->Slave_Address_lineEdit->setText("5");
    
        db.setHostName("ARETA-104");
        db.setDatabaseName("MY32DSN");
    
    }
    
    MainWindow::~MainWindow()
    {
        if (modbusDevice)
            modbusDevice->disconnectDevice();
        delete modbusDevice;
        delete ui;
    }
    
    
    
    void MainWindow::on_Connect_pushButton_clicked()
    {
        if(!modbusDevice)
        {
            statusBar()->showMessage("No modbus Device object created");
            return;
        }
        else
        {
           statusBar()->showMessage("Created");
        }
        modbusDevice->setConnectionParameter(QModbusDevice::SerialPortNameParameter,ui->Port_lineEdit->text());
        modbusDevice->setConnectionParameter(QModbusDevice::SerialParityParameter,QSerialPort::NoParity);
    
        modbusDevice->setConnectionParameter(QModbusDevice::SerialBaudRateParameter,QSerialPort::Baud9600);
        modbusDevice->setConnectionParameter(QModbusDevice::SerialDataBitsParameter,QSerialPort::Data8);
         modbusDevice->setConnectionParameter(QModbusDevice::SerialStopBitsParameter,QSerialPort::OneStop);
        if(modbusDevice->connectDevice())
        {
            statusBar()->showMessage("OK");
            ui->Connect_pushButton->setDisabled(1);
            ui->Disconnect_pushButton->setDisabled(0);
    
        }
        else
            statusBar()->showMessage("Fail");
        qDebug()<<modbusDevice->errorString();
    }
    
    void MainWindow::on_Disconnect_pushButton_clicked()
    {
        modbusDevice->disconnectDevice();
        ui->Disconnect_pushButton->setDisabled(1);
         ui->Connect_pushButton->setDisabled(0);
         qDebug()<<modbusDevice->error();
    
    }
    
    void MainWindow::on_GetState_pushButton_clicked()
    {
    
        switch (modbusDevice->state()) {
    
            case QModbusDevice::ConnectedState :
            qDebug()<<"connected";
            break;
    
            case QModbusDevice::UnconnectedState :
            qDebug()<<"Unconnected";
            break;
    
            case QModbusDevice::ConnectingState :
            qDebug()<<"ConnectingState";
            break;
    
            case QModbusDevice::ClosingState :
            qDebug()<<"ClosingState";
            break;
    
        }
    
    }
    
    void MainWindow::on_Read_pushButton_clicked()
    {
        if (!modbusDevice)
            return;
        ui->Read_Result_listWidget->clear();
        statusBar()->clearMessage();
         if (auto *reply = modbusDevice->sendReadRequest(readRequest(), ui->Slave_Address_lineEdit->text().toInt())) {
             if (!reply->isFinished())
                 connect(reply, &QModbusReply::finished, this, &MainWindow::readReady);
         }
    }
    
    QModbusDataUnit MainWindow::readRequest() const
    {
        return QModbusDataUnit(QModbusDataUnit::HoldingRegisters,0,18);
    }
    
    void MainWindow::readReady()
    {
        auto reply = qobject_cast<QModbusReply *>(sender());
        if (!reply)
            return;
        //
        if(!(db.open()))
            return;
        qDebug()<<"is Open!!";
        QSqlQuery qry;
        qry.exec("use tre");
        qry.exec("DROP TABLE slave_data");
        if(qry.exec("CREATE TABLE slave_data (address int, value text)"))
        {
           qDebug()<<"done1" ;
        }
    
        if (reply->error() == QModbusDevice::NoError) {
            const QModbusDataUnit unit = reply->result();
            for (uint i = 0; i < unit.valueCount(); i++) {
                const QString entry = tr("Address: %1, Value: %2").arg(unit.startAddress() + i)
                                         .arg(QString::number(unit.value(i),
                                              unit.registerType() <= QModbusDataUnit::Coils ? 10 : 16));
    
                ui->Read_Result_listWidget->addItem(entry);
           const QString database_input = tr("INSERT INTO slave_data (address, value) VALUES (%1, '%2')").arg(unit.startAddress() + i).arg(QString::number(unit.value(i), unit.registerType() <= QModbusDataUnit::Coils ? 10 : 16));
           qDebug()<<database_input;
          if(!qry.exec(database_input))
              qDebug()<<tr("err%1").arg(i);
            }
            qry.exec("SELECT value FROM slave_data");
            while(qry.next())
          qDebug()<<"read Result:" << qry.value(0);
    }
    
            else if (reply->error() == QModbusDevice::ProtocolError) {
                statusBar()->showMessage(tr("Read response error: %1 (Mobus exception: 0x%2)").
                                            arg(reply->errorString()).
                                            arg(reply->rawResult().exceptionCode(), -1, 16), 5000);
            } else {
                statusBar()->showMessage(tr("Read response error: %1 (code: 0x%2)").
                                            arg(reply->errorString()).
                                            arg(reply->error(), -1, 16), 5000);
            }
    
            reply->deleteLater();
    }
    
    

    NOW I want to read some information from database and send to the slave but dont know how to read from database. can any one help me?



  • @rezaMSLM said in How to Read From sql Database:

    but dont know how to read from database. can any one help me?

    http://doc.qt.io/qt-5/qsqlquery.html#details



  • @VRonin said in How to Read From sql Database:

    @rezaMSLM said in How to Read From sql Database:

    but dont know how to read from database. can any one help me?

    http://doc.qt.io/qt-5/qsqlquery.html#details

    Thank You
    I use this code and works good:

    void MainWindow::on_READ_pushButton_clicked()
    {
        if(!(db.open()))
        {
            qDebug()<<"database isn't Open";
            return;
        }
        qDebug()<<"Opened!";
        QUERY.exec("use tre");
       if (QUERY.exec("SELECT address, value FROM slave_data"))
           qDebug()<<"selected";
        while (QUERY.next()) {
       QString Values = QUERY.value("value").toString();
       qDebug()<<Values;
        }
    }
    

    BUT the code gives me the hole values of a column. (my database name is slave_data and has two columns: address, value
    and 17 rows.)

    how to read a certain data for example row 12th of column value?


  • Qt Champions 2019

    @rezaMSLM said in How to Read From sql Database:

    how to read a certain data for example row 12th of column value?

    I guess you mean row 12?
    Usually you read row/rows identified by some condition.
    Why do you want to read a row based on it index in the database?


  • Lifetime Qt Champion

    @rezaMSLM
    Hi
    You control that via the SQL staements
    the line, you use
    QUERY.exec("SELECT address, value FROM slave_data")
    could use the WHERE clause to specify some condition
    https://www.w3schools.com/sql/sql_where.asp

    In your case, address seems likely to be a candidate to use for the check.



  • @jsulm said in How to Read From sql Database:

    Why do you want to read a row based on it index in the database?

    I want to read column data based on their indexes to access them individually.

    @mrjj said in How to Read From sql Database:

    @rezaMSLM
    Hi
    You control that via the SQL staements
    the line, you use
    QUERY.exec("SELECT address, value FROM slave_data")
    could use the WHERE clause to specify some condition
    https://www.w3schools.com/sql/sql_where.asp

    In your case, address seems likely to be a candidate to use for the check.

    THANKS



  • one Question:
    when I comment the line:

    while (QUERY.next())
    

    this code:

        QUERY.exec("use tre");
       if (QUERY.exec("SELECT address, value FROM slave_data WHERE address=3"))
           qDebug()<<"selected";
       QString Values = QUERY.value("value").toString();
       qDebug()<<Values;
    

    I have no output.
    why?


  • Lifetime Qt Champion

    @rezaMSLM
    Because the next() will place the "cursor" on the first valid row of data
    Without its not ready to read.
    The Qt docs explain this, so make sure to check them out :)



  • @rezaMSLM said in How to Read From sql Database:

    BUT the code gives me the hole values of a column

    This is exactly what you are asking it to do:

    QUERY.value("value")

    if you want the address column you'd use QUERY.value("address")


Log in to reply