Editing and public synonyms

[MarkF]

I have a situation where a table exists in another schema and there is a public synonym that points to it. It appears that in this case ODAC (latest version under Delphi 2010) doesn't pick up the primary key of the table and so doesn't use it in any of the generated locking or refreshing statements.

Code: Select all

create table ddl.testtable (id number(38) primary key, vc varchar2(100));
insert into ddl.testtable values (1, 'ODAC');
commit;
create public synonym ddltesttable for ddl.testtable;
select * from ddltesttable;  <-- does not see primary key
select * from ddl.testtable;  <-- does see primary key

If you select using the public synonym then the locking statement used when updating the record will be:

Code: Select all

SELECT * FROM "DDLTESTTABLE"
WHERE
  "ID" = :"Old_ID" AND "VC" = :"Old_VC"
FOR UPDATE NOWAIT

If you select from the table directly the locking statement will be aware of the primary key:

Code: Select all

SELECT * FROM "DDL"."TESTTABLE"
WHERE
  "ID" = :"Old_ID"
FOR UPDATE NOWAIT

Thanks for any comments.

-Mark

[MarkF]

Actually it appears that ODAC won't use primary key information for any synonyms (not just public ones as I was first thinking). It seems like it should be synonym aware?

-Mark

[bork]

Hello

If you don't set the KeyFields property then ODAC attempts to determine key fields on it's own. But sometimes it is impossible (for public synonyms, for complex SQL query, etc.). If you want to be sure that ODAC uses primary key for updates and for other WHERE causes then you should define the KeyFields property.

[MarkF]

I can understand that sometimes it is impossible to get the key fields, but for synonyms? Shouldn't TSmartQuery be able to resolve a simple synonym (even a public one?) It seems like a duplication of effort to have me do it. If it can't handle synonyms, could I resolve it myself and simply change the UpdatingTable to the actual table? I tried doing that (setting it to DDL.TESTTABLE as per my example script) but it complained that the updating table didn't exist (it seems like that feature is just to set the value to a different table in the "from" list?)

[bork]

Hello

Now ODAC cannot resolve synonyms and detect primary key for the UPDATE clause. So for synonyms you should set the KeyFields property manually.

We will investigate the possibility of adding this feature in one of the next builds/versions of ODAC.

[MarkF]

Thanks for considering adding that. It's not an urgent request as I have gone ahead and fixed the issue on my end (for both synonyms and dblinks which it appears not to handle properly as well.)

-Mark