db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Restricted table function that narrows data
Date Wed, 11 May 2011 16:47:01 GMT
> 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.


View raw message