db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Table Function Question
Date Mon, 07 Jan 2008 16:25:32 GMT
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.



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;

View raw message