Difference between revisions of "MasterDetail"
(link to reference docs) |
|||
| Line 7: | Line 7: | ||
== SQLDB implementation == | == SQLDB implementation == | ||
The approach below applies to both Lazarus and FPC code that uses the built in [[SQLdb Package]] database layer. | The approach below applies to both Lazarus and FPC code that uses the built in [[SQLdb Package]] database layer. | ||
| + | |||
| + | Reference documentation on using parameters: [http://www.freepascal.org/docs-html/fcl/sqldb/usingparams.html Using params] | ||
Example from the EMPLOYEE database (as used in [[SQLdb Tutorial1]] and other tutorials): | Example from the EMPLOYEE database (as used in [[SQLdb Tutorial1]] and other tutorials): | ||
Revision as of 11:32, 2 February 2013
The term "master/detail" is a database concept that means that there is a general dataset/table (the master dataset). For each item in the master dataset, zero, one or more detail items are available in a detail dataset. In relational databases, this is represented by a foreign key in the detail table to the master table.
Classical examples of master/detail relations are
- customers with orders
- orders with ordered articles
SQLDB implementation
The approach below applies to both Lazarus and FPC code that uses the built in SQLdb Package database layer.
Reference documentation on using parameters: Using params
Example from the EMPLOYEE database (as used in SQLdb Tutorial1 and other tutorials):
- a CUSTOMER table with an integer primary key CUST_NO and other fields
- a SALES table with a CUST_NO integer field that is a foreign key linking to the CUST_NO field in the CUSTOMER table
On the form:
- use one connection, 1 transaction, but 2 queries, 2 data sources
- a master query called qryCustomers selects from the CUSTOMER table
- a detail query called qrySales that selects from the SALES table
In the detail query qrySales:
- set the database property as usual
- set the datasource property to point to the master datasource
- in the query SQL, use a WHERE query to limit the select; use the detail fieldname and limit it to a parameter with the name of the field in the master table
SELECT * from SALES WHERE SALES.CUST_NO=:CUST_NO
In this case we use the SALES.CUST_NO field that happens to have the same field name as the master field CUST_NO, but that need not be the case. As the master datasource keeps track of the current record for the master query qryCustomers, FPC can see the CUST_NO parameter as a reference to the current value of the CUST_NO field in the master/qryCustomers query.
Make sure that the master query is open before the detail query so it can look up fields.
Adding detail records with the right foreign key
The Delphi documentation linked above omits to mention that there is a need for further code: When adding new detail records, the SALES.CUST_NO field is still NULL unless we fill it. So we need to set up an AfterInsert event handler for qrySales:
procedure TForm1.qrySalesAfterInsert(DataSet: TDataSet);
begin
DataSet.FieldByName('CUST_NO').AsInteger := qryCustomers.FieldByName('CUST_NO').AsInteger;
end;