RefreshRecord, Master/Detail, KeyFields, Minor Bug: ParamOverwriting

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
dschuch
Posts: 73
Joined: Thu 05 Feb 2009 15:29
Location: Dresden

RefreshRecord, Master/Detail, KeyFields, Minor Bug: ParamOverwriting

Post by dschuch » Wed 28 May 2014 11:47

Hi,

Imagine the following Example:

2 Tables, bothe have the KeyField ID.

MasterDataSet

Code: Select all

SELECT id FROM mastertable
DetailDataSet

Code: Select all

SELECT id, detaildata, mastertable_id FROM detailtable WHERE mastertable_id=:id

Code: Select all

CREATE TABLE mastertable(id SERIAL PRIMARY KEY, data VARCHAR);
CREATE TABLE detailtable(id SERIAL PRIMARY KEY, mastertable_id INTEGER REFERENCES mastertable, detaildata VARCHAR);
I use the Mode ParamName=MasterFieldName as descriped in your help
"Working with Master/Detail Relationships" Pay attention to one thing: 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.
So master Detail is working fine in this way.

If u call RefreshRecord on the DetailDataSet now, DevArt will add the Where Clause depending on the KeyFields. The KeyField in the Detail is "id" too. Additionally the Where Clause added will get a Parameter with the same Name as the Key Field, in this Case "Id" too.

The Result is, that a ParameterOverload happens. The Resulting SQL will be

Code: Select all

WHERE
 mastertable_id=:id
AND
 id=:id  < this part is added for RefreshRecord of Detail.
U see that this condition will never work.

Solution: wether u make a random-paramname, or u check out if the paramname already exists and make it another name.

WorkAround: In DetailDataSet:

Code: Select all

SELECT id AS myid, detaildata, mastertable_id FROM detailtable WHERE mastertable_id=:id

Code: Select all

DetailDataSet.KeyFields:='myid'
Now the Where-Statement is correct with RefreshRecord:

Code: Select all

WHERE mastertable_id=:id
AND
 id=:myid < this part is added for RefreshRecord of Detail.
[/code]

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: RefreshRecord, Master/Detail, KeyFields, Minor Bug: ParamOverwriting

Post by AlexP » Thu 29 May 2014 09:04

Hello,

In the latest PgDAC version, on generating a RefreshRecord query, the basic query is not taken into account. Current record update query is generated again without conditions used in the basic query (only key fields of this table are added to the WHERE section). Please try to reproduce the problem on the latest PgDAC version and let us know the result.

dschuch
Posts: 73
Joined: Thu 05 Feb 2009 15:29
Location: Dresden

Re: RefreshRecord, Master/Detail, KeyFields, Minor Bug: ParamOverwriting

Post by dschuch » Fri 30 May 2014 12:38

Thanks for your awnser, i use the latest version.

ps - great work, after some trouble when upgrading from version 3.x its finally working, faster and better now ;)

i think the reason for the problem in this thread is that we use FullRefresh=True => i want also joined fields refreshed.

Daniel.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: RefreshRecord, Master/Detail, KeyFields, Minor Bug: ParamOverwriting

Post by AlexP » Fri 30 May 2014 13:22

Yes, when using FullRefresh, we don't change the basic SQL statement, and add conditions to it. In this case, you should use aliases.

dschuch
Posts: 73
Joined: Thu 05 Feb 2009 15:29
Location: Dresden

Re: RefreshRecord, Master/Detail, KeyFields, Minor Bug: ParamOverwriting

Post by dschuch » Mon 02 Jun 2014 17:24

ye, but u will agree:
if there is already a parameter in the statement, u cant simply take this parameter for a selectrecord refresh. if the parameter woudlnt be there u would create a new one.
so u should create a new parameter with another name or use a unique name, so the original parameter wont be overwritten.

daniel.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: RefreshRecord, Master/Detail, KeyFields, Minor Bug: ParamOverwriting

Post by AlexP » Thu 05 Jun 2014 07:24

We generate parameters by field names, therefore we cannot use the suggested approach without changing the components behavior.

dschuch
Posts: 73
Joined: Thu 05 Feb 2009 15:29
Location: Dresden

Re: RefreshRecord, Master/Detail, KeyFields, Minor Bug: ParamOverwriting

Post by dschuch » Thu 12 Jun 2014 08:04

ok, we know now how to handle it, thanks.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: RefreshRecord, Master/Detail, KeyFields, Minor Bug: ParamOverwriting

Post by AlexP » Thu 12 Jun 2014 08:47

You are welcome. Feel free to contact us if you have any further questions.

Post Reply