Missing data / default indexes

[nboodram]

Hello,

I am using DOA in a multi-tier setup: hooking up a TDataSetProvider to the TOracleDataset and passing the data to a TClientDataSet. I have a situation where a particular sql in the TOracleDataset is causing the clientdataset to hide some records. The SQL joins several tables and has an "order by" at the end. If I remove the "order by", the client dataset shows all records as it should. I tend to think it is DOA causing this behavior, because if I replace the TOracleDataset with the BDE's TQuery component, all the records show. I think it has to do with the DEFAULT_ORDER index that is created in the client dataset, but I'm not sure where to go from here. Trying simpler SQLs on the scott schema do not reproduce the problem. Any ideas or suggestions? Although it is possible for us to remove the "order by" from the SQL and create an index manually, we are switching a very large application to DOA and this would rather be avioded.

Thanks,
Natalie

 

[nboodram]

More information: the problem only occurs when there is a primary key on the parent table, the parent table is listed first in the FROM clause of the SQL, and there is an ORDER BY on the parent field. Please note that a sample project has been sent to support@allroundautomations.com that recreates the problem on Oracle's scott schema. You will need to create a primary key on the DEPT table to see the problem--
alter table DEPT add constraint DEPT_PK primary key (DEPTNO)
USING INDEX
STORAGE
(
INITIAL 4K
NEXT 4K
)
TABLESPACE TOOLS
/

Thanks for your attention to this matter.
Natalie

 

[Marco Kalter]

This is how the TOracleDataSet determines the key fields that are reported to the TDataSetProvider:
[*] Use the pfInKey option of the ProviderFlags of the fields to determine the key fields

[*] If they are not set, use the UniqueFields property

[*] If it is not set, use the primary key or unique key constraint of the updating table[/list]In step 3, the updating table is determined by using the first table in the from clause, or by using the UpdatingTable property. In your case, the DEPT table. The duplicate DEPTNO fields are discarded by the TClientDataSet because of this situation.

You can use one of the 3 methods described above to define the correct key fields.

------------------
Marco Kalter
Allround Automations