Cached Updates, ApplyUpdates inserts twice!

[bmarple]

I have a situation where I have master and detail tables, using TOracleDataset with CachedUpdates True. I insert a record into the master, post, then Session.ApplyUpdates([master], False). I add some detail records, post, then Session.ApplyUpdates([detail], False). I then make some more changes to the master record, post, and finally go to commit: session.ApplyUpdates([master, detail], True). I do not have Master set in the detail dataset - I apply the keys manually in OnNewRecord.

I get an Oracle Key Violation on the master table when I apply the second update. Why? Looking at Oracle Monitor I see that DOA tries to insert the same master record twice; once with each ApplyUpdates.

I am using Delphi XE and DOA 4.1.

 

[bmarple]

My understanding, correct me if I'm wrong, is that with CachedUpdates on changes to TOracleDataset values are stored in memory and can be accessed through field variables such as od_MyDataSetMYFIELD.AsInteger. However, a query cannot be run to get the values entered in that dataset until they have been passed onto Oracle by ApplyUpdates (no commit). At this point, TOracleDataset flushes local Cache. At this point I can query from Oracle the data that came from the dataset.

All this appears to be true. BUT, TOracleDataset still has the original query state in Cache despite having had the cache flushed and the sql applied to Oracle by ApplyUpdates([x],False). Why would a subsequent application of ApplyUpdates([x], True) write the same query as the previous one that was flushed?

In the case of an Insert query, why would, after flushing cache and sending the statement to Oracle, uncommitted, be replicated again when updates have been made that need to be passed on to Oracle after the insert statement has already been passed?

I'm confused.

 

[Marco Kalter]

When using CachedUpdates, each dataset has a local change log where all updates are cached. When calling ApplyUpdates, these local changes are applied to the database. When calling CommitUpdates, the transaction is committed, and the local change logs of the datasets are cleared. If you only call Commit, then the local change logs remain and subsequent calls to ApplyUpdates will apply the same changes again, which will lead to errors.

 

[bmarple]

Ok. Let me see if I understand.

When using CachedUpdates, each dataset has a local change log where all updates are cached. Yup.

When calling ApplyUpdates, these local changes are applied to the database. Yup.

When calling CommitUpdates, the transaction is committed, and the local change logs of the datasets are cleared. Yup.

If you only call Commit, then the local change logs remain and subsequent calls to ApplyUpdates will apply the same changes again, which will lead to errors. Yup. But that's not what I'm doing.

In pseudo code this is what I do and what I expect is happening.

. Insert new master record with keys. --In local cache.
. Enter master record data. --In local cache.
. ApplyUpdates([master], False) --Write to Oracle, empty cache.
. Insert detail record with keys. --In local cache.
. Enter detail record data. --In local cache.
. ApplyUpdates([detail], False) -Write to Oracle, empty cache.
. Make more changes to master record. --In local cache.
. ApplyUpdates([master, detail], True)

This is where I get fuzzy. I think that the last apply updates writes out only the changes since the last apply updates for the dataset and commits the transaction. If this is not the case and the cache is written out in its entirety then of course I'll have problems. (All evidence points to this being the case)

What I want to know is, if the above is true, how do I create, edit and post cached data to Oracle in a master detail arrangement such that I can roll back the whole transaction if desired? And that will allow me to run queries against inserted but uncommitted data within the session?

 

[Marco Kalter]

You should only call ApplyUpdates once for a transaction. After that you can call CommitUpdates, CancelUpdates, or Rollback for transaction management.

 

[bmarple]

So, I cannot apply the updates to Oracle then query them back in different form and add more data all in the same transaction? Bummer.

Is there no way I can flush the cache after a ApplyUpdates([x], False)?

 

[bmarple]

I found my solution!

If, after I perform session.ApplyUpdates([master,detail], False) I then perform master.CommitUpdates and detail.CommitUpdates the cached changes for the two datasets are flushed, the changes are now in Oracle's transaction cache, I can query the results and I can then make for changes and start the process all over again without error. At the end I can then Session.Commit or Session.Rollback.

The CommitUpdates method of the TOracleDataset appears to flush the cache and refresh the dataset as necessary. Perfect!

Why isn't TOracleDataset.CommitUpdates in the documentation?