db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Using Derby-style table functions
Date Wed, 22 Apr 2009 08:09:13 GMT
Tim Dudgeon <tdudgeon@informaticsmatters.com> writes:

> I'm looking into using Derby-style table functions and they look like
> they might be very useful to me.
> But I have one initial question.
> It looks like each external table accessed through JDBC has to have
> its own Java class written and loaded into Java, which means this its
> not going to be really possible to define these dynamically during
> runtime in a normal application.
>
> Are there any ways of doing this at runtime? e.g. I imagine a GUI that
> lets user select a table from a foreign JDBC connection and add it to
> Derby.

Perhaps writing a table function that takes parameters would
work. Something like

public class ForeignTable {
  public static ResultSet read(String driver, String url, String sql)
    throws Exception
  {
    Class.forName(driver);
    return DriverManager.getConnection(url).createStatement().executeQuery(sql);
  }
}

You would have to define different table functions for tables with
different number of columns and different data types. For instance like
this if the foreign table just has an int column:

CREATE FUNCTION FOREIGN_TABLE_INT(
    DRIVER VARCHAR(1024),
    URL VARCHAR(1024),
    SQL_TEXT VARCHAR(1024))
  RETURNS TABLE (C1 INT)
  LANGUAGE JAVA PARAMETER STYLE DERBY_JDBC_RESULT_SET
  NO SQL EXTERNAL NAME 'ForeignTable.read'

Or like this if it has one int column and one date column:

CREATE FUNCTION FOREIGN_TABLE_INT_DATE(
    DRIVER VARCHAR(1024),
    URL VARCHAR(1024),
    SQL_TEXT VARCHAR(1024))
  RETURNS TABLE (C1 INT, C2 DATE)
  LANGUAGE JAVA PARAMETER STYLE DERBY_JDBC_RESULT_SET
  NO SQL EXTERNAL NAME 'ForeignTable.read'

Only a single class should be needed if the table functions are used
this way, and it should be possible to choose tables dynamically:

SELECT *
  FROM
      TABLE(FOREIGN_TABLE_INT(
            'com.mysql.jdbc.Driver',
            'jdbc:mysql://server1/db',
            'select id from my_table')) T1(X),
      TABLE(FOREIGN_TABLE_INT_DATE(
            'org.postgresql.Driver',
            'jdbc:postgresql://server2/db',
            'select id,date from my_other_table')) T2(X,Y)
  WHERE
      T1.X = T2.X

-- 
Knut Anders

Mime
View raw message