Unable to save changes on QSqlRelationalTableModel to the PostGres DB
-
@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
UPDATEincludes a reference to"comments_table"."vin". How does the generatedSELECTwith 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.@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 givenexcuse me if i'm not understand your request right away ;)
-
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; -
What exact Qt version do you use?
-
What exact Qt version do you use?
@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 -
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.
-
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.
-
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');@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
idis alsoPRIMARY KEY, whatever that syntax is for PSQL. (And while you are at it make sure theidcolumns in the other two table are primary key too. And you should always have a PK in tables anyway.)For
QSqlRelationalTableModelhttps://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" = $6The problem lies in the
AND "comments_table"."vin" = $3 AND. ThisUPDATEstatement is "pessimistic locking"-type, i.e. it'sWHEREeach column equals original value. It is possible that if Qt code sees a primary key it will generate anUPDATEstatement with justUPDATE "comments_table" SET ... WHERE "comments_table"."id" = $2and no other columns in the
WHEREsince"comments_table"."id"is known to be primary key. If it does that it won't try to include the pseudo-columnvinin 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
INSERTstatements in a script (like where you already have some withCREATE TABLE VIN_tableor you can use whatever tool PSQL has like a "workbench".The objective is to verify that when populated with rows from
comments_tableit shows the values from the foreign key tables, so you need suitable items in all 3 tables. Then we know it is generating a suitableSELECTstatement, unlike theUPDATE. 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". -
@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...
-
@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."
-
here, the GitHub link to the code I'm running :
https://github.com/aVenturelli-qt/Edit_data_in_QSqlRelationalTableModel/tree/mainthe 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 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
I am pleased my guess about Primary Key was correct. Without it the code for theUPDATE ... WHEREput in"comments_table"."vin" = $3and 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. -
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 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 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.
Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
Visit the Qt Academy at https://academy.qt.io/catalog -
@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.
@Christian-Ehrlicher
I think the OP now has resolved all problems (primary key needed for originalUPDATEerror, thesetData()mentioned was a red-herring).@Andrea_Venturelli
OOI I just came across SubmitAll() fails on QSqlRelationalTableModel which ends up concludingbut 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
OnManualSubmitstrategy. 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. -
@Christian-Ehrlicher
I think the OP now has resolved all problems (primary key needed for originalUPDATEerror, thesetData()mentioned was a red-herring).@Andrea_Venturelli
OOI I just came across SubmitAll() fails on QSqlRelationalTableModel which ends up concludingbut 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
OnManualSubmitstrategy. 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.@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. ;)
-
A Andrea_Venturelli has marked this topic as solved on
