Frank Griffin wrote:
I've been looking into Table Functions as a way to access external data,
and I'm running into some design questions.
Briefly, I need to provide Java code which provides access to arbitrary
tables in a foreign database.
The problem I'm seeing from the docs is that it looks like Table
Functions have to be specific to the ResultSet being produced, i.e. you
have to write a specific Table Function class for each ResultSet. This
makes it difficult to write a single class which can handle multiple
virtual tables.
My intention was to provide configuration file(s) keyed on user-supplied
names so that a single Table Function class, given the name, could
initiate the foreign query associated with that name. The same files
would be used by my code which wraps Derby to inject CREATE FUNCTION
statements into Derby before allowing user access, so that the users who
coded the configuration files can refer to foreign tables by the same
names used in the configuration files.
Is there a way to do this that I'm not seeing ?
If not, would it be possible to add an optional keyword to CREATE
FUNCTION which could be passed to the Table Function to allow it to
customize its processing ? For compatibility, this could require that
the Table Function implement a new interface which provides a method to
be called to set the keyword value (much as is done for the Optimizer
logic).
Hi Frank,
I don't think you need a separate class for each foreign table. I don't
think you even need a separate method for each foreign table. At the end
of this message, I'm including some sample code which may help move this
discussion forward. The code has the following features:
1) There is one user-written class with one user-written method.
2) Each foreign query is backed by two pieces of Derby DDL:
a) A table function which declares the shape of the foreign ResultSet
b) A view which supplies the connection url and query string
This may help you. If not but if you're happy with creating a single
method per foreign query, then you may be able to use something like the
QueryRow annotation which is supplied as part of the vti demo. You can
see how to use this annotation by looking at the QueryRow vti declared
in org.apache.derbyDemo.vtis.example.VTIs The nice bit about QueryRow is
that it comes with machinery which performs the chore in step (2a).
Even if QueryRow isn't the right approach for you, you may find that you
want to clone this machinery.
Please let me know if these solutions don't address your issue. That
will help me understand your problem better.
Thanks,
-Rick
---------------------
Here is the sample code. First, there is a simple class and method which
return query results from the foreign data source:
import java.sql.*;
public class ForeignQueryVTI
{
public static ResultSet foreignQuery( String connectionURL,
String query )
throws SQLException
{
Connection conn = DriverManager.getConnection(
connectionURL );
PreparedStatement ps = conn.prepareStatement( query );
return ps.executeQuery();
}
}
Then there is a Derby script which creates views against the foreign
data. For simplicity's sake, the foreign database in this example is
just Derby--but by changing the connectionURL and queryText arguments,
this will work against MySQL or Postgres or DB2 or Oracle...:
connect 'jdbc:derby:testDatabase;create=true';
drop view foreignTable1;
drop view foreignTable2;
drop function f1;
drop function f2;
create function f1( connectionURL varchar( 32672 ), queryText varchar(
32672 ) )
returns TABLE
(
tableID char( 36 ),
tableName varchar( 128 )
)
language java
parameter style DERBY_JDBC_RESULT_SET
reads sql data
external name 'ForeignQueryVTI.foreignQuery'
;
create view foreignTable1 as
select *
from table( f1( 'jdbc:default:connection', 'select tableID, tableName
from sys.systables' ) ) s
;
create function f2( connectionURL varchar( 32672 ), queryText varchar(
32672 ) )
returns TABLE
(
columnName varchar( 128),
columnNumber int
)
language java
parameter style DERBY_JDBC_RESULT_SET
reads sql data
external name 'ForeignQueryVTI.foreignQuery'
;
create view foreignTable2 as
select *
from table( f2( 'jdbc:default:connection', 'select columnName,
columnNumber from sys.syscolumns' ) ) s
;
select * from foreignTable1;
select * from foreignTable2;
