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

[dschuch]

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]

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]

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]

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]

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]

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

[dschuch]

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

[AlexP]

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