KeyFields with joined tables

[s_grosskr]

Dear ODAC-Support Team,

I'm updating a legacy application from an older Delphi Version to Delphi XE2 (update 4) and
I wanted to "switch" to ODAC as my general data access layer, cause we use ODAC in
another project and are very content with it.
Up to now I used another data access layer (DOA).

I have to sketch the main architecture, so you can understand where the problem lies:
I use MIDAS for data access. The main data module has an TLocalConnection with
an OracleSession, which exports providers, which in turn serve the ClientDatasets
in the main program.

The schema of the database consists of dozens of tables, that for historical
reasons have a lot of compound primary keyfields.

The first problem I had with saving data. Saving data needs keyfields, and you
decided to "switch off" loading keyfields ( only for oracle ??):
TCustomOraDataSetService.PreventPSKeyFields returns True, so no Keyfields are loaded.
I solved this problem by creating a new class for my Providerdatasets (TblvOdacProviderQuery),
which overrides GetDataSetServiceClass, returning a class that returns False for
PreventPSKeyFields (TOdacProviderDataSetService).
In the ODAC support forum I found the statement that you made this decision to
prevent performance issues with MIDAS.
But what is the alternative? Set the KeyFields for every dataset that will be
saved manually? Maybe you can shed some light on this or give me a hint,
if there is a better approach.

Now I was able to save data, but another problem arises:
In our application we use clientdatasets that are filled e.g. for preview reasons,
with aggregated data from other sources. So we make a complex select on some joined
tables and insert data. A procedure, that worked very well with the old data access
layer now fails with a "key violation" error.

It took me some days to dive into this and track down to the root of the problem:
When 2 or more tables are joined, then the keyfields are not computed correctly, when Fields of the updating table are missing. The problem is: PSGetKeyFields doesn't return "" (Empty), as the old data layer does, but it returns only one (arbitrary?) field.
I tried to make s sample scenario for you to illustrate the problem:
say you have a Table TABLE_A and a Table TABLE_A_SUB.
TABLE_A_SUB references TABLE_A (foreign key, see also the enclosed SQL-scripts).

In my query I joind table TABLE_A and TABLE_SUB_A, TABLE_SUB_A is my first (updating) table (note, that there will never be a "real" update, this is only a preview datasource).
TABLE_SUB_A has a compound primary key existing of 3 fields.
When all of these fields are present in the query, everythings is ok (seemingly),
but if one of the 3 fields is missing, only 1 of the left fields is marked
as a keyfield. As mentioned above, the old data access layer returned an empty string in this case, so no "key violation" error occured.

Question: Can you confirm that this is a bug? What can I do?
Any chance to return
a) empty as PSKeyFields in this case
b) at least the 2 remaining fields, which would also help in this case

I'm afraid I otherwise had to go throuh all the thousand lines of my code and fix
all those spots, where these preview datasources are prepared.

Is there maybe a possibility to "switch off" the calculaction of KeyFields for
each provider-Dataset?
What about perfomance and memory, is there an instance of TDataSetServiceClass
for every dataset in my program or are they "shared"?
Maybe I could work with 2 descendants of TSmartQuery, returning different
descendants of TSmartDataSetService, one with, the other without keyfields support.

Enclosed you find a little sample program in form of a DUnit Test-Suite.
To run the example you have to do the following steps:
- run both SQL-scripts on your test database (first 01_create_ABC.sql, then 02_create_A_Sub.sql )
- change the login credentials in KeyFieldTest.pas
- compile / run

Sorry for the lots of text, but this affects the basic data access layer of my program
and I need to be sure, that there are absolutely no errors.

Thanx in advance for your help.

With regards,
Stefan "ostijo" Grosskreutz

Btw: I work with Delphi XE2 Update 4 Hotfix 1 and ODAC 8.6.12
P.S. I just noticed there is no possibility to attach a sample project, or am I missing something? Can I send it per E-Mail to you?

[s_grosskr]

Sorry, It seems I cannot attach my example. Please contact me, I can send you the sample per E-Mail.

[AlexP]

Hello,

You can send your example to alexp*devart*com.