Sign in Join

Live chat for Free Pascal, Lazarus, Delphi and every Pascal programmer.

Mailing lists / lazarus / 2012-12

Master/detail relationships: what am I missing?

5 messages · 3 people · –

Reply by email Subscribe to this list Your subscription

Read-only archive. Use j and k to move between messages.

Reinier Olislagers

Hi all,

I've been looking into how to use master/detail setups in Lazarus (aka tables with foreign keys).

Based on Delphi docs I started writing up & testing this:

Show quoted text 19 lines
See also http://docwiki.embarcadero.com/RADStudio/XE3/en/Establishing_Master-detail_Relationships_Using_Parameters Use one connection, 1 transaction, but 2 queries, 2 data sources Example from the Firebird EMPLOYEE database (as used in the SQLDB 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 - a master query selects from the CUSTOMER table - a detail query that selects from the SALES table In the detail query: - 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 a parameter with the name of the field in the master table SELECT * from SALES WHERE SALES.CUST_NO=:CUST_NO FPC will now see this as a reference to the current value of CUST_NO in the CUSTOMER (master) query. This is made possible by the fact that the master datasource keeps track of the current record for the master query Make sure that the master query is open before the detail query so it can look up fields. No need for further code, e.g. to specify default values for the SALES.CUST_NO field when adding records.

I get the form with a combobox for the master field and details grid to run without issue and can insert items in the detail grid but the foreign key to the master table is null for that record.

Am I missing something in the description above or is there another way of doing it (I've read about using the master query's afterscroll event).

Thanks,
Reinier

Reply by email

Unknown author
to Reinier Olislagers
Reinier Olislagers wrote 26 lines
Hi all, I've been looking into how to use master/detail setups in Lazarus (aka tables with foreign keys). Based on Delphi docs I started writing up & testing this: > See also > http://docwiki.embarcadero.com/RADStudio/XE3/en/Establishing_Master-detail_Relationships_Using_Parameters > Use one connection, 1 transaction, but 2 queries, 2 data sources > Example from the Firebird EMPLOYEE database (as used in the SQLDB 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 > - a master query selects from the CUSTOMER table > - a detail query that selects from the SALES table > > In the detail query: > - 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 a parameter with the name of the field in the master table > SELECT * from SALES WHERE SALES.CUST_NO=:CUST_NO > FPC will now see this as a reference to the current value of CUST_NO in the CUSTOMER (master) query. This is made possible by the fact that the master datasource keeps track of the current record for the master query > > Make sure that the master query is open before the detail query so it can look up fields. > > No need for further code, e.g. to specify default values for the SALES.CUST_NO field when adding records.

This is wrong. There is a need. This should still be specified.

I get the form with a combobox for the master field and details grid to run without issue and can insert items in the detail grid but the foreign key to the master table is null for that record.

Correct. You must enter these values yourself in the AfterInsert of the detail dataset.

Michael.

Reply by email

Reinier Olislagers
to Unknown author
Show quoted text 10 lines
On Mon, 17 Dec 2012, Reinier Olislagers wrote: > Hi all, > > I've been looking into how to use master/detail setups in Lazarus (aka > tables with foreign keys). > > Based on Delphi docs I started writing up & testing this: >> See also >> http://docwiki.embarcadero.com/RADStudio/XE3/en/Establishing_Master-detail_Relationships_Using_Parameters

<snip>

Show quoted text 12 lines
>> No need for further code, e.g. to specify default values for the >> SALES.CUST_NO field when adding records. This is wrong. There is a need. This should still be specified. > > I get the form with a combobox for the master field and details grid to > run without issue and can insert items in the detail grid but the > foreign key to the master table is null for that record. Correct. You must enter these values yourself in the AfterInsert of the detail dataset.

Thanks, Michael... seems like the Delphi docs were a bit too optimistic ;)

Created
http://wiki.freepascal.org/MasterDetail
updates/corrections welcome as usual!

Reply by email

John
to Reinier Olislagers
Reinier Olislagers wrote
<snip>

As you are playing with Master/detail, how are you setting the parameter datatype in the detail SQL ? I am finding that if I set it at design time (in the object inspector), that value is not restored on run, or on re-poening Lazarus. I have worked around this by setting it in a beforeopen event on the table. (This has been a problem for years. It used to forget paramtype as well, but this seems to be fixed now)

Also, sometimes, when the master is scrolled, the detail query closes. (I have put a check / repoen the detail query in an afterscroll event on the master query to work around this).

Are you seeing either of these, or is it just me / my system / my application ?

cheers,
John Sunderland

Reply by email

Reinier Olislagers
to John
John wrote 6 lines
On 12/17/2012 09:43 PM, Reinier Olislagers wrote: As you are playing with Master/detail, how are you setting the parameter datatype in the detail SQL ? I am finding that if I set it at design time (in the object inspector), that value is not restored on run, or on re-poening Lazarus. I have worked around this by setting it in a beforeopen event on the table.

See the wiki page I linked to. No problems with that minimal approach (Firebird embedded, FPC x86 2.6 (or possibly trunk), Laz trunk, Windows).

I do vaguely remember some issues with having to set parameter type on PostgreSQL but haven't looked into it.

Also, sometimes, when the master is scrolled, the detail query closes. (I have put a check / repoen the detail query in an afterscroll event on the master query to work around this).

Haven't seen it but have made no extensive tests.
If you can reproduce, could you file a bug report?

In any case, additions to the wiki welcome - even to document workarounds for failures that may not happen to everyone.

Reply by email