Results 1 to 11 of 11

Thread: Qt5 submitAll seems to work different than Qt4 did

  1. #1
    Join Date
    Sep 2021
    Posts
    6
    Qt products
    Qt5
    Platforms
    Unix/X11

    Default Qt5 submitAll seems to work different than Qt4 did

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

    Qt Code:
    1. QString sqlWhere;
    2.  
    3. sqlWhere.append("recipecode='");
    4. sqlWhere.append(cboRecipeSelect->currentText());
    5. sqlWhere.append( "' AND isactive=true");
    6. QSqlTableModel recipeTable;
    7. recipeTable.setTable("recipe");
    8. recipeTable.setFilter(sqlWhere);
    9. recipeTable.select();
    10. QSqlRecord recRecipe = recipeTable.record(0);
    11. if (!recRecipe.isEmpty()) // backwards way to say record exists
    12. {
    13. // if we get this far, it's an update
    14. recRecipe.setValue("isactive",false);
    15. recipeTable.setRecord(0,recRecipe);
    16. recipeTable.submitAll(); // and update the existing record
    17. qDebug () << "updating recipe record to not active " <<recipeTable.lastError().text();
    To copy to clipboard, switch view to plain text mode 

    qDebug shows " ". And the field in the record remains "true".

    What am I missing / failing to understand?

  2. #2
    Join Date
    Sep 2021
    Posts
    6
    Qt products
    Qt5
    Platforms
    Unix/X11

    Default Re: Qt5 submitAll seems to work different than Qt4 did

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

    Qt Code:
    1. int rowcount = recipeTable.rowCount();
    2. qDebug () << "recipe select returned " << rowcount << " rows";
    3.  
    4. QSqlRecord recRecipe = recipeTable.record(0);
    5. if (!recRecipe.isEmpty()) // backwards way to say record exists
    6. {
    7. // if we get this far, it's an update
    8. recRecipe.setValue("isactive",false);
    9. // recipeUpTable.setData(recipeUpTable.index(0,2), false);
    10. bool boolRecSubmit = recipeTable.submitAll(); // and update the existing record
    11.  
    12. qDebug () << "value for isactive is " << recRecipe.value("isactive");
    13. qDebug () << "updating recipe returned " << boolRecSubmit;
    14. qDebug () << "updating recipe record to not active " <<recipeTable.lastError().text();
    To copy to clipboard, switch view to plain text mode 

    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.

    2ActiveRecipes.png

    The first row should have isactive false.

  3. #3
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,133
    Thanks
    293
    Thanked 850 Times in 837 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default 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?
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  4. #4
    Join Date
    Sep 2021
    Posts
    6
    Qt products
    Qt5
    Platforms
    Unix/X11

    Default Re: Qt5 submitAll seems to work different than Qt4 did

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

    Qt Code:
    1. sqlWhere.append("recipecode='");
    2. sqlWhere.append(cboRecipeSelect->currentText());
    3. sqlWhere.append( "' AND isactive=true");
    4. QSqlTableModel recipeTable;
    5. recipeTable.setTable("recipe");
    6. recipeTable.setFilter(sqlWhere);
    7. bool boolSelect = recipeTable.select();
    8. qDebug () << "recipe select a record returned " << boolSelect;
    9. int rowcount = recipeTable.rowCount();
    10. qDebug () << "the select returned " << rowcount << " rows";
    11.  
    12. QSqlRecord recRecipe = recipeTable.record(0);
    13. if (!recRecipe.isEmpty()) // backwards way to say record exists
    14. {
    15. // if we get this far, it's an update
    16. recRecipe.setValue("isactive",false);
    17. // recipeUpTable.setData(recipeUpTable.index(0,2), false);
    18. bool boolRecSubmit = recipeTable.submitAll(); // and update the existing record
    19.  
    20. qDebug () << "value for isactive is " << recRecipe.value("isactive");
    21. qDebug () << "submitAll call return value " << boolRecSubmit;
    22. qDebug () << "submitAll error message " <<recipeTable.lastError().text();
    23.  
    24. //
    25. }
    To copy to clipboard, switch view to plain text mode 

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

  5. #5
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,133
    Thanks
    293
    Thanked 850 Times in 837 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: Qt5 submitAll seems to work different than Qt4 did

    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?
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  6. #6
    Join Date
    Sep 2021
    Posts
    6
    Qt products
    Qt5
    Platforms
    Unix/X11

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

  7. #7
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,133
    Thanks
    293
    Thanked 850 Times in 837 Posts
    Qt products
    Qt5
    Platforms
    Windows

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

    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.
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  8. #8
    Join Date
    Sep 2021
    Posts
    6
    Qt products
    Qt5
    Platforms
    Unix/X11

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

  9. #9
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,133
    Thanks
    293
    Thanked 850 Times in 837 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: Qt5 submitAll seems to work different than Qt4 did

    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.
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  10. #10
    Join Date
    Sep 2021
    Posts
    6
    Qt products
    Qt5
    Platforms
    Unix/X11

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

  11. #11
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,133
    Thanks
    293
    Thanked 850 Times in 837 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: Qt5 submitAll seems to work different than Qt4 did

    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.
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

Similar Threads

  1. submitALL, QSqlTableModel and QTableView
    By scott_hollen in forum Qt Programming
    Replies: 3
    Last Post: 9th October 2011, 09:38
  2. submitAll() not work for updating fields
    By brokensword in forum Qt Programming
    Replies: 1
    Last Post: 7th October 2008, 12:02
  3. QSqlTableModel->submitAll()
    By maxel in forum Qt Programming
    Replies: 1
    Last Post: 6th September 2008, 08:30
  4. QSqlRelationalTableModel and SubmitAll()
    By kroenecker in forum Qt Programming
    Replies: 7
    Last Post: 22nd January 2008, 15:53
  5. submitAll() : database error
    By locus in forum Qt Programming
    Replies: 3
    Last Post: 27th January 2007, 05:49

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.