Edited detail record can't be posted

Discussion of open issues, suggestions and bugs regarding IBDAC (InterBase Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
TheFastCoder
Posts: 5
Joined: Mon 18 Jun 2018 06:40

Edited detail record can't be posted

Post by TheFastCoder » Fri 14 Sep 2018 13:17

Hi,

I'm having a master detail relationship (Firebird with IBDAC, Delphi 10.2.2). A TIBCQuery is the master table and a TIBCTable is the detail table. This worked severals years! without any problems. A few weeks ago I've updated to IBDAC 6.2.8. Now I have this problem:

A few master records are present. I insert a detail record and post it. I edit the detail record and want to post it. Now I get this error:

EDatabaseError 'Refresh failed. Found 0 records'

Now I go to another master record and than back to the previous one. Now I can edit and post the detail record which triggered the error.

Why is that? Is it bad to use a TIBCQuery with TIBCTable in that way? (If I replace the TIBCTable with a TIBCQuery everything is fine).

Thank you in advance!

ViktorV
Devart Team
Posts: 2445
Joined: Wed 30 Jul 2014 07:16

Re: Edited detail record can't be posted

Post by ViktorV » Fri 14 Sep 2018 13:30

Unfortunately, we could not reproduce the specified behavior.
If this is so, in order for us to be able to give you a detailed answer, we need a sample demonstrating the behavior you mentioned. Therefore, please, compose a small sample demonstrating the described behavior and send it to us using the contact form https://devart.com/company/contactform.html including database objects creating scripts.

TheFastCoder
Posts: 5
Joined: Mon 18 Jun 2018 06:40

Re: Edited detail record can't be posted

Post by TheFastCoder » Fri 04 Jan 2019 09:28

I've made a small demo to reproduce the error and send it to you using your contant form.

ViktorV
Devart Team
Posts: 2445
Joined: Wed 30 Jul 2014 07:16

Re: Edited detail record can't be posted

Post by ViktorV » Fri 04 Jan 2019 12:20

Thank you for the information. We will investigate this behavior of IBDAC and will inform you about the result.

ViktorV
Devart Team
Posts: 2445
Joined: Wed 30 Jul 2014 07:16

Re: Edited detail record can't be posted

Post by ViktorV » Fri 11 Jan 2019 09:06

To solve the issue, please try to set the TIBCTable.DMLRefresh property to True. For example:

Code: Select all

  TabVorgangPos.DMLRefresh := True;

TheFastCoder
Posts: 5
Joined: Mon 18 Jun 2018 06:40

Re: Edited detail record can't be posted

Post by TheFastCoder » Mon 14 Jan 2019 10:20

Hi, thank you for your quick reply.

Why is it working if I set TIBCTable.DMLRefresh := True; Why do other TIBCTable doesn't need this property set to true? I've used a lot of TIBCTable's in many projects and never needed to set this property.

I've extended my demo project and mailed it to you. Note that DMLRefresh is still false here for testing. Now the new project contains a TIBCQuery named "QueryVorgangPos". With the button "Toggle IBCQuery <-> IBCTable" you can toggle between the TIBCQuery ("QueryVorgangPos") and the TIBCTable ("TabVorgangPos"). As you can see adding and editing a new record is *not* working with the TIBCTable. But with the TIBCQuery it works. I've found out that it has to do with the SQL code that both components use for locking the record. TIBCTable uses a SQL code different from TIBCQuery. If I put the SQL code from the TIBCTable into the TIBCQuery than the TIBCQuery can't edit/post the record too! That proves that the SQL code from the TIBCTable is wrong.

Code: Select all

--This works. Only VGNR and POS are primary keyfields 
SELECT NULL FROM VORGANGPOS
WHERE
VGNR = :Old_VGNR AND POS = :Old_POS
FOR UPDATE WITH LOCK

Code: Select all

--This is the code from the TIBCTable. This doesn't work!
SELECT NULL FROM VORGANGPOS
WHERE
VGNR = :Old_VGNR AND POS = :Old_POS AND ARTIKELNR IS NULL AND ARTIKEL = :Old_ARTIKEL AND MENGE = :Old_MENGE AND NETTO = :Old_NETTO AND BRUTTO = :Old_BRUTTO AND MWSTSATZ = :Old_MWSTSATZ AND ERLOESKONTO IS NULL AND MENGENEINHEITID IS NULL AND RABATT IS NULL AND RABATTTYP IS NULL AND GROESSE IS NULL AND FARBE IS NULL AND PROVISION IS NULL AND PROVISIONTYP IS NULL AND NOTIZ IS NULL AND ID = :Old_ID AND NETTOSUMME IS NULL AND BRUTTOSUMME IS NULL AND LFZEITRAUMVON IS NULL AND LFZEITRAUMBIS IS NULL AND MENGEERLEDIGT = :Old_MENGEERLEDIGT AND MENGEOFFEN IS NULL
FOR UPDATE WITH LOCK
Why uses the TIBCTable such long and incorrect code for locking? VGNR and POS are set at the TIBCTable KeyFields property.

Thanks in advance!

ViktorV
Devart Team
Posts: 2445
Joined: Wed 30 Jul 2014 07:16

Re: Edited detail record can't be posted

Post by ViktorV » Mon 14 Jan 2019 13:42

As we mentioned earlier, this behavior is related to the LockMode property. When setting the DMLRefresh property is set to True, we can get values generated on the server side in your example (e.g. the MENGEOFFEN computed field). When the DMLRefresh property is disabled and when properties LockMode <> lmNone, IBDAC does not receive values of the MENGEOFFEN and generates an automatic query with the MENGEOFFEN IS NULL condition. If the SQLLock, SQLInsert, SQLUpdate, SQLDelete, SQLRefresh properties are not empty, for example, generated in design-time or set manually, IBDAC will use SQL query specified in this property. If the property is set to an empty string, IBDAC will automatically generate the necessary SQL queries when executing the corresponding operations. At that, it equally applies to both, TIBCTable and TIBCQuery. You can make sure of it by clearing all queries generated in SQL design time in your example. SQL queries generated in design time and run time may differ.
To resolve the issue, you can either set the DMLRefresh to True or assign a required query to the TIBCTable.SQLLock.Text property. For example:

Code: Select all

TabVorgangPos.SQLLock.Text := 'SELECT NULL FROM VORGANGPOS WHERE VGNR = :Old_VGNR AND POS = :Old_POS '+
'FOR UPDATE WITH LOCK';

TheFastCoder
Posts: 5
Joined: Mon 18 Jun 2018 06:40

Re: Edited detail record can't be posted

Post by TheFastCoder » Mon 14 Jan 2019 14:18

Thank you! Now I understand it. Very good support! Keep it up that way.

ViktorV
Devart Team
Posts: 2445
Joined: Wed 30 Jul 2014 07:16

Re: Edited detail record can't be posted

Post by ViktorV » Mon 14 Jan 2019 14:33

It is good to see that the problem has been solved.
Feel free to contact us if you have any further questions about our products.

Post Reply