Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

referential integrity problem using DbiDoRestructure

Posted on 2000-03-12
12
Medium Priority
?
338 Views
Last Modified: 2010-04-04
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
0
Comment
Question by:tunct
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 

Expert Comment

by:deeptesla
ID: 2609964
hi tunct,
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.
0
 

Author Comment

by:tunct
ID: 2610142
Adjusted points to 200
0
 

Author Comment

by:tunct
ID: 2610143
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 27

Expert Comment

by:kretzschmar
ID: 2611349
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('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 := 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('Cannot 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(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;

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

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2611358
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
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2611366
just to mention, that this is delphi-code, no bcb
0
 

Author Comment

by:tunct
ID: 2614388
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.
0
 

Author Comment

by:tunct
ID: 2614390
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.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2614841
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('AField',ftString,20,False);
  Table1.FieldDefs.Add('BField',ftString,20,False);
  Table1.FieldDefs.Add('CField',ftString,20,False);
  Table1.IndexDefs.Add('Id_PK','Id',[ixPrimary,ixUnique]); //PK

  Table1.CreateTable;                                  //CreateTable

  Table2.DatabaseName := 'pdx';                       //DetailTable
  Table2.tableName := 'OrderDetail';
  Table2.FieldDefs.Add('Id',ftInteger,0,True);
  Table2.FieldDefs.Add('Head_Id',ftInteger,0,True);   //The FK must defined in the structure
  Table2.FieldDefs.Add('AField',ftString,20,False);
  Table2.FieldDefs.Add('BField',ftString,20,False);
  Table2.FieldDefs.Add('CField',ftString,20,False);
  Table2.IndexDefs.Add('Id_PK','Id',[ixPrimary,ixUnique]); //PK

  //Secondary -> give no name to force a creation of an standard Secondary Index (X0?,Y0?)
  Table2.IndexDefs.Add('','Head_Id',[]);

  Table2.CreateTable;                                 //CreateTable

  Table1.Exclusive := True;                           //OpenExclusive
  Table2.Exclusive := True;

  Table1.Open;
  Table2.Open;

  AddRi(Table1,Table2,[1],[2],'RIDetailToHead',RINTRestrict, RINTRestrict,0);  //Add Referential Integrity
end;

hope this helps

meikl
0
 

Author Comment

by:tunct
ID: 2616690
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.
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 600 total points
ID: 2616957
well, T. ;-)

glad to helped you,
often has a problem a simple solution,
but it takes time to get this simple solution.

what does you mean with "strict"?
just from bde-api help

RINTQual      Specifies the modify qualifier, either rintRESTRICT or rintCASCADE

there are only this two entrys, where
rintRESTRICT  Modify or delete forbidden, if there is an entry
rintCASCADE  Forward Modification and deletions
means

meikl ;-)
0
 

Author Comment

by:tunct
ID: 2617447
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.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question