Today I was looking into an issue with view links to help out my
buddy Clemens with a problem he was facing at a customer site on Friday.
As I was looking into it, I had the strange feeling that I'd looked
into this issue before. However, I recalled not being able to isolate
the root of the problem the previous time I investigated similar
symptoms a few months back for a Dutch software house building a large
application with BC4J. This time I got to the bottom of it.
The symptoms were as follows (stated in terms of Dept and Emp to keep things easier to follow):
- In the application, a primary key column in their DEPT table had a name with an underscore like DEPT_NO.
- While trying to create a view link between DeptView and EmpView,
on the "View Link SQL" panel of the view link editor, the "Where"
clause was showing up as:
:1 = DEPTNO
when the developer was expecting to see DEPT_NO with an underscore.
- Because the developer noticed that "BC4J had made an error" in
putting the name of the underlying DEPT_NO column, he corrected it by
hand to read:
:1 = DEPT_NO
otherwise it was generating a runtime SQL error because the view-link
WHERE clause without the underscore in the DEPT_NO column name was not
matching the name of the column in the table.
The corollary of having to hand-edit the view link WHERE clause was
that BC4J considers that view-link to be "expert-mode" in the sense
that, by design, the developer can introduce an arbitrarily complicated
SQL where clause fragment there to get the view link to be exactly what
is needed by the application.
When a view-link contains a developer-modified Where clause, then there are at least two other side-effects:
- View link consistency is automatically disabled on the target (detail) view object at runtime,
because to enforce it properly would require that BC4J have the ability
to evaluate in-memory an arbitrarily-complex SQL where clause fragment
to decide whether or not a new detail row would qualify for being in
that detail rowset or not.
- The view link cannot be bidirectional at runtime, meaning
that while a view link accessor is exposed for a parent (DeptView) row
to access its collection of detail (EmpView) rows, the reverse accessor
is not available for traversing from an EmpView row back to a
collection of (usually one) parent rows.
Debugging the problem, I discovered that the "DEPTNO" column
name (without the underscore) was coming from the "Alias" property of
the "Deptno" view object attribute on the detail "EmpView", which was
an expert-mode view object. That is, the developer had overridden the
default BC4J-generated SELECT statement with a manually-edited one.
This is of course a totally supported operation, but it also was at the
root of the problem as I explain next.
Looking at the EmpView in the view object editor, it indeed had a
view attribute SQL alias expression of "DEPTNO" instead of "DEPT_NO". I
also noticed that it's expert-mode SQL query looked like:
select emp.ename,
emp.empno,
:
etc.
:
emp.dept_no
from emp
So, a runtime error would happen if the developer did not hand-edit
the view link's WHERE clause SQL, because the detail query would end up
looking like this:
select * from (
select emp.ename,
emp.empno,
:
etc.
:
emp.dept_no
from emp) QRSLT WHERE :1 = DEPTNO
and the DEPTNO column alias used in the WHERE clause would not match
with the expert-mode query's DEPT_NO expression in the select list.
The solution is to make the EmpView's "Deptno" view attribute SQL
alias match the name of the corresponding column/expression selected in
the expert-mode query. That means either adding an "AS DEPTNO" column
alias in the expert-mode query to make it match, or editing the
"DEPTNO" alias to read "DEPT_NO" instead.
After doing this, you might need to reset your view link component
so that it goes back to being a non-user-customized one. You can verify
that BC4J considers your viewlink to be an "expert mode" (i.e.
user-customized SQL) view link by looking at the XML file related to
that view link and noticing whether the element has an attribute named Where whose
value is your user-customized where clause. To reset the view object,
you can visit the "Source" panel and remove all of the attributes
there. Then, shuttle the name of the association on which the view link
will be based over into the selected list. Now, you should be back in
business with a fully-functional, bidirectional,
view-link-consistent-supported view link again.
2:45:31 PM 
|
