Icon View Incident Report

Serious Serious
Reported By: Geroglidis Giannis
Reported On: 3/26/2013
For: Version 2.12 Build 2
# 3822 Opening/Closing Tables and Adding/Dropping FK Constraints Can Cause Error #401 and #402

We are in version 205 build 7 and we have not update since then because we are facing some problems with the later versions. The problem has to do with elevate errors #401 and #402:

Error #401 (Tables Master0 and Master2 are inaccesible)

Error #402 (Some tables have been closed during Create Constraint)

Database:

SCRIPT
BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE "MasterTable0"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1),
"CODE" VARCHAR(25) COLLATE "ANSI",
"DESCRIPTION" VARCHAR(50) COLLATE "ANSI",
CONSTRAINT "MasterTable0_PK" PRIMARY KEY ("ID"),
CONSTRAINT "MasterTable0_Code" UNIQUE ("CODE")
)';

EXECUTE IMMEDIATE 'CREATE TABLE "MasterTable"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1),
"CODE" VARCHAR(25) COLLATE "ANSI",
"DESCRIPTION" VARCHAR(50) COLLATE "ANSI",
"ID0" INTEGER,
CONSTRAINT "MasterTable_PK" PRIMARY KEY ("ID"),
CONSTRAINT "MasterTable_Code" UNIQUE ("CODE"),
CONSTRAINT "MasterTableToMasterTable0" FOREIGN KEY ("ID0") REFERENCES "MasterTable0" ("ID")
   ON UPDATE NO ACTION ON DELETE NO ACTION
)';

EXECUTE IMMEDIATE 'CREATE TABLE "MasterTable2"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1),
"CODE" VARCHAR(25) COLLATE "ANSI",
"DESCRIPTION" VARCHAR(50) COLLATE "ANSI",
"ID0" INTEGER,
CONSTRAINT "MasterTable2_PK" PRIMARY KEY ("ID"),
CONSTRAINT "MasterTable2_Code" UNIQUE ("CODE"),
CONSTRAINT "MasterTable2ToMasterTable0" FOREIGN KEY ("ID0") REFERENCES "MasterTable0" ("ID")
   ON UPDATE NO ACTION ON DELETE NO ACTION
)';

EXECUTE IMMEDIATE 'CREATE TABLE "DetailTable"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1),
"MASTERID" INTEGER,
"MASTERID2" INTEGER,
"DESCRIPTION" VARCHAR(50) COLLATE "ANSI",
CONSTRAINT "DetailTable_PK" PRIMARY KEY ("ID"),
CONSTRAINT "DetailTableToMaster" FOREIGN KEY ("MASTERID") REFERENCES "MasterTable" ("ID")
   ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "DetailTableToMaster2" FOREIGN KEY ("MASTERID2") REFERENCES "MasterTable2" ("ID")
   ON UPDATE NO ACTION ON DELETE NO ACTION
)';

END

Code:

procedure TdtmEDB.Error401;
begin
  tblMaster0.Active := True;
  DropConstraint;
  DropConstraint2;
  tblMaster0.Active := False;
  //
  tblMaster0.Active := True;
  tblMaster0.Active := False;
  AddConstraint;
  AddConstraint2;
  //
end;

procedure TdtmEDB.DelInsM0R1;
begin
  if tblMaster0.Locate('ID', 1, []) then
  begin
    tblMaster0.Delete;
  end;
  tblMaster0.Append;
  tblMaster0.FieldByName('ID').AsInteger := 1;
  tblMaster0.FieldByName('CODE').AsString := '001';
  tblMaster0.FieldByName('DESCRIPTION').AsString := '001';
  tblMaster0.Post;
end;

procedure TdtmEDB.Error402;
begin
  tblMaster0.Active := True;
  try
    DropConstraint;
    AddConstraint;
  finally
    DelInsM0R1;
    tblMaster0.Active := False;
  end;
end;



Resolution Resolution
Fixed Problem on 6/10/2013 in version 2.13 build 1


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB PHP Standard
ElevateDB PHP Standard with Source
ElevateDB PHP Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image