tunct
asked on
referential integrity problem using DbiDoRestructure
hi,
i'm using DbiDoRestructure to add referential integrity to paradox
tables. there's no problem if some RI has previously been defined (say,
using Database Desktop), or more specifically, if the *.X02, *.X03 etc.
files already exist .
but if it's the first time i try to create a RI, nothing happens (no error, no
RI created, nothing). can anybody guess what's going on?
thanks in advance,
T.
ps: configuration: BCB3 + BDE 5.00
i'm using DbiDoRestructure to add referential integrity to paradox
tables. there's no problem if some RI has previously been defined (say,
using Database Desktop), or more specifically, if the *.X02, *.X03 etc.
files already exist .
but if it's the first time i try to create a RI, nothing happens (no error, no
RI created, nothing). can anybody guess what's going on?
thanks in advance,
T.
ps: configuration: BCB3 + BDE 5.00
ASKER
Adjusted points to 200
ASKER
nope, i also tried doing it in the directory where the exe is located, it's still the same.
hmm, maybe the problem is more difficult than i thought... i'm increasing the score to 200
btw: os = NT4.0
T.
hmm, maybe the problem is more difficult than i thought... i'm increasing the score to 200
btw: os = NT4.0
T.
hi tunctm
from my paq,
procedure AddRI(Master, Detail: TTable; MFields, DFields : Array of word ;RIName: string; ModOp, DelOp: RINTQual; RINTNum : Word);
var
MasterProps, DetailProps: CURProps;
hDb: hDBIDb;
TableDesc: CRTblDesc;
Op: CROpType;
RInt: RINTDesc;
MIndex, DIndex: IDXDesc;
MNo, DNo: Word;
I : Integer;
begin
if Master.Active = False then
raise EDatabaseError.Create('Mas ter table: ' + Master.TableName +
' is not opened');
if Detail.Active = False then
raise EDatabaseError.Create('Det ail table: ' + Detail.TableName +
' is not opened');
if Master.Exclusive = False then
raise EDatabaseError.Create('Tab le: ' + Master.TableName +
' must be opened exclusively');
if Detail.Exclusive = False then
raise EDatabaseError.Create('Tab le: ' + Detail.TableName +
' must be opened exclusively');
// Make sure the tables are opened with an index and get their descriptors...
FillChar(DIndex, sizeof(DIndex), 0);
FillChar(MIndex, sizeof(MIndex), 0);
Check(DbiGetIndexDesc(Deta il.Handle, 0, DIndex));
Check(DbiGetIndexDesc(Mast er.Handle, 0, MIndex));
// Get the table properties to determine table type...
Check(DbiGetCursorProps(Ma ster.Handl e, MasterProps));
Check(DbiGetCursorProps(De tail.Handl e, DetailProps));
// If the table is not a Paradox table, raise an error...
if (MasterProps.szTableType <> szPARADOX) and
(MasterProps.szTableType <> szDBASE) then
raise EDatabaseError.Create('Mas ter table: ' + Master.TableName +
' must be a Paradox or dBASE table type');
if (DetailProps.szTableType <> szPARADOX) and
(DetailProps.szTableType <> szDBASE) then
raise EDatabaseError.Create('Det ail table: ' + Detail.TableName +
' must be a Paradox or dBASE table type');
if MasterProps.szTableType <> DetailProps.szTableType then
raise EDatabaseError.Create('Mas ter and Detail tables must be of same type');
// Blank out the structures...
FillChar(TableDesc, sizeof(TableDesc), 0);
FillChar(RInt, sizeof(RInt), 0);
// Get the database handle from the table's cursor handle...
Check(DbiGetObjFromObj(hDB IObj(Maste r.Handle), objDATABASE, hDBIObj(hDb)));
// Put the table name in the table descriptor...
StrPCopy(TableDesc.szTblNa me, Detail.TableName);
// Put the table type in the table descriptor...
TableDesc.szTblType := MasterProps.szTableType;
// Set the operation type...
Op := crADD;
TableDesc.pecrRintOp := @Op;
// Set the amount of new RI descriptors...
TableDesc.iRintCount := 1;
// Connect the table descriptor to the RI descriptor...
TableDesc.printDesc := @RInt;
// Setup the RI descriptor...
// Put in the name of the RI...
StrPCopy(RInt.szRintName, RIName);
// Do the restructure on the dependent (detail) table...
RInt.eType := rintDEPENDENT;
// Add the master table name...
StrPCopy(RInt.szTblName, Master.TableName);
// Modify operations will be restricted (this can be changed to rintCASCADE)...
RInt.eModOp := ModOp;
// Delete operations will be restricted (NOTE: rintCASCADE will not work)...
RInt.eDelOp := DelOp;
// Only one field in link...
RInt.iFldCount := high(MFields) + 1;
// If the tables are Paradox, then put the associated field numbers in the descriptor...
if (MasterProps.szTableType = szPARADOX) then
begin
//
if RInt.eDelOp = rintCASCADE then
raise EDatabaseError.Create('Can not use cascading delete RI with Paradox tables');
// Put the detail field index in the array...
// RInt.aiThisTabFld := DIndex.aiKeyFld;
for i := low(DFields) to High(DFields) do
RInt.aiThisTabFld[I] := DFields[I];
// Put the master field index in the array...
// RInt.aiOthTabFld := MIndex.aiKeyFld;
for i := low(MFields) to High(MFields) do
RInt.aiOthTabFld[I] := MFields[I];
RInt.iRintNum := RINTNum;
end;
// If the tables are dBASE, then put the sequence number in the descriptor...
if MasterProps.szTableType = szDBASE then
begin
Check(DbiGetIndexSeqNo(Mas ter.Handle , MIndex.szName, MIndex.szTagName, 0, MNo));
Check(DbiGetIndexSeqNo(Det ail.Handle , DIndex.szName, DIndex.szTagName, 0, DNo));
// Put the detail field index in the array...
RInt.aiThisTabFld[0] := DNo;
// Put the master field index in the array...
RInt.aiOthTabFld[0] := MNo;
end;
try
Master.Close;
Detail.Close;
Check(DbiDoRestructure(hDb , 1, @TableDesc, nil, nil, nil, FALSE));
finally
Master.Open;
Detail.Open;
end;
end;
Master, Detail: TTable -> must be opened exclusive
MFields, DFields : Array of word -> Field Number(s), not zeroBased,
count must be the same
RIName: string -> unique name of the Referential Integrity
ModOp, DelOp: RINTQual -> rintRESTRICT or rintCASCADE (delOp can only be
rintRESTRICT)
RINTNum : Word -> Index of the Referential Integrity (zeroBased) -> can
allways be zero
following restrictions i have checked out
the field(s) on the detailtable must be primary or secondary indexed
if there are more Referential Integrities as one then the other dependend tables must be closed before adding the new Referential Integrity.
-------------------------- ---------- ----
hope this helps
meikl
from my paq,
procedure AddRI(Master, Detail: TTable; MFields, DFields : Array of word ;RIName: string; ModOp, DelOp: RINTQual; RINTNum : Word);
var
MasterProps, DetailProps: CURProps;
hDb: hDBIDb;
TableDesc: CRTblDesc;
Op: CROpType;
RInt: RINTDesc;
MIndex, DIndex: IDXDesc;
MNo, DNo: Word;
I : Integer;
begin
if Master.Active = False then
raise EDatabaseError.Create('Mas
' is not opened');
if Detail.Active = False then
raise EDatabaseError.Create('Det
' is not opened');
if Master.Exclusive = False then
raise EDatabaseError.Create('Tab
' must be opened exclusively');
if Detail.Exclusive = False then
raise EDatabaseError.Create('Tab
' must be opened exclusively');
// Make sure the tables are opened with an index and get their descriptors...
FillChar(DIndex, sizeof(DIndex), 0);
FillChar(MIndex, sizeof(MIndex), 0);
Check(DbiGetIndexDesc(Deta
Check(DbiGetIndexDesc(Mast
// Get the table properties to determine table type...
Check(DbiGetCursorProps(Ma
Check(DbiGetCursorProps(De
// If the table is not a Paradox table, raise an error...
if (MasterProps.szTableType <> szPARADOX) and
(MasterProps.szTableType <> szDBASE) then
raise EDatabaseError.Create('Mas
' must be a Paradox or dBASE table type');
if (DetailProps.szTableType <> szPARADOX) and
(DetailProps.szTableType <> szDBASE) then
raise EDatabaseError.Create('Det
' must be a Paradox or dBASE table type');
if MasterProps.szTableType <> DetailProps.szTableType then
raise EDatabaseError.Create('Mas
// Blank out the structures...
FillChar(TableDesc, sizeof(TableDesc), 0);
FillChar(RInt, sizeof(RInt), 0);
// Get the database handle from the table's cursor handle...
Check(DbiGetObjFromObj(hDB
// Put the table name in the table descriptor...
StrPCopy(TableDesc.szTblNa
// Put the table type in the table descriptor...
TableDesc.szTblType := MasterProps.szTableType;
// Set the operation type...
Op := crADD;
TableDesc.pecrRintOp := @Op;
// Set the amount of new RI descriptors...
TableDesc.iRintCount := 1;
// Connect the table descriptor to the RI descriptor...
TableDesc.printDesc := @RInt;
// Setup the RI descriptor...
// Put in the name of the RI...
StrPCopy(RInt.szRintName, RIName);
// Do the restructure on the dependent (detail) table...
RInt.eType := rintDEPENDENT;
// Add the master table name...
StrPCopy(RInt.szTblName, Master.TableName);
// Modify operations will be restricted (this can be changed to rintCASCADE)...
RInt.eModOp := ModOp;
// Delete operations will be restricted (NOTE: rintCASCADE will not work)...
RInt.eDelOp := DelOp;
// Only one field in link...
RInt.iFldCount := high(MFields) + 1;
// If the tables are Paradox, then put the associated field numbers in the descriptor...
if (MasterProps.szTableType = szPARADOX) then
begin
//
if RInt.eDelOp = rintCASCADE then
raise EDatabaseError.Create('Can
// Put the detail field index in the array...
// RInt.aiThisTabFld := DIndex.aiKeyFld;
for i := low(DFields) to High(DFields) do
RInt.aiThisTabFld[I] := DFields[I];
// Put the master field index in the array...
// RInt.aiOthTabFld := MIndex.aiKeyFld;
for i := low(MFields) to High(MFields) do
RInt.aiOthTabFld[I] := MFields[I];
RInt.iRintNum := RINTNum;
end;
// If the tables are dBASE, then put the sequence number in the descriptor...
if MasterProps.szTableType = szDBASE then
begin
Check(DbiGetIndexSeqNo(Mas
Check(DbiGetIndexSeqNo(Det
// Put the detail field index in the array...
RInt.aiThisTabFld[0] := DNo;
// Put the master field index in the array...
RInt.aiOthTabFld[0] := MNo;
end;
try
Master.Close;
Detail.Close;
Check(DbiDoRestructure(hDb
finally
Master.Open;
Detail.Open;
end;
end;
Master, Detail: TTable -> must be opened exclusive
MFields, DFields : Array of word -> Field Number(s), not zeroBased,
count must be the same
RIName: string -> unique name of the Referential Integrity
ModOp, DelOp: RINTQual -> rintRESTRICT or rintCASCADE (delOp can only be
rintRESTRICT)
RINTNum : Word -> Index of the Referential Integrity (zeroBased) -> can
allways be zero
following restrictions i have checked out
the field(s) on the detailtable must be primary or secondary indexed
if there are more Referential Integrities as one then the other dependend tables must be closed before adding the new Referential Integrity.
--------------------------
hope this helps
meikl
just forgot,
a sample how to call
here the sample, how to call.
table1 is a producttable and holds a VendorID, DepartmentID, InventoryID, and a OtherID, pointed to tables, which holds additional informations.
procedure TForm1.Button1Click(Sender : TObject);
begin
AddRI(Table2, Table1, [1], [2], 'RIVendorProduct', RINTRestrict, RINTRestrict,0);
Table2.Close;
AddRI(Table3, Table1, [1], [3], 'RIDepartmentProduct', RINTRestrict, RINTRestrict,0);
Table3.Close;
AddRI(Table4, Table1, [1], [4], 'RIInventoryProduct', RINTRestrict, RINTRestrict,0);
Table4.Close;
AddRI(Table5, Table1, [1], [5], 'RIOtherProduct', RINTRestrict, RINTRestrict,0);
Table2.Open;
Table3.Open;
Table4.Open;
end;
meikl
a sample how to call
here the sample, how to call.
table1 is a producttable and holds a VendorID, DepartmentID, InventoryID, and a OtherID, pointed to tables, which holds additional informations.
procedure TForm1.Button1Click(Sender
begin
AddRI(Table2, Table1, [1], [2], 'RIVendorProduct', RINTRestrict, RINTRestrict,0);
Table2.Close;
AddRI(Table3, Table1, [1], [3], 'RIDepartmentProduct', RINTRestrict, RINTRestrict,0);
Table3.Close;
AddRI(Table4, Table1, [1], [4], 'RIInventoryProduct', RINTRestrict, RINTRestrict,0);
Table4.Close;
AddRI(Table5, Table1, [1], [5], 'RIOtherProduct', RINTRestrict, RINTRestrict,0);
Table2.Open;
Table3.Open;
Table4.Open;
end;
meikl
just to mention, that this is delphi-code, no bcb
ASKER
thanks for the example, meikl. it's very similar to the code i converted from delphi...
ok, the real problem appears to be the secondary index business. but i'd prefer not to constrain myself with that, since it means that i'll have to create the secondary index in run-time, too, which doesn't make life easier; the software refuses to issue the AddIndex() method. so bde API seems to have a general trouble in creating indexes in run-time. if databse desktop can do it, why can't i?
so let's change the question to: "given field and foreign key definitions, create two paradox tables and necessary RI's."
the creation part is no problem, but it's a prerequisite. any suggestions?
T.
ok, the real problem appears to be the secondary index business. but i'd prefer not to constrain myself with that, since it means that i'll have to create the secondary index in run-time, too, which doesn't make life easier; the software refuses to issue the AddIndex() method. so bde API seems to have a general trouble in creating indexes in run-time. if databse desktop can do it, why can't i?
so let's change the question to: "given field and foreign key definitions, create two paradox tables and necessary RI's."
the creation part is no problem, but it's a prerequisite. any suggestions?
T.
ASKER
thanks for the example, meikl. it's very similar to the code i converted from delphi...
ok, the real problem appears to be the secondary index business. but i'd prefer not to constrain myself with that, since it means that i'll have to create the secondary index in run-time, too, which doesn't make life easier; the software refuses to issue the AddIndex() method. so bde API seems to have a general trouble in creating indexes in run-time. if databse desktop can do it, why can't i?
so let's change the question to: "given field and foreign key definitions, create two paradox tables and necessary RI's."
the creation part is no problem, but it's a prerequisite. any suggestions?
T.
ok, the real problem appears to be the secondary index business. but i'd prefer not to constrain myself with that, since it means that i'll have to create the secondary index in run-time, too, which doesn't make life easier; the software refuses to issue the AddIndex() method. so bde API seems to have a general trouble in creating indexes in run-time. if databse desktop can do it, why can't i?
so let's change the question to: "given field and foreign key definitions, create two paradox tables and necessary RI's."
the creation part is no problem, but it's a prerequisite. any suggestions?
T.
hi tunct,
well, how to create just from scratch
(delphi-code of course)
procedure TForm1.Button1Click(Sender : TObject);
begin
Table1.DatabaseName := 'pdx'; //MasterTable
Table1.tableName := 'OrderHead';
Table1.FieldDefs.Add('Id', ftInteger, 0,True);
Table1.FieldDefs.Add('AFie ld',ftStri ng,20,Fals e);
Table1.FieldDefs.Add('BFie ld',ftStri ng,20,Fals e);
Table1.FieldDefs.Add('CFie ld',ftStri ng,20,Fals e);
Table1.IndexDefs.Add('Id_P K','Id',[i xPrimary,i xUnique]); //PK
Table1.CreateTable; //CreateTable
Table2.DatabaseName := 'pdx'; //DetailTable
Table2.tableName := 'OrderDetail';
Table2.FieldDefs.Add('Id', ftInteger, 0,True);
Table2.FieldDefs.Add('Head _Id',ftInt eger,0,Tru e); //The FK must defined in the structure
Table2.FieldDefs.Add('AFie ld',ftStri ng,20,Fals e);
Table2.FieldDefs.Add('BFie ld',ftStri ng,20,Fals e);
Table2.FieldDefs.Add('CFie ld',ftStri ng,20,Fals e);
Table2.IndexDefs.Add('Id_P K','Id',[i xPrimary,i xUnique]); //PK
//Secondary -> give no name to force a creation of an standard Secondary Index (X0?,Y0?)
Table2.IndexDefs.Add('','H ead_Id',[] );
Table2.CreateTable; //CreateTable
Table1.Exclusive := True; //OpenExclusive
Table2.Exclusive := True;
Table1.Open;
Table2.Open;
AddRi(Table1,Table2,[1],[2 ],'RIDetai lToHead',R INTRestric t, RINTRestrict,0); //Add Referential Integrity
end;
hope this helps
meikl
well, how to create just from scratch
(delphi-code of course)
procedure TForm1.Button1Click(Sender
begin
Table1.DatabaseName := 'pdx'; //MasterTable
Table1.tableName := 'OrderHead';
Table1.FieldDefs.Add('Id',
Table1.FieldDefs.Add('AFie
Table1.FieldDefs.Add('BFie
Table1.FieldDefs.Add('CFie
Table1.IndexDefs.Add('Id_P
Table1.CreateTable; //CreateTable
Table2.DatabaseName := 'pdx'; //DetailTable
Table2.tableName := 'OrderDetail';
Table2.FieldDefs.Add('Id',
Table2.FieldDefs.Add('Head
Table2.FieldDefs.Add('AFie
Table2.FieldDefs.Add('BFie
Table2.FieldDefs.Add('CFie
Table2.IndexDefs.Add('Id_P
//Secondary -> give no name to force a creation of an standard Secondary Index (X0?,Y0?)
Table2.IndexDefs.Add('','H
Table2.CreateTable; //CreateTable
Table1.Exclusive := True; //OpenExclusive
Table2.Exclusive := True;
Table1.Open;
Table2.Open;
AddRi(Table1,Table2,[1],[2
end;
hope this helps
meikl
ASKER
hi meikl,
ok, not giving a name to the 2ndary index did the trick... should've figured out that myself :) thanks.
i'll give you the points if you post an answer.
btw, do you know how to define the RI as "strict"? (that won't affect the grading, though :)
regards,
T.
ok, not giving a name to the 2ndary index did the trick... should've figured out that myself :) thanks.
i'll give you the points if you post an answer.
btw, do you know how to define the RI as "strict"? (that won't affect the grading, though :)
regards,
T.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
by "strict". i meant the option seen in the database desktop's RI definition dialog... it's a protection against older paradox versions.
never mind, thanks again.
T.
never mind, thanks again.
T.
i think you must configure working dir. right to define a RI. because BDE uses the tables from same working dir to create a relation ship between them.