Qt5 submitAll seems to work different than Qt4 did

Printable View

I have a table that contains recipe records. The application has been around since 2004. Now moving it into Qt5. I'm having issues updating a field in an existing record. When the operator wants to save a new recipe with the same recipe code. I need to set the current recipe "isactive" boolean false. This has worked fine until now. The Qt4 code looks like ...
Code:

QString sqlWhere; sqlWhere.append("recipecode='"); sqlWhere.append(cboRecipeSelect->currentText()); sqlWhere.append( "' AND isactive=true"); QSqlTableModel recipeTable; recipeTable.setTable("recipe"); recipeTable.setFilter(sqlWhere); recipeTable.select(); QSqlRecord recRecipe = recipeTable.record(0); if (!recRecipe.isEmpty()) // backwards way to say record exists { // if we get this far, it's an update recRecipe.setValue("isactive",false); recipeTable.setRecord(0,recRecipe); recipeTable.submitAll(); // and update the existing record qDebug () << "updating recipe record to not active " <<recipeTable.lastError().text();
qDebug shows " ". And the field in the record remains "true".

What am I missing / failing to understand?

1 Attachment(s)

Re: Qt5 submitAll seems to work different than Qt4 did

Here is what the Qt5 code looks like at this time.

Code:

int rowcount = recipeTable.rowCount();
      qDebug () << "recipe select returned " << rowcount << " rows";
 
      QSqlRecord recRecipe = recipeTable.record(0);
      if (!recRecipe.isEmpty()) // backwards way to say record exists
        {
        // if we get this far, it's an update
        recRecipe.setValue("isactive",false);
        // recipeUpTable.setData(recipeUpTable.index(0,2), false);
        bool boolRecSubmit = recipeTable.submitAll(); // and update the existing record
 
        qDebug () << "value for isactive is " << recRecipe.value("isactive");
        qDebug () << "updating recipe returned " << boolRecSubmit;
        qDebug () << "updating recipe record to not active " <<recipeTable.lastError().text();

Application Output ...
recipe select returned 1 rows
value for isactive is QVariant(bool, false)
updating recipe returned true
updating recipe record to not active " "

Somehow the change is not reflected in the database table.

Attachment 13717

The first row should have isactive false.

6th September 2021, 17:23
d_stranz

Re: Qt5 submitAll seems to work different than Qt4 did

QSqlTableModel::submitAll() returns a bool status, which you aren't checking. After you fix that (and all of the other calls that also return a status) what is the return value?

Re: Qt5 submitAll seems to work different than Qt4 did

New code where I check return status (I believe I found them all):

Code:

sqlWhere.append("recipecode='");
      sqlWhere.append(cboRecipeSelect->currentText());
      sqlWhere.append( "' AND isactive=true");
      QSqlTableModel recipeTable;
      recipeTable.setTable("recipe");
      recipeTable.setFilter(sqlWhere);
      bool boolSelect = recipeTable.select();
      qDebug () << "recipe select a record returned " << boolSelect;
      int rowcount = recipeTable.rowCount();
      qDebug () << "the select returned " << rowcount << " rows";
 
      QSqlRecord recRecipe = recipeTable.record(0);
      if (!recRecipe.isEmpty()) // backwards way to say record exists
        {
        // if we get this far, it's an update
        recRecipe.setValue("isactive",false);
        // recipeUpTable.setData(recipeUpTable.index(0,2), false);
        bool boolRecSubmit = recipeTable.submitAll(); // and update the existing record
 
        qDebug () << "value for isactive is " << recRecipe.value("isactive");
        qDebug () << "submitAll call return value " << boolRecSubmit;
        qDebug () << "submitAll error message " <<recipeTable.lastError().text();
 
        // 
        }

Qt Creator Application Output:
recipe select a record returned true
the select returned 1 rows
value for isactive is QVariant(bool, false)
submitAll call return value true
submitAll error message " "

Still have two active recipes in the database recipe table.

I'm missing something somewhere ...

7th September 2021, 16:05
d_stranz

Re: Qt5 submitAll seems to work different than Qt4 did

Quote:

I'm missing something somewhere ...

What are you using to view the DB contents after the changes have been submitted? Something within the same app or an external DB viewer program (like SQLite's DB Browser)? If it is within the same program, I am wondering that the SQL calls are working fine, it is the display that isn't receiving the updated content because a signal / slot connection is missing?
7th September 2021, 20:47
don@n-thOrder.com

Re: Qt5 submitAll seems to work different than Qt4 did

I've been using LibreOffice Base with the postgresql driver. I'm careful to not have the database table open in Base during testing. It shouldn't matter, but I'm trying to reduce interactions.

i also see the same results in pgamdin4.

Some system information:

Ubuntu Server 20.04.2
postgresql 12
QT Creator (docs & examples) 5.12.8
libqt5sql5-psql is version (5.12.8+dfsg-0ubuntu1).

The app reloads a combo box which now shows 2 "recipes" as both have "isactive" true.

I have a mac keyboard wired into the system. I have not found a way to get a screen capture of the combo box pulldown.

What other information might be useful?
8th September 2021, 16:04
d_stranz

Re: Qt5 submitAll seems to work different than Qt4 did

Don't know what else to tell you. If you still have a debuggable Qt4 version of the program, you can try stepping through the call to submitAll and see what the differences are, if any. I suspect it might be a difference at the driver level since the Qt code is the same.

Quote:

I'm careful to not have the database table open in Base during testing.

You can probably open it read-only, but you are right - it shouldn't matter since PostgreSQL should allow multiple connections.
9th September 2021, 01:29
don@n-thOrder.com

Re: Qt5 submitAll seems to work different than Qt4 did

I set the postgresql logging level to "error".

My comments / thoughts are in << ?>>

<<initial select? boolSelect = recipeTable.select();>>
2021-09-08 23:18:51.693 UTC [2238] strata@strata LOG: statement: Select * FROM recipe WHERE recipecode='1000' AND isactive=true

<<no idea>>
2021-09-08 23:18:51.694 UTC [2238] strata@strata LOG: statement: SELECT relname FROM pg_class WHERE pg_class.oid = 16415

<<grabbing the record? recRecipe = recipeTable.record(0))
2021-09-08 23:20:10.989 UTC [2238] strata@strata LOG: statement: SELECT pg_attribute.attname, pg_attribute.atttypid::int, pg_attribute.attnotnull, pg_attribute.attlen, pg_attribute.atttypmod, pg_get_expr(pg_attrdef.adbin, pg_attrdef.adrelid) FROM pg_class, pg_attribute LEFT JOIN pg_attrdef ON (pg_attrdef.adrelid = pg_attribute.attrelid AND pg_attrdef.adnum = pg_attribute.attnum) WHERE pg_table_is_visible(pg_class.oid) AND pg_class.relname = 'recipe' AND pg_attribute.attnum > 0 AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attisdropped = false ORDER BY pg_attribute.attnum

<<submitAll?>>
2021-09-08 23:20:10.996 UTC [2238] strata@strata LOG: statement: SELECT pg_attribute.attname, pg_attribute.atttypid::int, pg_class.relname FROM pg_attribute, pg_class WHERE pg_table_is_visible(pg_class.oid) AND pg_class.oid IN (SELECT indexrelid FROM pg_index WHERE indisprimary = true AND indrelid IN (SELECT oid FROM pg_class WHERE relname = 'recipe')) AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attisdropped = false ORDER BY pg_attribute.attnum

[[This code has not been shared (yet) as the insert works fine]]
<< grabbing a blank record for inserting the new record with isactive=true? recRecipe = recipeTable.record()>>
2021-09-08 23:20:10.998 UTC [2238] strata@strata LOG: statement: SELECT "recipecode", "reciperev", "isactive", "speedsp", "coatersp", "unwindtensionsp", "unwindtensiondev", "tension1sp", "tension1dev", "tension2sp", "tension2dev", "laminatortensionsp", "laminatortensiondev", "rewindtensionsp", "rewindtensiondev", "impressionrollhi", "impressionrolllo", "laminatorrollhi", "laminatorrolllo", "fusionintemphi", "fusionintemplo", "fusionouttemphi", "fusionouttemplo", "coatingtemphi", "coatingtemplo", "drumtemphi", "drumtemplo", "preheatsp", "preheatdev", "postheatsp", "postheatdev", "o2sp", "o2dev", "viscositysp", "viscositydev", "ir2a", "ir2b", "ir3a", "ir3b", "ir4a", "ir4b", "ir5a", "ir5b" FROM recipe WHERE recipecode='1000' AND isactive=true

<<getting ready to insert? recipeTable.insertRecord(-1,recRecipe)>>
2021-09-08 23:20:11.000 UTC [2238] strata@strata LOG: statement: PREPARE qpsqlpstmt_1 AS INSERT INTO recipe ("recipecode", "reciperev", "isactive", "speedsp", "coatersp", "unwindtensionsp", "unwindtensiondev", "tension1sp", "tension1dev", "tension2sp", "tension2dev", "laminatortensionsp", "laminatortensiondev", "rewindtensionsp", "rewindtensiondev", "impressionrollhi", "impressionrolllo", "laminatorrollhi", "laminatorrolllo", "fusionintemphi", "fusionintemplo", "fusionouttemphi", "fusionouttemplo", "coatingtemphi", "coatingtemplo", "drumtemphi", "drumtemplo", "preheatsp", "preheatdev", "postheatsp", "postheatdev", "o2sp", "o2dev", "viscositysp", "viscositydev", "ir2a", "ir2b", "ir3a", "ir3b", "ir4a", "ir4b", "ir5a", "ir5b") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43)
2021-09-08 23:20:11.001 UTC [2238] strata@strata LOG: statement: EXECUTE qpsqlpstmt_1 ('1000', '2021-09-08T19:20:10', TRUE, '59', '90', '1.5', '0.2', '1.2', '0.4', '1.7', '0.4', '0', '0.2', '1', '0.2', '45', '25', '45', '25', '120', '50', '180', '50', '98', '88', '98', '88', '100', '10', '100', '10', '4000', '500', '0', '0', 1, 1, 1, 1, 1, 1, 1, 1)

<<??>>
2021-09-08 23:20:11.001 UTC [2238] strata@strata DETAIL: prepare: PREPARE qpsqlpstmt_1 AS INSERT INTO recipe ("recipecode", "reciperev", "isactive", "speedsp", "coatersp", "unwindtensionsp", "unwindtensiondev", "tension1sp", "tension1dev", "tension2sp", "tension2dev", "laminatortensionsp", "laminatortensiondev", "rewindtensionsp", "rewindtensiondev", "impressionrollhi", "impressionrolllo", "laminatorrollhi", "laminatorrolllo", "fusionintemphi", "fusionintemplo", "fusionouttemphi", "fusionouttemplo", "coatingtemphi", "coatingtemplo", "drumtemphi", "drumtemplo", "preheatsp", "preheatdev", "postheatsp", "postheatdev", "o2sp", "o2dev", "viscositysp", "viscositydev", "ir2a", "ir2b", "ir3a", "ir3b", "ir4a", "ir4b", "ir5a", "ir5b") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43)

<<??>>
2021-09-08 23:20:11.002 UTC [2238] strata@strata LOG: statement: SELECT "recipecode", "reciperev", "isactive", "speedsp", "coatersp", "unwindtensionsp", "unwindtensiondev", "tension1sp", "tension1dev", "tension2sp", "tension2dev", "laminatortensionsp", "laminatortensiondev", "rewindtensionsp", "rewindtensiondev", "impressionrollhi", "impressionrolllo", "laminatorrollhi", "laminatorrolllo", "fusionintemphi", "fusionintemplo", "fusionouttemphi", "fusionouttemplo", "coatingtemphi", "coatingtemplo", "drumtemphi", "drumtemplo", "preheatsp", "preheatdev", "postheatsp", "postheatdev", "o2sp", "o2dev", "viscositysp", "viscositydev", "ir2a", "ir2b", "ir3a", "ir3b", "ir4a", "ir4b", "ir5a", "ir5b" FROM recipe WHERE "recipe"."recipecode" = '1000' AND "recipe"."reciperev" = TIMESTAMP WITH TIME ZONE '2021-09-08T23:20:10.000Z'

<<reloading the recipe combo box>>
2021-09-08 23:20:12.796 UTC [2238] strata@strata LOG: statement: SELECT recipecode FROM recipe WHERE isactive=true ORDER BY recipecode
2021-09-08 23:20:12.797 UTC [2238] strata@strata LOG: statement: Select * FROM recipe WHERE recipecode='1000' AND isactive=true

<<shutting down>>
2021-09-08 23:20:12.800 UTC [2238] strata@strata LOG: statement: DEALLOCATE qpsqlpstmt_1
2021-09-08 23:21:27.180 UTC [1066] LOG: received fast shutdown request
2021-09-08 23:21:27.183 UTC [1066] LOG: aborting any active transactions
2021-09-08 23:21:27.189 UTC [1066] LOG: background worker "logical replication launcher" (PID 1088) exited with exit code 1
2021-09-08 23:21:27.203 UTC [1083] LOG: shutting down
2021-09-08 23:21:27.224 UTC [1066] LOG: database system is shut down

Does this provide more information?
9th September 2021, 16:46
d_stranz

Re: Qt5 submitAll seems to work different than Qt4 did

Quote:

Does this provide more information?

I am sorry, but I have reached the limit of my ideas. Perhaps someone else with more experience can provide some help.

Have you tried doing this with a "toy" database - something very simple where you do the same set of SQL procedures, but with a very small and simple DB? That might help you determine whether it is a Qt problem, a PostgreSQL problem, a DB schema problem, or some combination.
11th September 2021, 14:53
don@n-thOrder.com

Re: Qt5 submitAll seems to work different than Qt4 did

The thread title should read more like ... "something changed how timestamps are handled.

Found the issue by turning postgresql error logging. Followed the instructions here:
https://tableplus.com/blog/2018/10/h...ostgresql.html

The log shows:

2021-09-10 14:04:08.538 UTC [4419] strata@strata LOG: statement: PREPARE qpsqlpstmt_1 AS UPDATE recipe SET "isactive"=$1 WHERE "recipe"."recipecode" = $2 AND "recipe"."reciperev" = $3

2021-09-10 14:04:08.539 UTC [4419] strata@strata LOG: statement: EXECUTE qpsqlpstmt_1 (FALSE, '1000', TIMESTAMP WITH TIME ZONE '2021-09-10T11:46:41.000Z')

What I noticed is the TIMESTAMP WITH TIME ZONE is 4 hours off from my local time. Looking at the table definition in pgadmin4 reciprev said

reciperev timestamp without time zone NOT NULL,

I changed the column definition to timestamp with time zone and the recipe update (isactive from true -> false) works as expected.

I do not know where how/where the change to "with time zone" occurred. Maybe in the upgrade from postresql 9.3 to 12. Qt4 to Qt5?
11th September 2021, 16:17
d_stranz

Re: Qt5 submitAll seems to work different than Qt4 did

Quote:

I do not know where how/where the change to "with time zone" occurred. Maybe in the upgrade from postresql 9.3 to 12. Qt4 to Qt5?

No idea. Both of those are major version updates so it could have been in either one. The Qt development and deployment architectures changed quite a lot going from 4 to 5.

Glad you were able to resolve the problem. Sorry I could be of more help.