[SOLVED] 0 recod(s) updated. Only one record should have been updated.

[vejrous]

Hi,
I have quite complicated query that join multiple tables so for updating TZUpdateSQL is used.

ModifySQL code:

Code: Select all

UPDATE
  Job
SET
  ID = :ID,
  Owner = :Owner,
  ...
WHERE
  ID = :Old_ID

This works OK, but ONLY if the record really changes. When I modify some field but the value at the end remains the same error "0 recod(s) updated. Only one record should have been updated." is raised.

Thans anybody for help.

[marsupilami]

Hello vejrous,

which driver do you use (firebird, postgres, ...)? And which database do you work with (Firebird 2.5, PostgreSQL 9.6, MSSQL 2008, ...)?

Usually Zeos expects that after an update the database reports one modified record. Maybe in the case where you don't change any data the database reports no changed records which in turn leads Zeos to belive that the updated record already was deleted on the database. In the end we will have to work out a solution on how Zeos trets this special case...

Best regards,

Jan

[vejrous]

Hi,
I am using MySQL 5.1.73
TZConnection.Protokol=mysql5
SVN: http://svn.code.sf.net/p/zeoslib/code-0 ... .2-patches

Of couse that setting the same value is specific, but this means it also happens when using LookupComboBox (when user wants to see what values are available: he clicks the ComboBox, DataSource is set to dsEdit, then he may keep the same value).

[marsupilami]

How is the property UpdateMode of your DataSet set?

[vejrous]

Dataset is set to:
UpdateMode=umUpdateChanged
I am also adding image, to be sure.

[marsupilami]

Hello vejrous,

the reason for this is that MySQL behaves differently from other databases when it comes to returning the affected rows count - it returns the changed rows count only. See https://dev.mysql.com/doc/refman/8.0/en/mysql-affected-rows.html for the original explanation.

For Zeos 7.2 please add the following line to your TZConnection.Properties:
CLIENT_FOUND_ROWS=1
This will enable the CLILENT_FOUND_ROWS option which will change the behaviour of MySQL according to the documentation in the link above.

For Zeos 7.3 we are discussing wether to enable this option by default. To follow this discussion please have a look at the following ticket: https://sourceforge.net/p/zeoslib/tickets/270/

Best regards,

Jan

[vejrous]

Thanks for help, problem solved now. Will watch the ticket to see, how it develops.