create a database for a tableview

[Mogli123]

Hi,

I'm new in Qt and c++ and have the following problem.

I get some arrays with data from an machine.
This data I want to display in a tableview, but I have no idea how i have to creat a database.
I have a sqllight database.
Is it possible to make the data variable there?
If yes, how i have to insert the array?
At this time my data in the database are static, but this is not what i need.
I created the tables with the designer.
I hope someone can help with an example code.

that is my code so far

db.h

#include <QSqlQuery>

static bool createConnection()
{
    QSqlDatabase db_Werkzeug_Wech_Warn = QSqlDatabase::addDatabase("QSQLITE");
    db_Werkzeug_Wech_Warn.setDatabaseName(":memory:");
    if (!db_Werkzeug_Wech_Warn.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 false;
    }

    QSqlQuery query;
    query.exec("Create Table CustomiseToolChange (COLOR varchar(20), "
               "TOOLNR int primary key, TIME varchar(20))");

    query.exec("insert Into CustomiseToolChange values('green', 13, '01:32')");
    query.exec("Insert Into CustomiseToolChange values('yellow', 14, '00:25')");
   ...

test_screen.cpp

#include "test_screen.h"
#include "ui_test_screen.h"
#include "tableCustomise.h"

#include "QDebug"


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


    myTableCustomiseToolChange = new modelCustomiseToolChange(this);

    

    myTableCustomiseToolChange = new TableCustomise(this);

    myTableCustomiseToolChange->setQuery("SELECT * FROM CustomiseToolChange");
    myTableCustomiseToolChange->setHeaderData(1, Qt::Horizontal, tr("Werkzeug Nr."));
    myTableCustomiseToolChange->setHeaderData(2, Qt::Horizontal, tr("Zeit"));
    ui->TableCustomiseToolChange->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->TableCustomiseToolChange->setModel(myTableCustomiseToolChange);

    ui->TableCustomiseToolChange->setColumnHidden(0, true);

    ui->TableCustomiseToolChange->verticalHeader()->hide();
    ui->TableCustomiseToolChange->setStyleSheet("QHeaderView::section { background-color:grey }");
    ui->TableCustomiseToolChange->setStyleSheet("border: 2px solid black; background: grey");
    ui->TableCustomiseToolChange->setSelectionMode(QAbstractItemView::NoSelection);

tableCustomise.cpp

#include <QtWidgets>
#include "QSqlQueryModel"

#include "tableCustomise.h"
#include "QColor"



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


QVariant TableCustomise::data(const QModelIndex &index, int role) const
{
    if(role == Qt::TextAlignmentRole)
    {
        return Qt::AlignCenter;
    }

    if(role == Qt::BackgroundRole )
    {

    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);
        }
    }

    //setzt die Daten
    return QSqlQueryModel::data(index, role);
}

Thank you for helping.

[VRonin]

This really has nothing to do with Qt, it's just SQL. You can use QSqlQuery to run an UPDATE query on your database

[Mogli123]

Thank you for your answer.

Can you explain it more exactly please.

[VRonin]

@Mogli123 said in create a database for a tableview:

query.exec("insert Into CustomiseToolChange values('green', 13, '01:32')");

Here you are inserting a value. You can use the exact same technique to run an update query something like:
query.exec("update CustomiseToolChange set COLOR='orange' where TOOLNR=13");

[Mogli123]

This post is deleted!

[Mogli123]

Thanks for your help.

How can I insert a array with 30 variable values in it?
The database is in a .h file is this a problem?
The data from my code above are wrong, they are only test data.
First of all I want fill the database only with the data from the array.
Later I want to add some other data.

Sorry for that if the questions are basic.
I'm a noob.

[VRonin]

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

or just call query.exec in a loop

[Mogli123]

Thank you very much it works

thats my test example

    int lengthTableCustomiseToolChange = 30;
    QVector<qint32> tableCustomiseToolChange(lengthTableCustomiseToolChange);
    for(int i = 0; i < lengthTableCustomiseToolChange; i++)
    {
        tableCustomiseToolChange[i] = i;
        qDebug()<<"Testwert"<<tableCustomiseToolChange[i];

    }
    qDebug()<<"aktueller Wert ="<<tableCustomiseToolChange[19];

    for (int i=0; i<(lengthTableCustomiseToolChange/3); i++)
    {
        int j;

    QSqlQuery query;
    query.exec("Create Table CustomiseToolChange (POCKETSTATUS varchar(20), "
                               "TOOLNR int primary key, TIME varchar(20), COLOR varchar(20))");
        query.prepare("INSERT INTO CustomiseToolChange (POCKETSTATUS, TOOLNR, TIME, COLOR) "
                      "VALUES (:POCKETSTATUS, :TOOLNR, :TIME, :COLOR)");
        query.bindValue(":POCKETSTATUS", tableCustomiseToolChange[j = i * 3 + 0]);
        query.bindValue(":TOOLNR", tableCustomiseToolChange[j = i * 3 + 1]);
        query.bindValue(":TIME", tableCustomiseToolChange[j = i * 3 + 2]);
        query.bindValue(":COLOR", "");
        query.exec();
     }

[VRonin]

I would move the query.exec("Create Table part outside of the loop

[Mogli123]

Thank you for this tip :)