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

update qsqlite and qtableview



  • 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


  • Lifetime Qt Champion

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



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


  • Lifetime Qt Champion

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



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


  • Lifetime Qt Champion

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


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


  • Lifetime Qt Champion

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



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



  • This post is deleted!


  • 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


  • Lifetime Qt Champion

    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]



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


  • Lifetime Qt Champion

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



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



  • @JonB
    @SGaist
    @jsulm

    Thank you very much that's exactly what I want


  • Lifetime Qt Champion

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


Log in to reply