How to Read From sql Database

rezaMSLM

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?

VRonin

@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

rezaMSLM

@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?

jsulm

@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?

mrjj

@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.

rezaMSLM

@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

rezaMSLM

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?

mrjj

@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 :)

VRonin

@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")