BDE - referential integrity and val

Check out this procedure...
you can find help about all Dbi functions in Delphi help

procedure AddRI(Master, Detail: TTable; RIName: string; ModOp, DelOp: RINTQual);
var
  MasterProps, DetailProps: CURProps;
  hDb: hDBIDb;
  TableDesc: CRTblDesc;
  Op: CROpType;
  RInt: RINTDesc;
  MIndex, DIndex: IDXDesc;
  MNo, DNo: Word;
begin
  if Master.Active = False then
    raise EDatabaseError.Create('Master table: ' + Master.TableName +
                                ' is not opened');
  if Detail.Active = False then
    raise EDatabaseError.Create('Detail table: ' + Detail.TableName +
                                ' is not opened');
  if Master.Exclusive = False then
    raise EDatabaseError.Create('Table: ' + Master.TableName +
                                ' must be opened exclusively');
  if Detail.Exclusive = False then
    raise EDatabaseError.Create('Table: ' + 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(Detail.Handle, 0, DIndex));
  Check(DbiGetIndexDesc(Master.Handle, 0, MIndex));
  // Get the table properties to determine table type...
  Check(DbiGetCursorProps(Master.Handle, MasterProps));
  Check(DbiGetCursorProps(Detail.Handle, DetailProps));
  // If the table is not a Paradox table, raise an error...
  if (MasterProps.szTableType <> szPARADOX) and
     (MasterProps.szTableType <> szDBASE) then
     raise EDatabaseError.Create('Master table: ' + Master.TableName +
                                 ' must be a Paradox or dBASE table type');
  if (DetailProps.szTableType <> szPARADOX) and
     (DetailProps.szTableType <> szDBASE) then
     raise EDatabaseError.Create('Detail table: ' + Detail.TableName +
                                 ' must be a Paradox or dBASE table type');
  if MasterProps.szTableType <> DetailProps.szTableType then
     raise EDatabaseError.Create('Master 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(hDBIObj(Master.Handle), objDATABASE, hDBIObj(hDb)));
  // Put the table name in the table descriptor...
  StrPCopy(TableDesc.szTblName, 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 := 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('Cannot use cascading delete RI with Paradox tables');
    // Put the detail field index in the array...
    RInt.aiThisTabFld := DIndex.aiKeyFld;
    // Put the master field index in the array...
    RInt.aiOthTabFld := MIndex.aiKeyFld;  end;
  // If the tables are dBASE, then put the sequence number in the descriptor...
  if MasterProps.szTableType = szDBASE then
  begin
    Check(DbiGetIndexSeqNo(Master.Handle, MIndex.szName, MIndex.szTagName, 0, MNo));
    Check(DbiGetIndexSeqNo(Detail.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;

samchan

check out the TUtil stuff they allow you to reindex, pack , fix key violations etc...

I forget the url now but just do a search on DSP

http://SunSITE.icm.edu.pl/delphi/

Later
BoRiS

Listening..

samchan, when you use a proper database engine (I mean something that comes with log facilities ... sqlanywhere, sqlserver, oracle ... and even interbase) you do not need to care with such question. because the database system is almost always able to reconstruct from log. and if you add some backup planning you are almost safe in every circumstances.

I am using SQLAnywhere for several years now, and I never had to reconstruct anything.

When you are using something less robust, let's say BDE or ACCESS, you will sometimes will have to use utilities that will try to put the things back togeather.

I suggest you use another engine, specially if you plan to have many datas in your database.

I consider SQLANYwhere studio as a good buy (if you want to buy something) because you can write java procedures with it. Otherways take Interbase


If You want to stick on BDE, I suggest you get the tools Boris Told you about.

cheers,

Marc

BUT IN NO CASE IT IS YOUR JOB TO WRITE SUCH UTILITIES. Because it is your provider's jub not yours, and because you know nothing about the internal file structures of your database.  

Thank you. That's is really a lot of coding. Well, although, I have given you the points, I will be even more happy, if u were to give me some examples on how to use the referential integrity procedure.

hi samchan,

first thanks for accepting,
sample follows, because i've the sample source at home. i will post it in 4-5 hours.

meikl ;-)

hi again samchan,

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;

be free to ask for further details

meikl

appendix,

you must add the unit bde in the uses-clause.
just for your infoemation

meikl

Whew! That's really great.
Thank you.
You're really very helpful. You will be on your way to the top of the experts scorer list by continuing giving such fantastic help. Once again, Thank you.