Editing a join query change in recent ODAC?

[MarkF]

It looks like there has been a change in the way that the editing SQL is generated. Previously doing a query like:

Code: Select all

select empno, dname
from emp e, dept d
where
  e.deptno = d.deptno;

Would correctly figure out that empno is editable and that dname is read only (as it's in the dept table which is not the "updatetable".) Trying to lock or edit this query fails as it puts dname into the lock, refresh and update queries as if it belonged to emp.

Interestingly enough, qualifying the columns with their aliases (i.e. e.empno, d.dname) fixes the issue, as well as setting "FieldsOrigin" to true. However neither should be required in this case (and wasn't required in previous ODAC versions.)

Any thoughts?

-Mark Ford

[AlexP]

Hello,

We couldn't reproduce the described problem. When calling the Lock method, the following query is called:

Code: Select all

SELECT EMPNO FROM EMP
WHERE
  EMPNO = :Old_EMPNO
FOR UPDATE NOWAIT


:Old_EMPNO(INTEGER,IN)=XXX

which locks the record only in the EMP table, and it is executed with no errors. Please specify your ODAC version and send a small sample demonstrating the problem to alexp*devart*com демонстрирующий проблему.

P.S. When automatically generating DML queries, if 2 or more tables are used in the main query, the first table specified in the query will be used.

[MarkF]

Thanks for checking. I apologize as I left out that you should set ExtendedFieldsInfo to false to see the problem. I'm using version 9.0, it appears to have changed fairly recently.

[AlexP]

Hello,

This is correct behaviour, since for defining the table, which fields belong to, we need to retrieve information about table fields. To solve the problem, you can set this option to True, or use TSmartQuery (the option is set to True by default), or explicitly specify the table aliases in the field names participating in the query.

Code: Select all

select e.empno, d.dname
from emp e, dept d
where
  e.deptno = d.deptno