Query edit and dblinks.

[MarkF]

I have the following query:
select * from emp@mydblink;
emp has a primary key (EMPNO).

If I open this query in a TSmartQuery then CanModify is always false.

Interestingly enough, the following work:
select ROWID, E.* from emp@loopback E; -- CanModify is true.
select E.* from emp@loopback E; -- CanModify is true.

In the past I have just detected this type of query and set the keyfields property and after that CanModify would be true. This no longer seems to be the case. Was there a change recently related to this? Is there a way for me to get this to work? Thanks for any help.

-Mark Ford

[AlexP]

Hello,

We cannot reproduce the problem. Please specify the exact versions of ODAC

[MarkF]

ODAC version 9.0.2. There are really two issues here.

1. A query of select * from table@dblink has CanModify set to false. Are there properties of the TSmartQuery object that affect this?

2. It appears that ODAC doesn't properly detect table key information on tables through db_links. Note that the SmartQuery.GetKeyList function doesn't work with db_links (although it appears that it is not used internally.) This is causing optimistic locking instead of pessimistic (which I get around by setting the KeyFields property with my own code.)

Is it possible to see how you get the key information? I don't any SQL queries related to this listed in DBMONITOR.

-Mark

[MarkF]

I've gone through your source code and I see that the metadata object doesn't handle db_links or synonyms (as far as I can tell.) This means that only optimistic locking will be used for those scenarios. I already work around this in my code by handling both and setting the KeyFields property. The main question is why my TSmartQuery of
select * from emp@dblink; won't allow editing. Any ideas or suggestions?

-Mark

[MarkF]

Are you sure you actually tested the query I gave? I found the answer. If ExtendedFieldsInfo is true (which is required for multi-table editing as per my earlier support request) then any "select * from table@dblink" query will not be editable. It looks like the extendedfieldinfo retrieval does not support db_links as mentioned above.

It's actually even a bit worse, since the data dictionary queries will use the tablename without the db_link, so it's possible to get metadata for the wrong table (maybe that's your issue, you need to make sure you test in a way that won't pick up metadata for the wrong table.)

-Mark

[MarkF]

Here is the answer. You can ignore the above posts.

There's a bug in procedure TCustomOraDataSetService.RequestFieldsInfo such that the current schema will be used as the table owner instead of the USERNAME from the db_link in queries that include a db_link, but not the table owner/schema.

eg:
select * from scott.emp@dblink; is editable.
select * from emp@dblink; is not editable (the dblink username is SCOTT but the query to get the extended fields info uses the current schema name instead.)

-Mark

[AlexP]

Hello,

Yes. If you don't specify the scheme explicitly when using Data Base Link, then the current scheme of the connection will be taken by default, therefore you should explicitly specify the scheme if they are different.
We have fixed the TOraDataSet.GetKeyList method, now it works correctly with Data Base Link

[MarkF]

No, that's incorrect. If a schema is not specified, you need to use the schema specified in the DBLINK, not the current schema. Note that your SYNONYM checking code does this correctly already. This is a bug that needs to be fixed.

-Mark

[AlexP]

Hello,

We have fixed the problem. From now, if a schema is not explicitly specified in the table name, the RequestFieldsInfo method will retrieve the schema name from DataBaseLink

[MarkF]

Excellent. Thanks for taking care of that.

-Mark

[AlexP]

Hello,

If you have any other questions, feel free to contact us.