Unintended update on refresh

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Valgardur
Posts: 165
Joined: Tue 24 Nov 2009 19:33

Unintended update on refresh

Post by Valgardur » Tue 11 Jul 2017 11:05

A strange situation, unable to reproduce though..

The same user running two Windows applications, one monitoring incoming files, the other display relevant records.

The monitoring updates a record, the user clicks "refresh" (which calls refresh on the dataset), and the old value is overwritten. No other fields are updated and the user has not updated the field in question.

To clariy..

The order application has live orders visible in a form one with quantity of 25, the monitoring application receives a file with an update to quantity of 40 and updates the order. The user refreshes the form in the order application, still showing quantity of 25.

We have a trigger logging details of changes and the monitor application clearly updates the quantity to 40, ten seconds later the order application (on user refresh) updates the order and sets quantity back to 25.

So.. a few questions.

1. Does refresh do anything but read data? The only property not set to default is that fetch all records is set to true.

2. Even if update was called and the record has already been modified by another application, are the new values overwritten without a warning?

3. If so, can I force a check for modifications?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Unintended update on refresh

Post by MaximG » Fri 14 Jul 2017 08:59

Thank you for the information.
1. You are absolutely right. The Refresh method execution will affect only data reading
2. The issue of updating data by several applications is related to the work of Oracle Database that you are using.
3. ODAC provides a possibility of obtaining the notifications you are interested in : https://www.devart.com/odac/docs/?devar ... lerter.htm The sample of using this component is available together with the distributed in ODAC demo : [ODAC install folder]\Demos\OdacDemo\Alerter\

Valgardur
Posts: 165
Joined: Tue 24 Nov 2009 19:33

Re: Unintended update on refresh

Post by Valgardur » Fri 14 Jul 2017 13:00

Thank you for the reply

I am not sure I understand item 2..

The Alerter is not exactly what I had in mind, although it might be an alternative way, and perhaps provide a safer working environment.

But as for my example..

if Application A, and Application B both read the same record... Application A posts an update.. and then 30 seconds later Application B posts an update, will Application B overwrite the updates from Application A without a warning? In my case Application A and Application B are updating different fields.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Unintended update on refresh

Post by MaximG » Mon 17 Jul 2017 10:11

It is possible to implement the described behavior with the following ODAC option: https://www.devart.com/odac/docs/?devar ... fields.htm
In addition, you can use the CheckMode property: https://www.devart.com/odac/docs/?devar ... fields.htm An example of its usage is shown in ODACDemo

Valgardur
Posts: 165
Joined: Tue 24 Nov 2009 19:33

Re: Unintended update on refresh

Post by Valgardur » Mon 17 Jul 2017 10:26

Both links refer to the same item.

I have UpdateAllFields set to the default value.

I have CheckMode to default value as well, and according to the help (which could be more detailed), "TOraDataSet first refetches record values and compares them with those of a client." which is exactly what I want.

So as I have set both properties as I am expecting, I am not understanding how the record got overwritten.. I have a trigger on the table and the record definately is updated by the second application..

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Unintended update on refresh

Post by MaximG » Tue 18 Jul 2017 08:32

Thank you for your comment. Actually, the information about using the CheckMode property is available at: https://www.devart.com/odac/docs/?devar ... ckmode.htm. Try using ODACDemo to test its compatibility with this property according to the described behavior. Using the ODAC Demo \ Working with components \ Smart example, set the cmException value to the CheckMode property, and the lmLockImmediate value to the LockMode property. In this case, when trying to edit the record of the table you are interested in, you will receive the message "Record was changed by another user" in the case if such a record has been updated by another application recently. Do we understand you correctly that you need to implement this behavior?

Valgardur
Posts: 165
Joined: Tue 24 Nov 2009 19:33

Re: Unintended update on refresh

Post by Valgardur » Tue 18 Jul 2017 10:20

Thank you for the reply, from the documentation I understood this to be different.
TOraDataSet first refetches record values and compares them with those of a client.
I understood this to mean the default behaviour.

I will try changing this and contact again if still unsolved.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Unintended update on refresh

Post by MaximG » Tue 18 Jul 2017 12:06

We are sure you will find the right solution. Should you have any questions about our products, feel free to contact us back

Valgardur
Posts: 165
Joined: Tue 24 Nov 2009 19:33

Re: Unintended update on refresh

Post by Valgardur » Tue 18 Jul 2017 12:24

Actually, setting CheckMode to cmException makes no difference.. are there any other properties I need to set? Or should be careful not to set?

I set CheckMode to cmException, opened the dataset in application A, modified Field1 in application B, then modified Field2 in application A.. and field 1 gets updated to the former value, without notification and even though I did not make any changes to Field1 in application A.. even without cmException I would expect Field1 to be unchanged, as I have UpdateAllFields set to False.

One thing I did notice.. if I start editing in application A, update in application B, then application A overwrites.. but if I don't start the editing in application A, the record is correctly refreshed before editing.

But this is very serious for my client, as this did almost cost wrong production information.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Unintended update on refresh

Post by MaximG » Wed 19 Jul 2017 10:22

In our tests we used the following scenario. The data was modified in the first row of the DEPT table (DeptNo=10), which is included into standard examples when working with OracleDB
a) we turned to the DEPT table as follows:

Code: Select all

…
  OraQuery.CheckMode := cmException;
  OraQuery.LockMode := lmLockImmediate;
  OraQuery.SQL.Text := 'Select DeptNo, Loc From Dept Where DeptNo=10';
  OraQuery.Open;
… 
b) using the third-party application (for example, SQL*Plus), we modified the value of the Loc field:

Code: Select all

Update DEPT Set Loc=’New Value’
Where DeptNo=10 and confirmed our actions using the

Code: Select all

Commit
operator

c) now, when executing the following code snippet :

Code: Select all

  OraQuery.Edit;
  OraQuery.FieldByName('Loc').AsString := 'NY';
  OraQuery.Post;
we receive the required message: "Record was changed by another user"

Valgardur
Posts: 165
Joined: Tue 24 Nov 2009 19:33

Re: Unintended update on refresh

Post by Valgardur » Wed 19 Jul 2017 10:53

Thank your for the reply.

Is the cmLockImmediate necessary? If so, it would be helpful to have that in the documentation discussing cmException.

But there is a problem with locking the record, I don't want it locked, and it actually it will be a problem in other situations if it gets locked.. I need both application to be able to update, but get a warning in case there is a conflict..

Is this not possible?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Unintended update on refresh

Post by MaximG » Wed 04 Oct 2017 14:21

In order to get the actual value of the field in the database table before changing its value, you can use the RefreshOptions property: https://www.devart.com/odac/docs/?devar ... ptions.htm
To receive notifications that the record you are interested in has been changed by another application, we can offer you the functionality of TOraAlerter: https://www.devart.com/odac/docs/?devar ... lerter.htm
In both cases, there is no need to lock records.

Post Reply