update qsqlite and qtableview

Mogli123

Hi,

I'm creating a database (qsqlite) and i want to show the data with a qtableview.
creating the database and to show them via qtableview works so far, but at the point to update it I have problems.
It does not work like I want. The new data are at the wrong place or some data are missing.
The sice of the database remains constant, only the data changes incomming from a tcpsocket.
I hope someone can help me
If I have some bad code please tell me.
I'm a beginner and want to learn how to do it corectly.

creating the database

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

 queryToolChangeWarnings = new QSqlQuery;
    queryToolChangeWarnings->exec("CREATE TABLE ToolChangeWarnings (COLOR varchar(20),"
                "TOOLNR int, TIME int, POCKETSTATUS int)");
    queryToolChangeWarnings->prepare("INSERT INTO ToolChangeWarnings (COLOR, TOOLNR, TIME, POCKETSTATUS) "
                   "VALUES (:COLOR, :TOOLNR, :TIME, :POCKETSTATUS)");
    queryToolChangeWarnings->bindValue(":COLOR", "");

    this->close();

    myTableToolChangeWarnings = new TableToolChangeWarnings(this);
    myTableToolChangeWarnings->move(spaceBetweenTables, spaceBetweenTables);
    myTableToolChangeWarnings->setMinimumHeight(tableHeight);
    myTableToolChangeWarnings->setMinimumWidth(tableWidth);
    myTableToolChangeWarnings->show();


    queryManipulationToolChangeWarnings = new QSqlTableModel(this);
    queryManipulationToolChangeWarnings->setTable("ToolChangeWarnings");
    queryManipulationToolChangeWarnings->select();
    queryManipulationToolChangeWarnings->setEditStrategy(QSqlTableModel::OnFieldChange);

the new data for the database

void detailScreen::receiveVectorTool_W(QVector<int> vector)
{
    if (vector.length() == 30)
    {
        for(qint8 i = 0; i<30; i++)
        {
            arrayToolChangeWarnings[i] = vector[i];
        }
        if (firstFill == false)
        {
    
            firstFillDatabaseToolChangeWarnings();
            emit fillTableNewToolChangeWarnings();
    
            firstFill = true;
        }
        else
        {
            emit fillTableNewToolChangeWarnings();
        }

    }
}

first fill of the database

void detailScreen::firstFillDatabaseToolChangeWarnings()
{
    for (int i = 0; i < 10; i++)
    {
        queryToolChangeWarnings->bindValue(":TOOLNR", arrayToolChangeWarnings[i * 3 + 0]);
        queryToolChangeWarnings->bindValue(":TIME", arrayToolChangeWarnings[i * 3 + 1]);
        queryToolChangeWarnings->bindValue(":POCKETSTATUS", arrayToolChangeWarnings[i * 3 + 2]);
        queryToolChangeWarnings->exec();
    }
}

update the data

void detailScreen::fillDatabaseToolChangeWarnings()
{

    qint8 i = 0;
    for (qint8 indexRow = 0; indexRow < 10; indexRow ++)
    {

        for (qint8 indexColumn = 1; indexColumn < 4; indexColumn ++)
        {
            queryManipulationToolChangeWarnings->setData(queryManipulationToolChangeWarnings->
            index(indexRow,indexColumn), arrayToolChangeWarnings[i]);
            queryManipulationToolChangeWarnings->submit();
            i++;
        }

    }

    emit refreshTableToolChangeWarnings();
}

in my table clase I try to update the data via signal & slot

void TableToolChangeWarnings::updateTable()
{
    myModelToolChangeWarnings->setQuery("SELECT * FROM ToolChangeWarnings");
};

If you need more informations please ask

Thank you in advance
regards

SGaist

Hi,

@Mogli123 said in update qsqlite and qtableview:

myModelToolChangeWarnings

What is that class ?

Can you explain more precisely what you mean by "at the wrong place" ?

Mogli123

Hi, thank you for answering
myModelToolChangeWarnings inherits from QSqlQueryModel

SGaist:"Can you explain more precisely what you mean by "at the wrong place" ?"
To say: "they are at the wrong place" is wrong. Sorry for that.
I think I'm overiting the old data wrong in the database.
To write the data the first time to the database works but updating doesn't work.
I don't know how exactly to do that.

Is that the write way to update the database or is there a better solution?

jsulm

@Mogli123 To update existing data you use an UPDATE SQL query. How does your UPDATE query look like?

Mogli123

@jsulm
I tried it like this way

void detailScreen::fillDatabaseToolChangeWarnings()
{
    qint8 i = 0;
    for (qint8 indexRow = 0; indexRow < 10; indexRow ++)
    {

        for (qint8 indexColumn = 1; indexColumn < 4; indexColumn ++)
        {
            queryManipulationToolChangeWarnings->setData(queryManipulationToolChangeWarnings->
            index(indexRow,indexColumn), arrayToolChangeWarnings[i]);
            queryManipulationToolChangeWarnings->submit();
            i++;
        }

    }

    emit refreshTableToolChangeWarnings();
}

but it seems to be wrong

Yesterday i tried it like this

void detailScreen::fillDatabaseToolChangeWarnings()
{

 queryToolChangeWarnings->prepare("UPDATE ToolChangeWarnings (COLOR, TOOLNR, TIME, POCKETSTATUS) "
                                        "VALUES (:COLOR, :TOOLNR, :TIME, :POCKETSTATUS)");
    for (int i = 0; i < 10; i++)
    {
        queryToolChangeWarnings->bindValue(":TOOLNR", arrayToolChangeWarnings[i * 3 + 0]);
        queryToolChangeWarnings->bindValue(":TIME", arrayToolChangeWarnings[i * 3 + 1]);
        queryToolChangeWarnings->bindValue(":POCKETSTATUS", arrayToolChangeWarnings[i * 3 + 2]);
        queryToolChangeWarnings->exec();
    }
    emit refreshTableToolChangeWarnings();
}

but it does not work too.

can you please tell me to do it corectly?

jsulm

@Mogli123 There is no WHERE in your query, so you're updating everything. If you, for example, want to update a data record with a specific ID you can do it like this:

UPDATE ToolChangeWarnings (COLOR, TOOLNR, TIME, POCKETSTATUS) "
                                        "VALUES (:COLOR, :TOOLNR, :TIME, :POCKETSTATUS)"
                                        "WHERE ID = :ID"

Mogli123

@jsulm
to updating everything it's ok, because the old data are not necessary after updating

jsulm

@Mogli123 But then I don't understand what the problem is.
Or maybe you misunderstood what I wrote? Without WHERE you're updating all data records in the table. So, if you have 10 records in that table all 10 will be updated.

Mogli123

@jsulm
Yes that is that wat I want because I don't know which data had changed therefore I want to override all data.
Or is this a bad solution?
My problem is that it does not work and I don't know why.
after writing the data at first time it works fine and the qtableview is displaying them.
If I try to update them the qtableview does nothing
Furthermore I don't know if updating the database works because the qtableview shows no new data

Mogli123

This post is deleted!

Mogli123

if I try something like this

        queryToolChangeWarnings->prepare("UPDATE ToolChangeWarnings SET TOOLNR = :TOOLNR");
        queryToolChangeWarnings->bindValue(":TOOLNR", arrayToolChangeWarnings[0]);

The updating is working but complete column TOOLNR geht the int value from the arrayToolChangeWarnings[0]

How can I only update for example the first value from the column not the complete column
and so on

SGaist

Because your query asks exactly for that. Use the WHERE keyword and state precisely where you want the update to happen.

See the documentation which also contain a big warning about missing the WHERE part.

[edit: added link to documentation SGaist]

Mogli123

@SGaist
Sorry your right
If I try it lik this it works

 queryToolChangeWarnings->prepare("UPDATE ToolChangeWarnings SET TOOLNR = :TOOLNR WHERE ID = 0");
 queryToolChangeWarnings->bindValue(":TOOLNR", arrayToolChangeWarnings[0]);
 queryToolChangeWarnings->exec();

It looks like that it is very impractical to do this for all cells
Do you know a solution to do this in a more efficient way

SGaist

Then use another bind value for ID and pass it also to your query. You can do that in a loop.

JonB

@Mogli123
Provided I understand you right:

It looks like that it is very impractical to do this for all cells

I believe you're saying you have some array of data (from a TCP socket). You want to update the database to change the TOOLNR value of a bunch of rows to a variety of values in your input data,

It cannot be helped that this requires a distinct SQL statement to be sent to the database for each input value (array element). If you have a 100 rows of data you will be executing 100 SQL statements. That's how it is.

At the code side, as @SGaist says you do not have to write 100 lines of code, one for each row. You use a loop to produce the statements, like:

for (int i = 0; i < arrayToolChangeWarnings.size(); i++
{
    queryToolChangeWarnings->prepare("UPDATE ToolChangeWarnings SET TOOLNR = :TOOLNR WHERE ID = " + QString::number(i));
    queryToolChangeWarnings->bindValue(":TOOLNR", arrayToolChangeWarnings[i]);
    queryToolChangeWarnings->exec();
}

There may be a way of sending the multiple statements as a single batch efficiently, I don't know (but then you'd have to deal with the bindings somehow).

Mogli123

@JonB
@SGaist
@jsulm

Thank you very much that's exactly what I want

SGaist

Great !

Then please mark the thread as solved using the "Topic Tools" button so that other forum uses may know a solution has been found :)