I'm trying to write a restricted table function that retrieves data
from a different database and needing some clarification on how the
"narrowing" of the data is supposed to work. The docs mentions that
initScan() is called like this:
initScan( new String[] { "ID", null, null, "FIRSTNAME", "LASTNAME" } ...)
e.g. that null values are specified for the unneeded columns.
Then the docs continues:
For each row, Derby calls:
MyVTIClass.getInt( 1 ) to get the id column.
MyVTIClass.getString( 4 ) to get the firstName column.
MyVTIClass.getString( 5 ) to get the lastName column.
e.g. Derby expects the ResultSet to be as wide as the full
un-restricted ResultSet
How is this supposed to work? Are columns 2 and 3 supposed to be
supplied, but have null values so that they do not have to be
retreived from the second database.
e.g. something like
select id, null, null, firstname, lastname from employee
(though that syntax is not legal).
Any advice gratefully received.
Thanks
Tim
Hi Tim,
Your understanding of how RestrictedVTIs work is correct. One way to
solve the problem is to write a ResultSet which supplies null values for
the unused columns. Another way to solve the problem is to write a
ResultSet which internally selects a smaller row and then remaps column
ids, taking advantage of the fact that Derby will never ask for the
unused columns.
You might want to take a look at ForeignTableVTI, attached to
https://issues.apache.org/jira/browse/DERBY-4962. This table function
does exactly what you want. However, instead of cooking up
select id, cast( null as int), cast( null as int), firstname,
lastname from employee
the table function cooks up
select id, firstname, lastname from employee
The magic is in the makeQuery() and mapColumnNumber() methods.
You may want to subset ForeignTableVTI because it provides some related
database procedures which you don't need: procedures to register and
deregister vtis against every table in your foreign schema.
Let me know if this is still muddled. I think what you need to do sounds
fairly straightforward.
Thanks,
-Rick
