Can't get DBGrid to update MySQL via ZQuery

[svtdoug]

I've run into a problem with DBGrid and ZQuery. I'm running Delphi 10.4.2. I have an application using TDBGrid, TDataSource, TZQuery, and TZConnection using a MySQL 8.0 DB. All works great, except I can't get changes made through the DBGrid to update the DB. I have another routine in code which inserts new records to the DB fine using the ZQuery and SQL text, but I also need to be able to edit records with the DBGrid.

Nothing is set to Read Only in the DBGrid or any of its column properties. I even tried setting a TDBEdit control on the form and trying to update one column in the db via it, and it fails. No error messages, the DB just does not get the update. I also added a TDBNavigator control, and using its commit button, and that does not work to update the DB.

Also of note, I can do a refresh of the ZQuery in code:

zquery1.Active:= False;
zquery1.SQL.Clear;
zquery1.SQL.Text:= 'Select * From mydata';
zquery1.ExecSQL;
zquery1.Active:= True;

and the updates from the DBGrid data are still there. So its like the ZQuery is remembering the changes, but they are not getting committed to the MySQL server.

With the above code, if I kill the ZConnection first, then reconnect, the data in the DBGrid reverts back to pre-edit conditions.

Any hints on how to get the DBGrid or DBEdit to post changes would be appreciated!

Thanks,
Doug

//Edit
I put the ZSQLMonitor on the form and wrote to a file. This is what is recorded when I edit a cell in the DBgrid and move to another cell.

"2021-11-21 18:29:44 cat: Prepare, proto: mysql-5, msg: Statement 7 : SHOW KEYS FROM exercisetracker.commlog
2021-11-21 18:29:44 cat: Execute prepared, proto: mysql-5, msg: Statement 7
2021-11-21 18:29:44 cat: Prepare, proto: mysql-5, msg: Statement 8 : UPDATE exercisetracker.commlog SET `RecNo`=?,`Op`=?,`Date`=?,`Time`=?,`ReporterCall`=?,`ReporterName`=?,`BlockTeam`=?,`Receiver`=?,`IncidNum`=?,`Resolved`=?,`What`=?,`WhereField`=?,`Status`=?,`Resolution`=?,`ResolvedTime`=? WHERE `RecNo`=? AND `Date`=? AND `Time`=? AND `ReporterCall`=? AND `BlockTeam`=? AND `Receiver`=? AND `IncidNum`=?
2021-11-21 18:29:44 cat: Execute prepared, proto: mysql-5, msg: Statement 8"

I don't know what to make of that...

Doug

[aehimself]

What is the transaction isolation set to on your ZConnection component?

[marsupilami]

Did you possibly disable autocommit on the TZConnection?

[svtdoug]

Thanks for the replies!

AE - Transaction isolation on the ZConnection is set to none - I presume the default.

marsupilami - AutoCommit was set to false! Setting it to True, now the grid updates the DB! Phew! Thanks so much! Must have clicked that off in testing, trying to click on something else. Another lesson learned! I figured it was something simple, one little switch.

Working really well now!

Many thanks.
Doug