Page 1 of 1

Update or insert can fails without raise exception?

Posted: 25.04.2023, 09:39
by louis
Hello,

with FB 2.5.9 I have experiencing to a customer that Update or insert in some circumstances seem to fails.

The query (TZReadOnlyQuery) is quite simple:

Code: Select all

UPDATE OR INSERT INTO DOC (DOC_ID, XML)
VALUES (:ID, :xml);
DOC_ID = Integer
XML = Blob sub_type 1

:ID come from link with a Master Datasource.
:XML come from parameters like this:

Code: Select all

roDOCXml.Params.ParamByName('xml').AsString := xmlDoc.SaveToString;
roDOCXml.ExecSQL;
But in some case not updates the previous vales of blob field.

The query is trap in a try .. exception..end but not raise exception.

What I can control?

Thanks

Re: Update or insert can fails without raise exception?

Posted: 25.04.2023, 10:54
by marsupilami
Hello Louis,

I saw your mail on the firebird-support mailing list and hoped it wasn't Zeos you use ;)

Unfortunately possibilities are limited. Are there other processes accessing the table and changing the DOC table? And I assume you don't know (yet) how to reproduce this issue? Do you use transactions when doing these changes?

If it is possible, it might makle sense create an audit log in the hopes that we can see what happens on the Firebird side. The trace API was introduced in Firebird 2.5. There are some ressources on the internet regarding how to do that:
https://stackoverflow.com/questions/302 ... ql-queries
https://firebirdsql.org/rlsnotesh/rnfb25-trace.html
Unfortunately I didn't use these tools myself yet so I can only do guesswork and read documentation there.

These logs hopefully can show us what kind of error Firebird creates and sends to the client.

Also it might make sense to use TZSQLMonitor. Attach it to a Connection object, give it a file name and activate it.This might give insights in what Zeos receives and if it generates an Exception or not.

I assume that both options will decrease performance so you might want to warn your customer about that.

And last but not least you might want to try what happens if you use Zeos 8. The problem might be fixed there...

Best regards,

Jan

Re: Update or insert can fails without raise exception?

Posted: 26.04.2023, 12:16
by louis
Hello Jan,
the application that raise the issue runs on LAN with 3 PCs (1 is Server but also Client) + 2 Clients.
Occasionally ther is a PC connected with a Anydesk RemoteAccess with the PC who is Server and Client.

For now, We don't know to reproduce this issue :( that raised 13 times of about 10000 fiscal documents (invoices)

I modified the delphi code inserting a MastedDataset.Refresh to force resync of all detailDatasets then apply the update of a xml. May be an incorrect sync with ID field of MasterDatasource with the DetailDatasource in SQL code?

I even added some code to monitor the content of the xml file with the database if the issue recourse again I will try to monitor with the TZSQLMonitor.

For now I can't use Zeos 8 because I make a modification of a TZQuery used in the application who not work yet with Zeos8 :(

Thanks

Re: Update or insert can fails without raise exception?

Posted: 26.04.2023, 12:28
by marsupilami
Hello Louis,

hmm - that sounds really strange. Is there any chance that you replaced the wrong documents in the database?

Something else you might want to consider: You could check what happens if you read back the document and compare it to what you intended to save. With Firebird 2.5 you will have to read back the whole document to see if it matches - which should be no problem on a LAN.
You could consider switching to Firebird 4. There you can generate Checksums and compare them with the checksum generated on your client PC.

With best regards,

Jan

Re: Update or insert can fails without raise exception?

Posted: 26.04.2023, 14:40
by louis
Hello,
hmm - that sounds really strange. Is there any chance that you replaced the wrong documents in the database?
No.
In some cases the document is created several times and transmitted only at the end, for example:
(a) I start the creation and write two lines of detail and save the document.
(b) later I add or edit a detail line and save the document;
(c) later still or even the next day I add or edit more detail lines and save the document;
(d) later still or the next day I take the document saved in the database already validated and transmit it to the tax agency.

Each time I save, the application does not know whether the document is finished or not so it always validates it and if it is formally correct saves it for transmission to the tax agency.

The problem occurs, when it occurs, at point d) because the document saved in the database is not the last one but an intermediate one i.e. the one from point a) or point b) in the previous example.
If the document is created and finished at one time the problem cannot occur because the document is stored only once before being transmitted.

In the meantime of (a), (b), (c), (d), users do other things in the database, i.e., they move some goods, collect/pay invoices, record accounting, etc.

Thanks

Re: Update or insert can fails without raise exception?

Posted: 26.04.2023, 15:56
by marsupilami
Ok. Did you change TZQuery or did you change some other component? Maybe we can fix things so you can work with a current Zeos version again?

Re: Update or insert can fails without raise exception?

Posted: 26.04.2023, 18:42
by louis
Hi Jan,
I apperciate your help :)

I personalize only TZQuery.

I derived from TZAbstractDataqSet because TZquery was derived from it but not in 8.0 because TZQuery derive from TZAbstractRWTxnUpdateObjDataSet and if i derive from it have problems and the application non works.

The personalization are made because I when initiate to use Zeos i have wrong to understand Master-detail usage and instead using the correct properties "masterSource" + "Masterfields" I used only "DataSource". Change the paradigma is hard to do now with dozens of Master-Details relationship and new approfondite testing to verify the application works correctly.
In addition I use normally a MasterDataset.CacheUpdate = False and all DetailDatasets.CachedUpdate = True.

I Know is not btter way to use master-detail but it works like I prefer without relevant penalty.

In myZQuery I manage (centralize) all DetailDataset with my rules for ApplyUpdates, commitUpdates, cancelupdates, saving Masterdata before childrens because otherwise I not have primary key to link children.

Another issue if because are presents hundreds TFloatFields (for Currency) to change in TBCDFields but this is quite simple to substitute via "search and replace" in .dfm and .pas files.

My core business are not programmer but I 'm an accountant that create software to use in my own fiscal activity to manage the employe payrolls and accounting for my customer plus.

Manage the change from 7.2.0b to 8.0 for me is hard in this period because in Italy is a budgets period and tax payments and I have poor time to make those changes. In addition there is the need to implements new functions in the application to accomplish with fiscal rules which prevents me from implementing the upgrade to version 8.0 :(

Thanks

Re: Update or insert can fails without raise exception?

Posted: 28.04.2023, 08:13
by marsupilami
Hello Louis,

I think, I understand your problem. And even though I understand your limited ressources, you will need to think about some kind of an exit strategy because right now all the maintenance of that old Zeos version is on you whreas our official version sees bugfixes from selveral sources.

For now though, I have more questions ;) What are the problems when you try to upgrade your project from Zeos 7.2.0 to Zeos 7.2.14? Are there changes in our code that conflict with your changes? Or are there compile problems? Maybe we can find a way for you to work through this or to get the necessary modifications done?

With best regards,

Jan

Re: Update or insert can fails without raise exception?

Posted: 28.04.2023, 10:41
by louis
Hi Jan,
I really appreciate your support.

When I had to upgrade fron 7.2.0 to 7.2.10 about a year and a half ago it was necessary to upgrade all TFloatFields to TExtendedFields but now I don't remember well there was another problem during compilation.

I tried to upgrade to version 3 but found bugs which I reported and EgonHugeist fixed.

Running the application with version 7.3/8.0 trunk 7526 would crash under some circumstances, but I had to implement new features and suspended porting.

Around December 2022 I tried again and discovered more bugs that EgonHugeist fixed, but I had to suspend again :(

Now I plan to try again over the weekend if possible.

I would like to neglect version 7.2.14 because it requires more or less the same changes and try the newly downloaded 8.0 trunk 8015 version with TortoiseSVN.

For today I have completed the work planned for my accountant business and will start the modifications and wish me good luck :)

I will report here my steps which I hope lucky.

Thank you

Re: Update or insert can fails without raise exception?

Posted: 28.04.2023, 13:42
by louis
Hello,
ther are problems with Version 8.0 trunk 8015, I Try to 7.2.14 Stable.

Thanks

Re: Update or insert can fails without raise exception?

Posted: 02.05.2023, 10:12
by louis
Hello,
i complete the update to 7.2.14 stable with minor adjustments.
Now the app is testing to see if works right.

Thanks

Re: Update or insert can fails without raise exception?

Posted: 02.05.2023, 13:58
by marsupilami
Hello Louis,

that sounds promising. PLease keep me posted about any news.

With best regards,

Jan