4. update QSqlDatabase and QTableView

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

update QSqlDatabase and QTableView


  • Hi,

    I'm new in Qt and c++ and have following problem.
    I created a QSqlDatabase and fill it whit data.
    I display this database with a QTableView and this works so far.
    I get the data for the database from an array, this data are variable and here is my problem.
    I don't know how I can update the QSqlDatabase and the QTableView with the new data.
    It would be enough if the data were all 5-10 seconds updating.
    I'm using a QSqlTableModel at the moment.
    Is that the right way?
    Do I need a mapper?

    I'm also very grateful for improvements and criticism of my code.
    If you need more code or something else, let me know.

    I hope someone can help me.
    Thank you very much.

    Regards mogli

    Here is my code.

    cpp (the timer is only to create a variable array "arrayCustomiseToolChange[1]")

    test_screen::test_screen(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::test_screen)
{
    ui->setupUi(this);

    timer = new QTimer(this);
    timer->setInterval(1000);
    timer->start();

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open())
    {
        QMessageBox::critical(nullptr, QObject::tr("Cannot open database"),
            QObject::tr("Unable to establish a database connection.\n"
                        "This example needs SQLite support. Please read "
                        "the Qt SQL driver documentation for information how "
                        "to build it.\n\n"
                        "Click Cancel to exit."), QMessageBox::Cancel);
        return;
    }

//create a test Array
    qint32 testwerteFuerArray[30];
    int lengthTableCustomiseToolChange = 30;
    for (int i = 0; i < lengthTableCustomiseToolChange; i++)
    {
        int randInt = rand() % (1010000 - 0) + 1000000;
        testwerteFuerArray[i] = randInt;
    }

    fillArray(testwerteFuerArray, 30);

    query = new QSqlQuery;
    query->exec("Create Table CustomiseToolChange (COLOR varchar(20),"
               "TOOLNR, TIME int, POCKETSTATUS varchar(20))");
    query->prepare("INSERT INTO CustomiseToolChange (COLOR, TOOLNR, TIME, POCKETSTATUS) "
                   "VALUES (:COLOR, :TOOLNR, :TIME, :POCKETSTATUS)");
    query->bindValue(":COLOR", "");

//insert the array in the Database
    fillDatabaseTableCustomiseToolChange();
// increases "arrayCustomiseToolChange[1]" every second
    connect(timer, SIGNAL(timeout()), this, SLOT(functionFuerTestwert()));

    myTableCustomiseToolChange = new TableCustomise(this);
    myTableCustomiseToolChange->setTable("CustomiseToolChange");
    myTableCustomiseToolChange->select();
    ui->TableCustomiseToolChange->setModel(myTableCustomiseToolChange);

//***************************************************
//  ----        Methoden        ----
//***************************************************

void test_screen::fillArray(qint32 valuesForCustomiseToolChangeArray[],int arrayLength)
{
    for (int i=0; i<arrayLength; i++)
    {
        arrayCustomiseToolChange[i] = valuesForCustomiseToolChangeArray[i];
    }
    arrayCustomiseToolChange[1] = 10;
}

void test_screen::fillDatabaseTableCustomiseToolChange()
{
    int queryValue =0;

    for (int i = 0; i < (30 / 3); i++)
    {
        query->bindValue(":TOOLNR", arrayCustomiseToolChange[i * 3 + 0]);
        query->bindValue(":TIME", arrayCustomiseToolChange[i * 3 + 1]);
        query->bindValue(":POCKETSTATUS", arrayCustomiseToolChange[i * 3 + 2]);
        query->exec();
     }
}

void test_screen::functionFuerTestwert()
{
    arrayCustomiseToolChange[1] ++;
}

...

    cpp from my model

    TableCustomise::TableCustomise(QObject *parent)
    : QSqlTableModel(parent)
{
}

///////////////////////////////////////////////////////
//--------        Methoden         ------------------
///////////////////////////////////////////////////////

QVariant TableCustomise::data(const QModelIndex &index, int role) const
{
    if (role == Qt::DisplayRole)
    {
        if (index.column() == 0)
        {
            return displayData(index, limits[0]);
        }
qint32 contentForTime = index.sibling(index.row(), index.column()).data(Qt::EditRole).toInt();

        if (index.column() == 2 && contentForTime >= 1000000)
        {
            return setTimeFormat(index);
        }
    }
    if(role == Qt::BackgroundRole)
    {
        return backgroundData(index);
    }
    return QSqlQueryModel::data(index, role);
}
QString TableCustomise::displayData(const QModelIndex &index, qint32 limit)const
{
    qint32 contentForVerificationStatus = index.sibling(index.row(), index.column() + 3).data(Qt::EditRole).toInt();
    qint32 contentForColor = index.sibling(index.row(), index.column() + 2).data(Qt::EditRole).toInt();

            if (index.column() == 0)
            {
                if ((limit <= contentForColor   &&  contentForColor < 1000000) || (contentForVerificationStatus == 1))
                {
                    return "green";
                }
                else if (0 < contentForColor && contentForColor <limit)
                {
                    return "yellow";
                }
                else if (contentForColor == 0 || contentForColor > 1000000)
                {
                    return "red";
                }
                else
                {
                    return "yellow";
                }
            }

}

QVariant TableCustomise::backgroundData(const QModelIndex &index)const
{

    QString content = index.sibling(index.row(), 0).data(Qt::DisplayRole).toString();
        if ( content == "red" )
        {
            return QBrush(Qt::red);
        }
        else if (content == "yellow")
        {
           return QBrush(Qt::yellow);
        }
        else if (content == "green")
        {
            return QBrush(Qt::green);
        }
        else
        {
            return QBrush(Qt::magenta);
        }
}

QString TableCustomise::setTimeFormat(const QModelIndex &index)const
{
    qint32 contentForTime = index.sibling(index.row(), index.column()).data(Qt::EditRole).toInt();

    if (index.column() == 2 && contentForTime >= 1000000)
    {
        QString formattedTime;
        QString formattedTime_2;

        qint32 time = contentForTime - 1000000;
        int h = time/(60*60);
        int min = ((time - h * (60 * 60)) / 60);
        int sec = time % 60;
 
        formattedTime.append(QString("%1").arg(h,2,10,QLatin1Char('0')) + ":" +
                             QString("%1").arg(min,2,10,QLatin1Char('0')) + ":" +
                             QString("%1").arg(sec,2,10,QLatin1Char('0')));

        return formattedTime;
     }
}
    0 1 Reply

  • @Mogli123 said in update QSqlDatabase and QTableView:

    I get the data for the database from an array, this data are variable and here is my problem.
    I don't know how I can update the QSqlDatabase and the QTableView with the new data.
    It would be enough if the data were all 5-10 seconds updating.

    I don't know what this means?! :) You should update the model (QSqlTableModel, your TableCustomise) tied to the view with whatever data (e.g. http://doc.qt.io/qt-5/qsqltablemodel.html#setData, http://doc.qt.io/qt-5/qsqltablemodel.html#insertRecord) then the view will redraw to reflect that. See also http://doc.qt.io/qt-5/qsqltablemodel.html#EditStrategy-enum.

    I have no idea what "It would be enough if the data were all 5-10 seconds updating" might mean.

    0 1 Reply

  • @JonB

    Thank you for the quick answer.
    Sorry my English is not the best.
    I hope I can write it understandable.

    @JonB : I have no idea what "It would be enough if the data were all 5-10 seconds updating" might mean.

    I only would say that it isn't necessary to update the data very quickly.
    It is enough to update the table for example all 5 seconds.

    The data for the database only comes from the variable array.
    It isn't possible for the user to change any data with the tableview.

    the edit strategy should look like this.
    Is that right?

    myTableCustomiseToolChange->setEditStrategy(QSqlTableModel::OnFieldChange);

    Have you an example code?
    I'm new, therefore it is very diffucult for me to manage my problem with your links.

    0 1 Reply
  • Lifetime Qt Champion

    @Mogli123 said in update QSqlDatabase and QTableView:

    The data for the database only comes from the variable array.

    How often is this array updated?

    0

  • That isn't defined at this moment, but not faster than every 5 seconds.

    0 1 Reply

  • @Mogli123
    If the data only comes from an array, do you need to save it in a database at all? You can tie a QTableView to a memory array without involving SQL/a database at all.

    Not sure what your best edit strategy should be, given that you won't allow user to make any changes from the UI view anyway....

    0 1 Reply

  • @JonB
    Thank you for your help.

    No, it isn't necessary to save the data in a database.
    Yes, I have to save the data in a database.
    I tried it this way because that is the only way I know...

    @JonB :
    "You can tie a QTableView to a memory array without involving SQL/a database at all."
    Is that a "better and easier" way to manage my problem?
    How can I do this?
    Furthermore, with the database I had the possibility to change the header data.
    In addition, at this time I have some hidden column with have for example the color information in it, which I need.
    This color information isn't in the array.
    Is that with your way possible, too?

    the table should look something like that
    0_1541497400730_Unbenannt.PNG

    Sorry if this is basic.

    0 1 Reply

  • @Mogli123
    Let's take a step back!

    I think you are getting some data, which arrives in an array to your program, from some external source, which maybe sends new data every so often. Is that right?

    Forget Qt etc. for the moment. Do you or do you not wish to preserve that data in a database for some future purpose? If the answer turns out to be "No, I simply want to display the data in a table in the UI, when the program exits I'm happy for it to vanish" then you do not want to use a database/SQL! Instead look at http://doc.qt.io/qt-5/qstandarditemmodel.html, which is the basis for models which do not need SQL storage.

    1 1 Reply

  • @JonB

    Tank you.

    *I think you are getting some data, which arrives in an array to your program, from some external source, which maybe sends new data every so often. Is that right?

    Yes, exactly.
    I don't need the data in the future.
    I only have to display the data like in the picture, but not all of them. Some data I only need for the decision which color the row need, or other decisions. This data are at this time in hidden columns.

    0 1 Reply

  • @Mogli123
    So like I said: get rid of all SQL stuff, start out from QStandardItemModel (can be sub-classed if required), use that as your QTableView's model. (I said earlier you could use an array/QVector/whatever directly; I think you actually want to copy your data into a QStandardItemModel.) You can then insert/delete/update rows as your data arrives, and the view will update correspondingly.

    0 1 Reply

  • @JonB
    Thank you very much for your help.

    I create my own model like shown in my code above.
    Is this with the QStandartItemModel possible on the same way?
    I need that for manipulating the data.

    How can I insert the data from my array (there are only qint32 in it) and how are they updating?
    Whit signal and slots?

    I know that are many questions and I'm very thankful for your help.

    0

  • My boss just told me that he wants the data in a database.
    He knows that it is not necessary at the moment, but later he needs the functions of a database.
    So back to my original question, how can I update the database and the tableview?

    I'm so sorry for this wrong information.

    0 1 Reply

  • @Mogli123
    So then I said earlier:

    You should update the model (QSqlTableModel, your TableCustomise) tied to the view with whatever data (e.g. http://doc.qt.io/qt-5/qsqltablemodel.html#setData, http://doc.qt.io/qt-5/qsqltablemodel.html#insertRecord) then the view will redraw to reflect that.

    You need to read those, look at examples etc.

    1

  • Can you give me a smal example pleas how I have to do that?
    That will be very helpfull.

    Thank you very much.

    0

  • @JonB
    Thank you for your help:)
    I tried it like this and it seems that it works

    cpp
...
  myTableCustomiseToolChange->setEditStrategy(QSqlTableModel::OnFieldChange);
...
//Methoden
...
void test_screen::functionFuerTestwert()
{
    arrayCustomiseToolChange[1] ++;
    myTableCustomiseToolChange->setData(myTableCustomiseToolChange->index(1,2), arrayCustomiseToolChange[1]);
}

    is that the right way?

    0 1 Reply

  • @Mogli123
    Yes, you seem to be using the model's setData() to alter values, which should be automatically reflected in the view, as per your original question.

    0

  • @JonB
    Thank you very much:)

    Have a nice day.

    0
Log in to reply