Workspace Manager

[Dave Reid]

Hi there,
After a table has been version-enabled, I've found that a user cannot edit a record more than once before they post the change and refresh the dataset.

i.e. I version enabled a table called AC_CLAIM. After I do this, Oracle renames the table to AC_CLAIM_LT and creates a view called AC_CLAIM, which I use whenever I want to edit claim information. If a user makes a change to a record in AC_CLAIM that record will be removed from the view and will be replaced by the most recent record in the AC_CLAIM table. I understand that this is why a user cannot change a record more than once in a version-enabled table, but is there a way to replace the old record in my dataset with the most recent record (after it's been changed of course) without having to refresh the entire dataset?

I tried calling RefreshRecord in the AfterPost event, but that didn't work, probably because RefreshRecord is using the RowID to perform the refresh. Essentially I'd be able to do it if I could change the RowID of a record in the AfterPost event. But I don't think that's possible.

To circumvent this problem I'm using cached updates because I can control when the change is posted to the database. I can also force the dataset to perform a full refresh whenever changes are posted. However, I'd rather not do this because Oracle transactions are much nicer to use.

Thanks a lot,
Dave Reid

 

[Marco Kalter]

Perhaps this can be fixed by setting the UniqueFields property of the dataset?

 

[Dave Reid]

Hi Marco,
I've tried setting the UniqueFields property to the primay key of the table (i.e. RecordID) but that didn't work. Do you have any more ideas?

FYI - I'm using DOA version 4.0.6.2 and Oracle version 10.1.0.2.0.

Thanks again,
Dave Reid

 

[Marco Kalter]

Can you let me know what exactly goes wrong? I'm not sure I completely understand.

 

[Dave Reid]

Hi Marco,
I'll outline the steps that I've taken in as much detail as I can.
1) I version enabled the AC_CLAIM table. At this point Oracle renamed the table to AC_CLAIM_LT and created the view AC_CLAIM. The
AC_CLAIM_LT table will store the version data and the version history, whereas the AC_CLAIM view will just show the most recent version of the data.
2) I created a test program that uses a grid to display the contents of AC_CLAIM. The dataset does not cache updates and immediately posts data changes.
3) I run the test program
4) I edit a claim record (e.g. change a claim value from $450.00 to $1,000.00)
5) I press Enter and go off the record. The record is then posted to the database. At this point Oracle does the following via a trigger:
(a) Closes the current $450 claim record in the AC_CLAIM_LT table by setting the retire time
(b) Adds a record to the AC_CLAIM_LT record for the $1,000 claim and timestamps the change.
(c) Refreshes the view, which causes the $1,000 claim record to replace the $450 claim record. i.e. the old record no longer exists in the AC_CLAIM view.
6) Now I go back to the record I just changed and try to update the value again. At this point I get the error "record has been changed by another user".

I get this error because the data is stale. If I was to refresh the data after each post, I wouldn't get this error, but that could potentially really impact performance. What I'd like to do is replace the record that I've just changed with the newest record, but I don't know if that's possible.

I hope that helps clarify things.

Thanks again,
Dave Reid