Problems with TOraTable and CachedUpdates

[devmann]

Hi all,

In a form there are TOraTable objects in master-detail relationship, one master, 10 detail tables, linked with the primary key on the master table. There are CRDBGrids objects / DBGrids objects in the form for monitoring the data. All TOraTables share one TOraSession. AutoCommit are all False. CachedUpdates are True. With these settings, I found that data cannot be committed to database properly.

Scenario 1

*POSTED* data on detail tables all disapper after record focus changed on the master table.

Scenario 2

Only data on the master table can be successfully committed to database. All posted newly appended data in the detail datasets cannot be commited. Worse, they disappear after a session commit.

Here is how this happends:

Before commiting I update data on tables, master first, then details.

Master

Code: Select all

oratblMaster.FieldByName('blah').AsString := 'blah';
oratblMaster.Post;

Then Details, for each tables I do the following things:

Code: Select all

oratblDetail1.First;
while not oratblDetail1.eof do
begin
  if not (oratblDetail1.State in [dsInsert, dsEdit]) then oratblDetail.Edit;
  oratblDetail1.FieldByName('fieldx').AsInteger := 999;
  oratblDetail1.Post;
  oratblDetail1.Next;
end;

etc etc, do the same things till the last table, say, oratblDetail10.
Since calling session.ApplyUpdates is a preferred method. Then I issue a session ApplyUpdates

Code: Select all

OraFormSession.ApplyUpdates
// This is not a oratable.session.ApplyUpdates.  I call it on a session level.

With this method, only the master table data can be committed.

Scenario 3

Since a session level applyupdates is not sucessful. I use individual table update. Appearantly it succeeds, newly appended data can be saved to database. But existing data deleted by a TOraTable.Delete command is still exists on table after committing data.

Here is how:

Suppose the data is already in the table. I append new records to the table with this command.

Code: Select all

oratblDetail2.Append;

Then I write data to the table, and Post.
And I delete existing record by this command.

Code: Select all

oratblDetail2.Delete;

And then Post.

Here is how I commit data to the database.

Code: Select all

// apply update
oratblDetail1.ApplyUpdates;
blah blah blah
oratblDetail10.ApplyUpdates;
oratblMaster.ApplyUpdates
// commit data
oratblDetail1.Session.Commit;
blah blah blah
oratblDetail10.Session.Commit;
oratblMaster.Session.Commit;
// clear the cached memory
oratblDetail1.CommitUpdates;
blah blah blah
oratblDetail10.CommitUpdates;
oratblMaster.CommitUpdates;

Then, the newly appended records are committed to the database sucessfully. But the deleted records still exist. They're there as if they've not been deleted before.

These is the software config of my environment.

ODAC version: 4.50.2.1 for Delphi 6
Delphi version: 6 Enterprise build 6.240
Oracle 7.3.3

Can anyone give me some solutions to these scenarios?

Thanks very much.

dm

[Challenger]

The Master/Detail relationship is not compatible with the CachedUpdates mode. When you change position in the master dataset all detail datasets are reopened. So all updates that you've made in detail datasets in thу CachedUpdates mode will be lost. That's why you should call the ApplyUpdates method of TOraSession before scrolling in the master dataset.