Unable to save changes on QSqlRelationalTableModel to the PostGres DB

Andrea_Venturelli

hi everyone, this post is related to this topic I posted few weeks ago called:
"Custom QStyledItemDelegate to customize apperence of QListView with a SQLModel under the hood"

I have a lineEdit, a QPushBotton (commit_btn, to save changes) and a TableView displaying the SqlRelationalTableModel.

this is how the database is composed and the top_table is what I see after connecting to the DB.

here the MainWindow's code:


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

    // connecting to the postgres database
    QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
    db.setHostName(db_info::HOST);
    db.setDatabaseName(db_info::DB_NAME);
    db.setUserName(db_info::USER_NAME);
    db.setPassword(db_info::PASSWORD);
    db.setPort(db_info::PORT);

    // try connection
    bool connected = db.open();
    if (!connected) qDebug() << "Db is unable to establish a connection";

    // define the model and passing the connection
    m_model = new QSqlRelationalTableModel(this, db);
    m_model->setTable("comments_table");
    m_model->setRelation(1, QSqlRelation("vin_table", "id", "vin"));
    m_model->setRelation(2, QSqlRelation("users_table", "id", "mail"));
    m_model->setJoinMode(QSqlRelationalTableModel::InnerJoin);

    m_model->select();

    // set the model to the view
    ui->display_tv->setModel(m_model);
    ui->display_tv->setItemDelegate(new QSqlRelationalDelegate(ui->display_tv));

    // check for editing strategy
    qDebug() << "Edit strategy: " << m_model->editStrategy();       // 1 = on_row_change, 2 = manual_submit
    m_model->setEditStrategy(QSqlTableModel::OnManualSubmit);

    // connect lineedit "ReturnPressed" to model->setFilter()
    connect(ui->search_le, &QLineEdit::returnPressed, this, [this](){
        // get the content of QLineEdit
        auto txt = this->ui->search_le->text();

        // set filter whereas txt is empty or not
        auto filter = (!txt.isEmpty())? QString("vin = '%1'").arg(txt) : "";
        this->m_model->setFilter(filter);
        this->m_model->select();
    } );

    connect(ui->commit_btn, &QPushButton::clicked, this, &MainWindow::commitChanges);
}


MainWindow::~MainWindow()
{
    delete m_model;
    delete ui;
}

bool MainWindow::commitChanges()
{
    auto idx = m_model->index(0, 5);        // body message
    bool result = m_model->setData(idx, "Modification works correctly");
    m_model->submitAll();
    return result;
}

the problem:

But nothing seems to update the values in the PostGres Database, so when I close the App and re-start it, the values is still the old ones

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

artwaw

Have you examined the values of lastError etc after setData()? If there is an error in datatype or somewhere around it should be visible.
Also, you never check for the value of bool result?

Andrea_Venturelli

hi @artwaw thanks for the replay.
I checked "LastError" as you suggested. m_model->setData(...) works fine and update the view with the text I'm espacting, but after "m_model->submitAll() is being called, lastError now output:

[ "after submitAll" ] last error is	 "ERROR:  the column comments_table.vin doesn't exist\nLINE 1: ..._message\"=$1 WHERE \"comments_table\".\"id\" = $2 AND \"comments_...\n                                                             ^\n(42703) QPSQL: Unable to prepare statement"

but for me is very criptic with the meaning of the error, beside "column not existing" that is obvious.

my "comments_table" contains the columns:

ID
CHASSIS_ID (foreign key to VIN_table)
USER_ID (foreign key to USERS_table)
POST_DATE
POST_EDITING_DATE
BODY_MESSAGE

// postgres query to create table
/*
-------------------------------------------------------------------
                        COMMENTS TABLE  
-------------------------------------------------------------------
*/
CREATE TABLE COMMENTS_table (
    id BIGSERIAL UNIQUE,
    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');

artwaw

After a brief look at the documentation:
When using setData() the role should always be Qt::EditRole, and when using data() the role should always be Qt::DisplayRole.

Perhaps that's the problem, before we start digging deeper?

Andrea_Venturelli

when i look at the documentation yesterday I red that Qt::EditRole is the third parameter (default) provided to model.setData().

as I said, the model and the view shows the right modified text, but the problem occure after calling model.submitAll() to save the current model to the Postgres DB. I'm thinking about at some conversion problem between the QSqlRelationalTableModel (having different columns name due to the "QSqlRelation") with the orginals column in the DB.

Andrea_Venturelli

from the Postgres' logs tab i get the following:

2024-11-28 11:52:25.129 CET [18852] ERROR:  la colonna comments_table.vin non esiste al carattere 108
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

but my DB has this:

and instead I got this

artwaw

there is also a chapter about aliasing names that occurs using relations?
I presume all constraints listed there you have examined already.

Andrea_Venturelli

I tryed changing the header so they match with the original version but doesn't work.
Currently the only workaround I found, is to use the QSqlQuery and pass it, the database and call exec()

If someone with deep knowledge about QSqlRelationalTableModel have a solution with an explanation about the problem, I'll be glad to listen it.

JonB

@Andrea_Venturelli
But the error message states just what is wrong (whether English or Italian!)

... AND "comments_table"."vin" = $3 AND ....
ERROR: the column comments_table.vin doesn't exist
ERROR: la colonna comments_table.vin non esiste al carattere 108

And you have shown your table definition, comments_table has a column named chassis_id, there is no vin column there, so you cannot have it in a condition. chassis_id is a FOREIGN KEY into another table, VIN_table, but that is not the same in any way as comments_table.vin in your query. Unless PSQL has some magic way/syntax of accessing it that way, which I have not heard of in SQL.

I would expect your UPDATE or SELECT statements on comments_table to have WHERE chassis_id = <some number> to access that column. As per e.g. your INSERT INTO statements above. If you want to access the VIN_table you should need a JOIN.

This all somehow comes from

m_model->setRelation(1, QSqlRelation("vin_table", "id", "vin"));

If you replace "vin" by "chassis_id" does it work?

I am unclear why what is being generated for the UPDATE statement seems wrong. Does your SELECT statement for populating the table work correctly and display the linked VINs (if you populate that at the database side)? Just to confirm: if a SELECT works correctly but UPDATE does not it feels like there is something wrong. I don't know where or whether that's to do with PSQL.

Andrea_Venturelli

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

there is no vin column there, so you cannot have it in a condition.

exactly, I don't have it, this column name is automatically generated from the line:

m_model->setRelation(1, QSqlRelation("vin_table", "id", "vin"));

because if I omit the two m_model->setRelations, the table_view will display numbers (foreing key) that is not usefull when I have to retrive the User who wrote the comment, and the VIN (vehicle ID number) to filter the database in order to dsplay only one CAR VIN at a time.

when I call m_model->ubmitAll() this generate an error because VIN is not part of the original table "comments_table", but I didn't change the column's name so I was hoping the QSqlRelationtableModel have a clue to map-back to the original ID associetied with

JonB

@Andrea_Venturelli
Indeed. Assuming the UPDATE statement you show is generated from QSqlRelationalTableModel without you altering it, which I think you are saying is the case, then that looks wrong to me, exactly as per the error message complaint!

I asked you to verify whether it gets it right if you populate a few correct rows into all three tables and you just do a SELECT (i.e. when the QTableView fills). What statement does that generate? Maybe it's only a problem on UPDATE? I don't have PSQL, someone else who reads this forum does and may want to test it, and I don't have time to test it all with e.g. MYSQL to see whether it works there.

Andrea_Venturelli

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

If you replace "vin" by "chassis_id" does it work?

doing as you seggested produce an empty table.

m_model->setRelation(1, QSqlRelation("vin_table", "id", "vin"));

setRelation() takes the column's number containing the foreign key_ID from the base table setted in "model->setTabel("name_of_table") " and after you pass a QSqlRelation() specifing "name_of_target_table" , the column_name to look for a match in the base_table and the column_name indicating what values to display instead of foreign keys"

like so (green text highlighted in figma)

Andrea_Venturelli

a pure query works fine as a workaround solution like this test does

    db.transaction();
    QSqlQuery my_query(db);
    auto res = my_query.exec("UPDATE comments_table SET body_message = 'HELLO MY FRIENDS'::text WHERE id = 1;");
    if (res)
    {
        qDebug() << "All right";
        db.commit();
    } else {
        qDebug() << "problems:" << my_query.lastError().text();
    }

What I asked is why the QSqlRelationalTableModel generate an invalid query

JonB

@Andrea_Venturelli
Third time I have suggested the same starting point:

I asked you to verify whether it gets it right if you populate a few correct rows into all three tables and you just do a SELECT (i.e. when the QTableView fills). What statement does that generate?

We are trying to discover whether only UPDATE includes a reference to "comments_table"."vin". How does the generated SELECT with population from the FK table handle that column?

What I asked is why the QSqlRelationalTableModel generate an invalid query

I said maybe this is a problem in PSQL, which someone other than me may be able to check.

P.S.

        // set filter whereas txt is empty or not
        auto filter = (!txt.isEmpty())? QString("vin = '%1'").arg(txt) : "";
        this->m_model->setFilter(filter);
        this->m_model->select();

Can you remove this temporarily? Just not certain how the filter handles mention of non-existent/aliased column vin.

Andrea_Venturelli

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

I asked you to verify whether it gets it right if you populate a few correct rows into all three tables and you just do a SELECT (i.e. when the QTableView fills). What statement does that generate?

How can I can Add elements to the tables ? via query or are you suggesting with the method model->setData() ?
because model->setData() works fine when I tryed to change only the body_message column and no error was given

excuse me if i'm not understand your request right away ;)

Andrea_Venturelli

I leave the query to create the three tables to whoever wants to test the Postgres with the full db

/*
-------------------------------------------------------------------
                        VIN TABLE  
-------------------------------------------------------------------
*/
CREATE TABLE VIN_table (
    id BIGSERIAL UNIQUE,
    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 UNIQUE,
    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 UNIQUE,
    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;

Christian Ehrlicher

What exact Qt version do you use?

Andrea_Venturelli

@Christian-Ehrlicher said in Unable to save changes on QSqlRelationalTableModel to the PostGres DB:

What exact Qt version do you use?

i’m using Qt 6.8 as major version, but I can’t check until tomorrow the full versioning number

edited
Windows 11, Qt creator 14 (i think) and building with MinGw

Christian Ehrlicher

Windows 11, Qt creator 14 (i think) and building with MinGw

This does not matter

i’m using Qt 6.8

If you really use Qt6.8.x then please provide a minimal, compilable example. No mainwindow or ui, just a simple tableview + model + a simplified db schema.

Andrea_Venturelli

how can be simpler than that?
I already provided the query_code to create automatically the 3 tables. if you want you could delete some row and column. for just test purposes I guess you can ignore the “users_table” and the corresponding column present in the “comments_table” named “user_id”.

but all of this can be done simply deleting some part of the text provided before.

tell me if i’m understanding correctly:
you are asking for a Qt’s code without the use of .ui file and avoiding the use of ui->setup(this) and create an expressive code like:

auto my_wdg = qwidget(this)
my_wdg.setLayout(QVBoxLayout())
my_wdg.layout().addWidget(new QTableView())
ecc.. ecc…

anyway, tomorrow (italian timezone) I’ll put all the project on github so everybody can access it and run.