Master/Detail with NestedDataSet on ClientDataSet

[Theprasit]

I'm in process of migrate my client application from D6 using ODAC 5.10 to DXE with latest ODAC trial version. I faced a problem on using master/detail with nesteddataset in datasnap server and client. My client database had different column name among master and detail as in the statement below:-

select a.*
,(select b.col_type from bot_col_type b where b.code_type = 'CODE' and b.code = a.asset_type and rownum = 1)asset_type_name
,colsubtypedesc(a.asset_type,a.asset_sub_type) asset_sub_type_name
,ct.city
,ct.province
from case_asset a
,v_city ct
where a.tr_type = 'PROTECT'
and ct.city_id(+) = a.city_id
and a.case_id = :CASE_ID
and a.asset_cmd_runno = :RUN_NO
order by a.line_no

I tried using MasterField and DetailField but not success. If I turn on Option.LocalMasterDetail:= True it work fine.

I cannot use Option.LocalMasterDetail as the data is a bit huge and took very long time to return.

Do you have any help on this issue. Why using MasterField and DetailField is not solve my problem?

Thank You and Regard.

TS

P.S. I just test MasterField and DetailField on local client without Option.LocalMasterDetail, it's work fine. But not with NestedDataSet with ClientDataSet.

[AlexP]

hello,

Please send a small sample demonstrating the problem, including the scripts for creating and filling the DB objects to alexp*devart*com, or explain the problem in more details, as we don't fully understand the matter.

[Theprasit]

Just send a sample project "Test MD-UniDAC.zip". Please review. I used UniDAC rather than ODAC because of my client condition but both UniDAC and ODAC gave the same result.

As my client project is nearly dead-line of submission, I have a work-around solution of changing SQL statement using alias in master SQL. This is not I expected because I had about 200 SQL which to be changed. Please give me an advice.

Thank You and Regards.

TS

[AlexP]

Hello,

When using a master-detail relation, you don't need to specify in the query body the WHERE operator with parameters for connecting these tables. When setting the MasterFields and DetailFields properties, a query with parameters is generated automatically.

[Theprasit]

Yes, I do knew about that as per reading from ODAC document. Are you go thru my sample project sent to you. You can try both of the 2 case you mention in my sample project. In case two, when you click on the button "Use MasterField/DetailField", I changed the query statement and the detail query data is return fine.

The problem is that, when you append the detail record by using DBNavigator, The master query automatic filled "DEPT_NO" column of the detail query (grid) rather than "HEAD_DEPT" as expected. You can notice that I set MasterField:= DEPT_NO and DetailField:= HEAD_DEPT

This setting is normal use and work fine when using dbExpress.

Could you please verify my sample project and give me another advise. This is very serious for my client project, I cannot do further with this obstacle.

Very Much Thank You

[Theprasit]

For more information, the query setting as per in my first post is working with ODAC 5.1 without using MasterField and DetailField. Why is not working with the latest version?

Does it concern about TDataSetProvider and TClientDataSet on Delphi XE?

Very Much Thank You

[AlexP]

hello,

Thank you for the information.
We have reproduced the problem.
We will notify you as soon as we have any results.

[Theprasit]

You just release new version for ODAC. Do this solve my issue?

[AlexP]

hello,

We are still working on this problem.
We will notify you as soon as we have any results.

[Theprasit]

Hi!

Just to recall on this issue.

I went thru history log of your product till the latest one, but didn't some mentioned about this issue.

Did this issue be resolve?

Thank you & Regards

[AlexP]

Hello,

We have investigated this behaviour more deeply, and it is correct. Since if you use a parameter in a query, then data is inserted to a field with the same name as the parameter has. Moreover, if localMasterDetail is not used, then a separate query to the detailed table is run for each record in the master DataSet, that significantly decreases performance. Therefore, if using ClientDataSet and Master-Detail relationship, you should enable the localMasterDetail option.