TOracleDataset.RefreshOptions.roAllFields implications for RefreshRecord.

[Arvid]

We have an issue with "RefreshRecord".

What happens is that:
1. We fetch data from the database in a TOracleDataset (ODS1)
Sql something like SELECT * FROM CUSTOMERS WHERE COMPANY = 'UNKOWN';
2. This dataset is connected to a TDBGrid where we can choose a customer.
3. We can then execute a TOracleScript where COMPANY is changed to a different value than 'UNKOWN' and then a "RefreshRecord" is done.

If RefreshOption.roAllFields are set to true this DOES NOT WORK. Why?

If we change other fields than the field that limits the search the
refresh is done. And if RefreshOption.roAllFields = false it works.

The issue only occure when data is so that the row is not included if the dataset is refreshed and RefreshOptions.roAllFields are set to true.

Is this intended or a bug?

 

[Marco Kalter]

I think the /* END_REFRESH */ option provides the answer:

Refreshing derived fields
When a record is refreshed, by default only the fields belonging to the updating table will be fetched from the database. Derived fields that result from joins, function calls, calculations, and so on will therefore not be refreshed. If you want to refresh derived fields as well, enable the roAllFields option.

This is accomplished by re-executing the SQL statement for just the current record. This requires that the where clause is extended with the rowid of the current record. Let's assume the following SQL statement:

select emp.*, dept.dname, dept.loc
    from emp, dept
   where dept.deptno = emp.deptno

Before editing or after inserting or updating a record, the following statement will be executed to refresh all fields:

select emp.*, dept.dname, dept.loc
    from emp, dept
   where dept.deptno = emp.deptno
     and emp.rowid = :doa__rowid

If however the statement is a little bit more complicated, problems can occur:

select emp.*, dept.dname, dept.loc
    from emp, dept
   where dept.deptno = emp.deptno
     and emp.sal < 5000

If a record is inserted or updated that does not meet the criteria, it would not be fetched anymore. Let's assume you update a record and set the 'sal' field to 6000. The following statement would no longer retrieve a record because emp.sal < 5000 is no longer true:

select emp.*, dept.dname, dept.loc
    from emp, dept
   where dept.deptno = emp.deptno
     and emp.sal < 5000
     and emp.rowid = :doa__rowid

To prevent this problem, you can add an /* END_REFRESH */ hint in the SQL statement that tells the dataset which part of the where clause to exclude from the refresh statement:

select emp.*, dept.dname, dept.loc
    from emp, dept
   where dept.deptno = emp.deptno
     /* END_REFRESH */
     and emp.sal < 5000

Note that the hint must be literally /* END_REFRESH */, and may not differ in any way.