Unable to save changes on QSqlRelationalTableModel to the PostGres DB

JonB

@Andrea_Venturelli said in Unable to save changes on QSqlRelationalTableModel to the PostGres DB:

CREATE TABLE COMMENTS_table (
id BIGSERIAL UNIQUE,

I have a possibility. Change so that id is also PRIMARY KEY, whatever that syntax is for PSQL. (And while you are at it make sure the id columns in the other two table are primary key too. And you should always have a PK in tables anyway.)

For QSqlRelationalTableModel https://doc.qt.io/qt-6/qsqlrelationaltablemodel.html#details actually requires:

The table must have a primary key declared.

but it does not say why.

Now, the error you get is from

2024-11-28 11:52:25.129 CET [18852] INSTRUCTION:  PREPARE qpsqlpstmt_1 AS UPDATE "comments_table" SET "body_message"=$1 WHERE "comments_table"."id" = $2 AND "comments_table"."vin" = $3 AND "comments_table"."mail" = $4 AND "comments_table"."post_date" = $5 AND "comments_table"."post_editing_date" IS NULL AND "comments_table"."body_message" = $6

The problem lies in the AND "comments_table"."vin" = $3 AND. This UPDATE statement is "pessimistic locking"-type, i.e. it's WHERE each column equals original value. It is possible that if Qt code sees a primary key it will generate an UPDATE statement with just

UPDATE "comments_table" SET ... WHERE "comments_table"."id" = $2

and no other columns in the WHERE since "comments_table"."id" is known to be primary key. If it does that it won't try to include the pseudo-column vin in the statement.

While you are at it comment out this->m_model->setFilter(filter);, I don't know if that affects the SQL generated.

For adding rows for testing you can either write INSERT statements in a script (like where you already have some with CREATE TABLE VIN_table or you can use whatever tool PSQL has like a "workbench".

The objective is to verify that when populated with rows from comments_table it shows the values from the foreign key tables, so you need suitable items in all 3 tables. Then we know it is generating a suitable SELECT statement, unlike the UPDATE. At the moment most of your pics show those columns as empty or the underlying integer value. I don't know whether you have got that working yet or not. Just like you show in your "How the Final Table Should Appear".

Andrea_Venturelli

@JonB you are absolutly right!! I thought (do not ask me why) that declaring BIGSERIAL also set the column as a primary key.
I drop the tables and re-built them with the PK in the right spot like so:



/*
-------------------------------------------------------------------
                        VIN TABLE   
-------------------------------------------------------------------
*/
CREATE TABLE VIN_table (
    id BIGSERIAL PRIMARY KEY,    /*  added PrimaryKey here */
    VIN VARCHAR(32) NOT NULL UNIQUE,
    PRJ_CODE VARCHAR(32),
    MAKE_YEAR DATE
);

/* RECORD 1 */
INSERT INTO VIN_table (VIN, PRJ_CODE, MAKE_YEAR)
VALUES ('5LMPU28A8YLJ84702', 'PRJ100', '2019/01/01'::DATE);

/* RECORD 2 */
INSERT INTO VIN_table (VIN, PRJ_CODE, MAKE_YEAR)
VALUES ('3NIAB51D95L401539', 'PRJ155', '2019/01/01'::DATE);

/* RECORD 3 */
INSERT INTO VIN_table (VIN, PRJ_CODE, MAKE_YEAR)
VALUES ('5N3AA08C14N800205', 'PRJ25', '2017/01/01'::DATE);

/* RECORD 4 */
INSERT INTO VIN_table (VIN, PRJ_CODE, MAKE_YEAR)
VALUES ('4A3AK34YIXE055700', 'PRJ75', '2010/01/01'::DATE);

/* RECORD 5 */
INSERT INTO VIN_table (VIN, PRJ_CODE, MAKE_YEAR)
VALUES ('2GIWF52E749130019', 'PRJ10', '2020/01/01'::DATE);

SELECT * FROM vin_table;


/*
-------------------------------------------------------------------
                        USERS TABLE   
-------------------------------------------------------------------
*/
CREATE TABLE USERS_table (
    id BIGSERIAL PRIMARY KEY,                           /*    added PrimaryKey here */
    MAIL VARCHAR(100) NOT NULL UNIQUE,
    PASSWORD VARCHAR(100),
    PRIVILAGE VARCHAR(32) CHECK(PRIVILAGE IN ('ADMIN', 'USER'))
);

/* RECORD 1 */
INSERT INTO USERS_table (MAIL, PASSWORD, PRIVILAGE)
VALUES ('userl@gmail.com', '123password', 'USER');

/* RECORD 2 */
INSERT INTO USERS_table (MAIL, PASSWORD, PRIVILAGE)
VALUES ('123user@gmail.com', 'qwerty135', 'USER');

/* RECORD 3 */
INSERT INTO USERS_table (MAIL, PASSWORD, PRIVILAGE)
VALUES ('us.er@gmail.com', 'simplePassword', 'ADMIN');

/* RECORD 4 */
INSERT INTO USERS_table (MAIL, PASSWORD, PRIVILAGE)
VALUES ('random_user@gmail.com', 'pASSW0rd', 'ADMIN');


SELECT * FROM USERS_table;



/*
-------------------------------------------------------------------
                        COMMENTS TABLE   
-------------------------------------------------------------------
*/
CREATE TABLE COMMENTS_table (
    id BIGSERIAL PRIMARY KEY,                      /* added PrimaryKey here */
    CHASSIS_ID BIGINT NOT NULL,
    USER_ID BIGINT NOT NULL,
    POST_DATE TIMESTAMP NOT NULL UNIQUE,
    POST_EDITING_DATE TIMESTAMP UNIQUE,
    BODY_MESSAGE TEXT NOT NULL,

    CONSTRAINT ext_key_chassis_id
        FOREIGN KEY(CHASSIS_ID)
        REFERENCES VIN_table(id),
    
    CONSTRAINT ext_key_user_id
        FOREIGN KEY(USER_ID)
        REFERENCES USERS_table(id)
);

/* RECORD 1 */
INSERT INTO COMMENTS_table (CHASSIS_ID, USER_ID, POST_DATE, BODY_MESSAGE)
VALUES (3, 2, '2023/02/19 13:30:00'::timestamp, 'RANDOM TEXT 1');

/* RECORD 2 */
INSERT INTO COMMENTS_table (CHASSIS_ID, USER_ID, POST_DATE, POST_EDITING_DATE, BODY_MESSAGE)
VALUES (3, 2, '2023/06/20 11:30:00'::timestamp, '2023/06/22 14:30:00'::timestamp, '123 TEXT TEXT');

/* RECORD 3 */
INSERT INTO COMMENTS_table (CHASSIS_ID, USER_ID, POST_DATE, BODY_MESSAGE)
VALUES (2, 1, '2023/07/03 8:30:00'::timestamp, 'TEST MESSAGE');

/* RECORD 4 */
INSERT INTO COMMENTS_table (CHASSIS_ID, USER_ID, POST_DATE, BODY_MESSAGE)
VALUES (3, 4, '2023/09/08 10:20:00'::timestamp, 'RND TEXT 2');

/* RECORD 5 */
INSERT INTO COMMENTS_table (CHASSIS_ID, USER_ID, POST_DATE, POST_EDITING_DATE, BODY_MESSAGE)
VALUES (1, 3, '2024/12/24 18:30:00'::timestamp, '2025/01/05 11:55:00'::timestamp, 'TXT TXT TXT');

/* RECORD 6 */
INSERT INTO COMMENTS_table (CHASSIS_ID, USER_ID, POST_DATE, BODY_MESSAGE)
VALUES (4, 1, '2025/01/01 13:30:00'::timestamp, 'MY TEXT');

/* RECORD 7 */
INSERT INTO COMMENTS_table (CHASSIS_ID, USER_ID, POST_DATE, BODY_MESSAGE)
VALUES (5, 2, '2025/02/19 17:58:00'::timestamp, 'NO TEXT HERE');


SELECT * FROM COMMENTS_table;

this solves the problem with the "commitAll()" call but now the method model->setData() do not change the value but return TRUE...

Andrea_Venturelli

@Christian-Ehrlicher I check the version of Qt I'm using and on this PC is not the 6.8.x but I have the Qt 6.7.3

@JonB sorry if wasn't clear at the beginning, but I have a fully working application that display to a QTableView the database's values correctly like the example provided in the "How the Final Table Should Appear" as you can see from the following screenshot

"PLEASE NOTE: the ID 1 was push to the bottom because at the beginning of the MainWindow's constructor I was testing the work-around with QSqlQuery that updated the value. And apparently when you edit a a row this will be moved to the end of the table."

Andrea_Venturelli

here, the GitHub link to the code I'm running :
https://github.com/aVenturelli-qt/Edit_data_in_QSqlRelationalTableModel/tree/main

the only file I omitted is "database_access_info.h" that contains the following:

#ifndef DATABASE_ACCESS_INFO_H
#define DATABASE_ACCESS_INFO_H

namespace db_info
{
    const char HOST[] = "localhost";
    const int PORT{5432};
    const char CONNECTION_NAME[] = "postgres";
    const char USER_NAME[] = "postgres";
    const char DB_NAME[] = "comments_db";

    const char PASSWORD[] = "Put_your_password_here";    //update this value with your password
}

#endif // DATABASE_ACCESS_INFO_H

JonB

@Andrea_Venturelli
I am pleased my guess about Primary Key was correct. Without it the code for the UPDATE ... WHERE put in "comments_table"."vin" = $3 and that is just wrong/not acceptable. Perhaps they knew this when they write you must have a PK on the referencing table. Like I said you always want an (explicit) PK anyway.

this solves the problem with the "commitAll()" call but now the method model->setData() do not change the value but return TRUE...

If this is still a problem I don't know what you mean. What setData() where? There is too much going on in this thread for me to know. setData() will return true if it successfully changes a column's value or if the column already has that value, in which case it will do nothing.

Andrea_Venturelli

I found out why the view do not update here are the summary for the resolution of the problems.

fixing: "submitAll() causes an error in the model->lastError().text() and does not save the changes to the DB" problem

thanks to @JonB to find the solution. this is caused because EVERY TABLE MUST HAVE a PrimaryKey Declared (and I wrongly assumed that "BIGSERIAL" also declare that column as a PR).

Dropping the tables and add the "Primari Key" sintax in the SQL code will fix the problem.

fixing: "model->setData() now does not update the value inside the view" problem

after the line

m_model->setData(m_model->index(0, 5), "Modification works correctly");

I was calling "model->select()" but this will clear the pending data to be filled in on the table view.
delete the model->select() line and use submit() instead.


bool MainWindow::commitChanges()
{

    bool result = m_model->setData(m_model->index(0, 5), "changed from commit_btn");   // returns true, but do not update the view
    m_model->setData(m_model->index(0, 4), QDateTime::currentDateTime());
    m_model->submit();
    return result;
}

Christian Ehrlicher

@Andrea_Venturelli said in Unable to save changes on QSqlRelationalTableModel to the PostGres DB:

but I have the Qt 6.7.3

Then take a look at QTBUG-128434 - maybe it's your problem.

JonB

@Christian-Ehrlicher
I think the OP now has resolved all problems (primary key needed for original UPDATE error, the setData() mentioned was a red-herring).

@Andrea_Venturelli
OOI I just came across SubmitAll() fails on QSqlRelationalTableModel which ends up concluding

but the big problem was that my reference tables did not have primary keys at the time. Once I changed that, it worked as a charm!

delete the model->select() line and use submit() instead.

Hmmm. From what I can see you are using OnManualSubmit strategy. Be aware that QSqlTableModel::submit() states:

Submits the currently edited row if the model's strategy is set to OnRowChange or OnFieldChange. Does nothing for the OnManualSubmit strategy.

Use submitAll() to submit all pending changes for the OnManualSubmit strategy.

You might consider this. Maybe you can use submitAll() as you used to do to avoid any potential issue.

Andrea_Venturelli

@JonB said in Unable to save changes on QSqlRelationalTableModel to the PostGres DB:

Hmmm. From what I can see you are using OnManualSubmit strategy. Be aware that QSqlTableModel::submit() states:

I tested with OnManualSubmit and with the default OnRowChanged. Both submit() and submitAll() works correctly. I will mark this topic as "solved" later today after some more tests to be sure.

For now, thanks you all. ;)