Devart Forums

Posted: **Thu 04 Aug 2016 07:07**

I am looking at converting a massive application to UniDac. This involves a lot of master/detail tables. Since this was done in the early days of dbExpress, I had a TNewTable component written to simulate a TTable component which also included master detail linking with SQL commands similar to how UniDac does it.

I need to clarify the UniDac's operation of the master/detail links.

This is from the help file:

parameter name in detail dataset SQL must be equal to the field name in the master dataset that is used as foreign key for detail table.

This matches my system in place already, however none of the tables linked with foreign keys. In some cases the linking is ad-hoc so wouldn't be foreign key linked anyway (for example a postcode).

Is it possible to have this work where it looks at the parameters and links any matching with master field names, or is it not possible? This would skip the foreign key requirement and just look for fields in the master table with the same name as the parameter. My TNewTable component did it with DataLinks, based on how the original TTable component worked, I am not sure how yours works.

The second method in the help file is to specify the fields.

// setup MD
Detail.MasterFields := 'Dept_No'; // primary key in Department
Detail.DetailFields := 'DepLink'; // foreign key in Employee

I could convert my code to this scenario, however what about other conditions in the where clause? The help file says:

Also note that there are no WHERE clause in detail dataset SQL.

The where clause might contain other filtering criteria as well as the master/detail link. For example all the orders for customer (master/detail), but only over $1000 (where clause). Does UniDac honor the where clause for the other criteria and just append its own criteria?

I have thousands of these links in the application so need something that translates.

Thanks.

Posted: **Tue 09 Aug 2016 09:56**

We have written a small application demonstrating Master-Detail implementation principles. The are two tables created in the sample: Dept and Emp. The Emp table has no foreign constraint, that would relate it with the Dept table. Relation between tables is implemented by the fields Dept.DeptNo and Emp.DeptLink :

Code: Select all

...
    SQLiteUniProvider: TSQLiteUniProvider;
    UniConnection: TUniConnection;
    QueryDept: TUniQuery;
    QueryEmp: TUniQuery;
    DataSourceDept: TUniDataSource;
    DataSourceEmp: TUniDataSource;
...
UniConnection.ProviderName := 'SQLite';
  UniConnection.SpecificOptions.Values['Direct'] := 'True';
  UniConnection.Database := ':memory:';
  UniConnection.Connect;
  UniConnection.ExecSQL('Create Table Dept (DeptNo Number, DName Varchar(16))');
  UniConnection.ExecSQL('Insert Into Dept Values (10, ''ACCOUNTING'')');
  UniConnection.ExecSQL('Insert Into Dept Values (20, ''RESEARCH'')');
  UniConnection.ExecSQL('Insert Into Dept Values (30, ''SALES'')');
  UniConnection.ExecSQL('Create Table Emp (EmpNo Number, EName Varchar(16), Salary Number, DeptLink Number)');
  UniConnection.ExecSQL('Insert Into Emp Values (7934, ''MILLER'', 1500, 10)');
  UniConnection.ExecSQL('Insert Into Emp Values (7839, ''KING'', 2600, 10)');
  UniConnection.ExecSQL('Insert Into Emp Values (7369, ''SMITH'', 1600, 20)');
  UniConnection.ExecSQL('Insert Into Emp Values (7566, ''JONES'', 5600, 20)');
  UniConnection.ExecSQL('Insert Into Emp Values (7876, ''ADAMS'', 3200, 20)');
  UniConnection.ExecSQL('Insert Into Emp Values (7499, ''ALLEN'', 1200, 30)');
  UniConnection.ExecSQL('Insert Into Emp Values (7654, ''MARTIN'', 2500, 30)');
  UniConnection.ExecSQL('Insert Into Emp Values (7900, ''JAMES'', 2200, 30)');

  QueryDept.SQL.Text := 'Select * From Dept';
  QueryDept.Open;

  QueryEmp.SQL.Text := 'Select * From Emp Where Salary > 1500 And DeptLink=:DeptNo';
  QueryEmp.MasterSource := DataSourceDept;
  QueryEmp.Open;

In this case, we used filter by field Salary in the query to the Emp table. In addition, implementation of Master-Detail relation may be like the following:

Code: Select all

QueryEmp.SQL.Text := 'Select * From Emp Where Salary > 1500';
  QueryEmp.MasterFields := 'DeptNo';
  QueryEmp.DetailFields := 'DeptLink';
  QueryEmp.MasterSource := DataSourceDept;
  QueryEmp.Open;

Devart Forums

Master/Detail field linking

Master/Detail field linking

Re: Master/Detail field linking