Access violation in ZQuery.Refresh

[atendimentobed]

Hi,

After migrating a project to Delphi 11.2 + MySQL 5.0 + Zeos 8.0 I noticed other strange behavior that did not occur previously in the 7.1.4-stable version of Zeos used in Delphi 2010.

To make it easier, I reproduced the problem in a simple scenario.
Project Delphi: https://mega.nz/file/PBM3XYJb#tVaTqSJGQ ... 3mTPg76ZZE
Script SQL: https://mega.nz/file/ectDCYST#GKyfpOq64 ... LIBAIGMdc0

I created a new VCL project with just one form and the components below:


I also created a test2 database with just one table named tbl_test2.


I also put some rows in the table for the example.

ZQuery1 is linked to the tbl_test2 table, DataSource1 is linked to ZQuery1, ZQuery1 is pointed to ZUpdateSQL1 to execute the DML commands and DBEdit1 pointed to DataSource1, field name. A normal scenario.

In the Append button I execute ZQuery1.Append, in the Delete button I execute ZQuery1.Delete and in the Post button I execute two commands:

Code: Select all

  ZQuery1.Post;
  ZQuery1.Refresh;

If I click the Delete button, then click the Append button, then fill in a value in DBEdit, and then click the Post button, an exception occurs in the code of ZQuery1.Refresh.

This behavior happened only in the new Zeos 8.0 + Delphi 11.2 scenario.

Can anybody help me?

Thx

[marsupilami]

Hello atendimentobed,

the problem is the combination of using TZUpdateSQL and having no valid RefreshSQL in TZUpdateSQL. With TZUpdateSQL you tell Zeos to not worry about data and that you will take care of everything.

So - you insert a record but provide no value for the ID field. Zeos assumes that the value is null. Also you don't have a valid RefreshSQL in TZUpdateSQL. So Zeos tries to fetch a record but since the key value (id) is nil, it will not get a record from MySQL.

So - either don't use the TZUpdateSQL (in this case Zeos will query MySQL for the generated id number) or provide a correct value for the ID field - possibly in the BeforePost event of TZQuery.

With best regards,

Jan

[atendimentobed]

Hi marsupilami,

Thx for your explanation. In fact, I didn't even know about this RefreshSQL property in ZUpdateSQL, because in the previous version I never used it and it never gave an error (Delphi 2010 + Zeos 7.1.4 stable).

I just filled in DeleteSQL, InsertSQL and ModifySQL and automatically following the sequence below:


So how should I handle RefreshSQL?

[marsupilami]

Honestly I don't use these generators. I simply write my SQL into the properties.

Also I wonder if you really need to use TZUpdateSQL.

[atendimentobed]

Hi marsupilami,

My project already has more than 100 ZUpdateSQL, I can't stop using it.

My question is about the purpose of ZUpdateSQL's RefreshSQL property, I didn't quite understand the purpose.

The InsertSQL and ModifySQL commands, for example, occur at the time of ZQuery1.Post, depending on whether ZQuery1 is in insert or update mode, correct? And the DeleleSQL commands occur at the time of ZQuery1.Delete, correct?

But I didn't understand how to use RefreshSQL or when it is triggered. Using the example I attached in the post, I put a DbGrid1 and the following SQL script in RefreshSQL:

Code: Select all

SELECT * FROM TBL_TEST2 ORDER BY id DESC LIMIT 1

Project Delphi Modified: https://mega.nz/file/3MMlVS5a#IeaZegNq8 ... MXgiHGjjRQ

But when the ZQuery1.Refresh command is executed, ALL records are still being displayed, not just the last one in the grid.

Wouldn't it be correct at this point to run the RefreshSQL script?

[marsupilami]

Using ZUpdateSQL usually is only intended if Zeos cannot figure out things for itself. Like when you do a join and want rows to be updateable. For the standard cases ('select field1, field2, field3 from sometable where ....') usually it isn't necessary to use TZUpdateSQL.

RefreshSQL is a statement that is used if you call TZQuery.Refresh. It should refresh the currently selected row. So in your case it might be something like "select * from tbl_test2 where is = :NEW_ID".

With best regards,

Jan

[atendimentobed]

Hi marsupilami,

Thx for explanation...

I always used ZUpdateSQL filling InsertSQL, ModifySQL and DeleteSQL because I thought it was necessary for ZQuery1.Append, ZQuery1.Edit, ZQuery1.Delete, ZQuery1.Post to work. It is not necessary?

[marsupilami]

ZUpdateSQL usuall is only necessary if you want queries that query from more than one table to become writable or if you want to make a readonly view writable or a selectable stored procedure or something like that.
For most tasks it isn't necessary. TZQuery tries to build the correct statements for insert, update, delete and refresh on its own, so you don't have to worry about them.

[atendimentobed]

Hi marsupilami,

Nice, I didn't know... I always used ZUpdateSQL without needing. Thx for explanation.

Do you have any examples of using RefreshSQL in ZUpdateSQL so I can better understand when and how to use it?

[marsupilami]

RefreshSQl is used for refreshing a single row. In your example it could be something like:

Code: Select all

SELECT * FROM TBL_TEST2 where id = :NEW_ID

[atendimentobed]

So - you insert a record but provide no value for the ID field. Zeos assumes that the value is null. Also you don't have a valid RefreshSQL in TZUpdateSQL. So Zeos tries to fetch a record but since the key value (id) is nil, it will not get a record from MySQL.

Hi my friend...

I was looking at your explanation and noticed that this doesn't happen.

As you said, when inserting a new record, I don't inform the id value, but that's not a problem. Do the test.

Run my project, try to insert a record by clicking the Append button, fill in the name and click Post. Even not informing the id value the Refresh works.

The access violation only happens when I first click on Delete, as I explained at the beginning of the Post.

Sorry for the delay in realizing this, but when I translated your explanation in Google Translator it was not clear, my english is bad. kkkkkkkkkk