IT SOLUTIONS
Your full service technology partner! 
-Collapse +Expand
Delphi
Search Delphi Group:

Advanced
-Collapse +Expand Delphi To/From
To/FromCODEGuides
-Collapse +Expand Delphi Store
PRESTWOODSTORE

Prestwood eMagazine

October Edition
Subscribe now! It's Free!
Enter your email:

   ► KBProgrammingDelphi for W...Using DataAdvantage Da...   Print This     
  From the August 2011 Issue of Prestwood eMag
 
Delphi Advantage Database:
ADS Components - Master/Detail datasets in Delphi 6
 
Posted 19 years ago on 12/10/2002
Take Away: Tips and techniques for creating a typical master/detail relationship between Advantage Database Server tables in a Delphi 6 form. This article also includes tips for using client datasets in relationships between ADS tables.

KB100080

In most SQL databases, referential integrity between two or more tables can be managed through foreign keys on related fields in each table.

When you build a master/detail relationship between two TTable components in Delphi 6, whether you are using ADO or dbExpress components, the relationship is always defined the same way:

  1. Assign the DataSource of the parent dataset to the MasterSource property of the child dataset.
  2. Use the MasterFields property editor on the child dataset to define the fields in the relationship.

In most SQL tables, there is no need to define indexes on the relational columns in both tables before you can add fields to a master/detail relationship. In fact, if you have indexes which include the relational columns (plus additional columns), they will improve the data access at run time.


In Advantage Database tables, however, there is a specific method to building master/detail datasets in a Delphi application.

Advantage Database Server does not provide foreign keys on tables, but you can build relationships on two tables if you define indexes specific to the columns in the relationship.

The TADSTable components in Delphi 6 do not have a MasterFields property editor. This is confusing to Delphi developers who are used to building master/detail datasets in BDE, ADO, or dbExpress.

Instead, the TADSTable component uses indexes on the parent and child datasets to define the master/detail relationship. If you attempt to assign the DataSource of the parent dataset to the MasterSource property of the child dataset, without defining indexes in both tables on the related columns you will get the following error in Delphi:

Error 5014 : 
Advantage does not have an index open with the specified handle. 
The handle must be to an index order.

You must begin by adding specific indexes to each table in ADS (using the Advantage Data Architect) on the columns in the relationship. Let's look at a very simple example:

Table Order.adt
Order_ID INTEGER
Customer_ID INTEGER
Customer_Name CHAR(30)
Order_Date TIMESTAMP
Table Shipment.adt
Shipment_ID INTEGER
Customer_ID INTEGER
Order_ID INTEGER
ShipTo_Address CHAR(200)
Fill_Date TIMESTAMP
Ship_Date TIMESTAMP

Now, to create a master/detail between these two tables, using the Customer_ID and Order_ID fields, you must:

  1. Define an index (X_CUSTOMER_ORDER) on Order (Customer_ID, Order_ID)
  2. Define a corresponding index (X_SHIPCUSTORDER) on Shipment (Customer_ID, Order_ID)

It doesn't matter if you already have a primary key index on Orders (Order_ID) on the table. The secondary index is required in order to define the master/detail relationship.

In Delphi 6, you would then create TADOTable components for the Order and Shipment tables. Then,

  1. Assign a TDataSource to the Order TADOTable
  2. Assign that datasource to the MasterSource property of the Shipment TADOTable
  3. In the MasterFields property of the Shipment TADOTable, type Customer_ID;Order_ID
  4. In the IndexName property of the Shipment TADOTable, type its relational index (X_SHIPCUSTORDER)

When you activate the two tables, the X_CUSTOMER_ORDER index on the Orders table resolves the relationship on the two columns. If the index is missing, you will get the Error 5014 message shown above.


Client Datasets

The same conditions apply when working with Dataset Providers and ClientDatasets on ADS tables in Delphi 6. Often, a developer may want to use Client Datasets to filter a related dataset based on user selections. The index requirements shown above must be in place before adding the Dataset Providers and ClientDatasets.

If the error:

Circular datalinks are not allowed.

appears while activating the datasets, this can be due to the fact that a master/detail relationship is defined between two ClientDatasets before the indexes are applied to the ADS tables. Quite often, (because the form or data module has become corrupted), the only way to fix this error is to delete the Client Dataset components and rebuild them (after the indexes are added to the ADS tables).


Comments

0 Comments.
Share a thought or comment...
 
Write a Comment...
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1274A1
Enter key:
KB Post Contributed By Scott Wehrly:

Scott Wehrly is currently working on .Net web applications for the gaming industry. Scott is a former employee of Prestwood Software (he was a Development Manager). Scott's specialties include C#, ASP.Net, MSSQL Server 2005, Delphi, SQL databases, C++, C, and Windows programming in general. When time allows, he participates in this online community.

Visit Profile

 KB Article #100080 Counter
17918
Since 4/2/2008
Sales Website: www.prestwood.com Or visit our legacy sales site: 
legacy.prestwood.com


©1995-2021 Prestwood IT Solutions.   [Security & Privacy]