Board index » delphi » Problem IBDataSet Cachedupdates deleting Master/detail records

Problem IBDataSet Cachedupdates deleting Master/detail records

Trouble with delete master record, and detail records with
IBDataSet -Cachedupdates.
When I delete detail record, every thing was fine, but when deleting master
record, detail records are not deleted. With debuger I so that detail record
are proceed with deleting, but on SQL monitor I do not se any SQL with
delete statement, only master.
Other question is : How to change key in deteil dataset ,becouse other user
add new record with the same key. Befor I change master key I iterate thrue
detail DataSet and Edit key, and then edit (change key) in master data set.
This is only in insert new master, and detail records. Master record key are
changed, but detail key are in orginal insert key (on new record event).

Please Help.
TIA
Miljenko

My Environment:
Delphi 5 with SP2, IBX 5.03, Interbase 6.01, Windows 98
Table definition
CREATE TABLE "ORD_MST"
(
  "GRUPA" CHAR(2) CHARACTER SET WIN1250 NOT NULL,
  "ORDNO" NUMERIC(11, 0) NOT NULL,
  "CUSTNO" CHAR(5) CHARACTER SET WIN1250 NOT NULL,
  "CUSTDS" BLOB SUB_TYPE TEXT SEGMENT SIZE 100 CHARACTER SET WIN1250,
  "DTCON" TIMESTAMP,
  "REFNO" VARCHAR(20) CHARACTER SET WIN1250,
  "DTREF" TIMESTAMP,
  "DELDI" VARCHAR(50) CHARACTER SET WIN1250,
  "CNTRY" CHAR(3) CHARACTER SET WIN1250,
  "SHMD" CHAR(3) CHARACTER SET WIN1250,
  "SHMDD" VARCHAR(15) CHARACTER SET WIN1250,
  "PACK" CHAR(1) CHARACTER SET WIN1250,
  "PACKD" VARCHAR(15) CHARACTER SET WIN1250,
  "GARPE" VARCHAR(25) CHARACTER SET WIN1250,
  "CURR" CHAR(3) CHARACTER SET WIN1250,
  "PARTY" VARCHAR(15) CHARACTER SET WIN1250,
  "TERPY" VARCHAR(25) CHARACTER SET WIN1250,
  "OROPN" VARCHAR(15) CHARACTER SET WIN1250,
  "DT_OP" TIMESTAMP,
  "ORAPR" VARCHAR(15) CHARACTER SET WIN1250,
  "DT_AP" TIMESTAMP,
  "DTPRT" TIMESTAMP,
  "DTOTV" TIMESTAMP DEFAULT 'now',
  "USR_OTV" "USERNAME",
  "DTZAPR" TIMESTAMP,
  "USR_ZAPR" VARCHAR(20) CHARACTER SET WIN1250,
CONSTRAINT "ORD_MSTPRI" PRIMARY KEY ("GRUPA", "ORDNO")
);

CREATE TABLE "ORD_DET"
(
  "GRUPA" CHAR(2) CHARACTER SET WIN1250 NOT NULL,
  "ORDNO" NUMERIC(11, 0) NOT NULL,
  "REDBR" INTEGER DEFAULT 0 NOT NULL,
  "ITDSC" BLOB SUB_TYPE TEXT SEGMENT SIZE 400 CHARACTER SET WIN1250,
  "QTY" NUMERIC(12, 2),
  "DTREQ" TIMESTAMP,
  "ITPRI" NUMERIC(15, 2),
  "DTOTV" TIMESTAMP DEFAULT 'now',
  "USR_OTV" "USERNAME",
  "DTZAPR" TIMESTAMP,
  "USR_ZAPR" VARCHAR(20) CHARACTER SET WIN1250,
CONSTRAINT "ORD_DETPRI" PRIMARY KEY ("GRUPA", "ORDNO", "REDBR")
);

SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE "LAST_ORDDET_RBR"
(
  "GRUPA" CHAR(2) CHARACTER SET WIN1250,
  "ORDNO" NUMERIC(11, 0)
)
RETURNS
(
  "RBR" INTEGER
)
AS
BEGIN EXIT; END ^

ALTER PROCEDURE "LAST_ORDDET_RBR"
(
  "GRUPA" CHAR(2) CHARACTER SET WIN1250,
  "ORDNO" NUMERIC(11, 0)
)
RETURNS
(
  "RBR" INTEGER
)
AS
BEGIN
  SELECT MAX(REDBR)
  FROM ORD_DET
  WHERE GRUPA = :GRUPA AND ORDNO = :ORDNO
  INTO :RBR;

  IF (RBR IS NULL) THEN
  BEGIN
    RBR = 0;
  END
END
 ^

COMMIT WORK ^
SET TERM ; ^

SET TERM ^ ;

/* Triggers only will work for SQL triggers */

CREATE TRIGGER "SET_ORDDET_RBR" FOR "ORD_DET"
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE MAXRBR INTEGER;
BEGIN
  EXECUTE PROCEDURE LAST_ORDDET_RBR( NEW.GRUPA, NEW.ORDNO)
  RETURNING_VALUES :MAXRBR;
  NEW.REDBR = MAXRBR + 1;
END
 ^

COMMIT WORK ^
SET TERM ;^

Definition in DataModule
Database
 SQLDialect=3
 DafaultTransaction = Transaction

Transaction
 AutoStopAction= saNone
 DefaultAction = TACommit
 Params = read_committed
  rec_version
  nowait

Definition TIBDataSet:
Master
 Cachedupdates = True
 IBOrders = IBOrders
 SelectSql =select * from ORD_MST WHERE grupa = :GRP Order by grupa, ordno
desc
Events:
Beforedelete
   DeleteItems;
AfterDelete
   IBDatabase.ApplyUpdates([IBItems, IBOrders]);
   Transaction.CommitRetaining;

procedure TDtaMod.DeleteItems;
begin
  DeletingItems := True;
  IBItems.DisableControls;
  try
    IBItems.First;
    while not IBItems.EOF do IBItems.Delete;
  finally
    DeletingItems := False;
    IBItems.EnableControls;
  end;
end;

Detail
 Cachedupdates = True
 IBOrders = IBItems
 DataSource = IBOrdersSrc
 SelectSql =select * from ORD_DET where grupa = :grupa and ordno = :ordno
Order by grupa, ordno, redbr
Events:
Beforedelete
   IBOrders.Edit;

AfterDelete
   if not DeletingItems then UpdateTotals;

On Edit Form Event
FormCloseQuery
 begin
   CanClose := DtaMod.DataSetApplyUpdates(DtaMod.IBOrders, ModalResult =
mrOK);
   if CanClose then
   begin
     DtaMod.IBOrders.Transaction.CommitRetaining;
     DtaMod.IBOrders.Close;
     DtaMod.IBItems.Close;
     DtaMod.QCur.Close;
     DtaMod.QCountry.Close;
   end;
and function DataSetApplyUpdates in DataModule

function TDtaMod.DataSetApplyUpdates(DataSet: TDataSet; Apply: Boolean):
Boolean;
begin
  Result := True;
  with TIBCustomDataSet(DataSet) do
  begin
    if (State in dsEditModes) or UpdatesPending then
    begin
      if Apply then
      begin
        Database.ApplyUpdates([DataSet as TIBCustomDataSet]);
        Transaction.CommitRetaining;
        CancelUpdates;
      end
      else
      begin
        if (MessageDlg('Exit without savings ?', mtConfirmation,
          [mbYes, mbNo], 0) = mrYes) then
          CancelUpdates
        else
          Result := False;
      end;
    end;
  end;

 

Re:Problem IBDataSet Cachedupdates deleting Master/detail records


Your problem is

   IBDatabase.ApplyUpdates([IBItems, IBOrders]);

On deletes you must apply the detail first and then the master.  This is one of
the reasons I recommend against CachedUpdates (TQuery has the same problem).
When you have that ordering the Master delete is applied first.  The record is
removed from the master.  This cause a new master which causes the detail to
scroll to the new master and lose its changes then finally the detail is applied
but there are no changes left.  You have to reverse the order (which also won't
work if you have a mixture of inserts/edits and Deletes).

I recommend just using transaction control.

Quote
Miljenko Rajkovic wrote:

> Trouble with delete master record, and detail records with
> IBDataSet -Cachedupdates.
> When I delete detail record, every thing was fine, but when deleting master
> record, detail records are not deleted. With debuger I so that detail record
> are proceed with deleting, but on SQL monitor I do not se any SQL with
> delete statement, only master.
> Other question is : How to change key in deteil dataset ,becouse other user
> add new record with the same key. Befor I change master key I iterate thrue
> detail DataSet and Edit key, and then edit (change key) in master data set.
> This is only in insert new master, and detail records. Master record key are
> changed, but detail key are in orginal insert key (on new record event).

> Please Help.
> TIA
> Miljenko

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
This sad little lizard told me that he was a brontosaurus on his mother's
side.  I did not laugh; people who boast of ancestry often have little else
to sustain them.  Humoring them costs nothing and adds to happiness in
a world in which happiness is in short supply.   (RAH)

Other Threads