Problem when updating a row with with select * - query

[geert]

Zeos 7.2.14 Stable, MariaDB, Rad Studio 11.2

These are preliminary test results showing a possible bug for a query with a wildchart.

I have a table with 99 columns, and a ZQuery :

select * from leveranc where stamnr=:stamnr

This table leveranc has a primary key stamnr.

I use an update object that has as its modifySQL :

update leveranc set naam=:naam, woon=:woon ...(and so on for 99 fields) where stamnr = :OLD_stamnr

The fields have TDbEdit components connected to them. The datasource is set to auto-edit-mode (which is the default).
The ZQuery is initially in "dsBrowse" state.
If I edit the DBEdit that corresponds to the 95th (or so) to 99th field in the table, and then Post, NO update query is performed. On re-opening the dataset, no data was changed.
If I edit the DBEdit components that correspond to the 1st to 95th field in the table, everything works fine.
If I edit one of the fields no. 95-99, together with a field no. 1-95, and then POST, then all data is saved (from all edited fields).
It seems as if changes in fields 95-99 are not seen by ZEOS, and it then decides not to generate an update-query.

On the other hand... if I write out all fields in the select query (and not use a * - wildchart) :

select naam, woon, ...(and so on for 99 fields) from leveranc where stamnr=:stamnr

then everything seems to work fine for all 99 fields. for now.

Is this behaviour known or familiar to anyone ?
Thanks,
Geert.

[marsupilami]

Hello Geert,

I have an application with Firebird where we use more than 99 fields. I don't see that behavior there. Could you maybe provide a small sample application that shows the problem?

With best regards,

Jan

[geert]

Jan,
sure, thanks for looking into it.
This example project (TestZeos.zip) exhibits the problem.
I have provided the mariadb "create" statement and 2 insert statements that you can use to setup a database ("testzeos") on a Mariadb server (here Mariadb 10.3.38) in a Memo-component.
Afterwards, click on the button "open" to open the row with stamnr=1 (=primary key).
If you change the first field (naam) and click "post", everything will work as expected.
If you change ONLY the second field (lev_num_boekh), which corresponds to the 90-something-th field in the database table, and click "post", the value will jump back to the previous value.
If you change both fields, everything is saved as expected ...

2023-03-28_15-42.png

Geert.

[marsupilami]

Hello Geert,

I didn't know that there are users out there who use Zeos with C++ Builder Soo - I think we fixed the problem. TThe problem was that TZUpdateSQL used the column count from the dataset whereas your select * from returned more columns than you have persistent fields in your DataSet. This lead to TZUpdateSQl not checking for changes in the last columns.
We changed that and now your example should work. Could you please test the latest Revision of Zeos from SVN?

Also: I saw that you use some decimal columns in your table. You might want to think about migrating to Zeos 8 because Zeos 8 supports these columns as TBCDField and TFMTBcdField. There you have more control over how the data gets displayed. But it will require work if you use persistent columns because Zeos will now expect TBCDFields (and TFMTBcdFields) instead of TFloatField.
One approach might be to not use persistent columns at all. If you need to set properties on columns, you might want to consider setting them in code in the AfterOpen event of TZQuery.

Best regards,

Jan