Hi Tim,
If you are running the table function from a jar file stored in your
database, you may have run into the following bug:
http://issues.apache.org/jira/browse/DERBY-4126 This bug is fixed in the
current 10.5 release candidate which we are testing.
Hope this helps,
-Rick
Tim Dudgeon wrote:
Hi thanks for that advice.
I've given it a try, but am getting a ClassNotFound exception that I
can't explain. I added a simple method that I set up a s a functiona
dn that works fine, but when I try to set it up as a table function,
even with a null implementation it fails.
I have a class that looks like this
package org.apache.derbyDemo.vti;
import java.sql.*;
public class ForeignTable {
public static ResultSet readNull() throws SQLException {
throw new SQLException("testing");
}
private static int called;
public static int called() {
return called++;
}
}
Then in ij I do this (jar file has already been loaded).
ij version 10.4
ij>
ij> connect 'jdbc:derby:C:\Documents and Settings\timbo\My
Documents\IJCProjects\ijc-project-etl\.config\localdb\db';
ij> create function called ()
returns int
language java
parameter style java
no sql
> > > > > external name 'org.apache.derbyDemo.vti.ForeignTable.called';
0 rows inserted/updated/deleted
ij> values called();
1
-----------
0
1 row selected
ij> values called();
1
-----------
1
1 row selected
ij> CREATE FUNCTION F_TAB ()
> RETURNS TABLE (foo INT)
> LANGUAGE JAVA PARAMETER STYLE DERBY_JDBC_RESULT_SET
> NO SQL EXTERNAL NAME 'org.apache.derbyDemo.vti.ForeignTable.readNull';
0 rows inserted/updated/deleted
ij> SELECT w.* FROM TABLE ( F_TAB()) w;
ERROR 38000: The exception 'java.lang.ClassNotFoundException:
org.apache.derbyDemo.vti.ForeignTable' was thrown while evaluating an
expression.
ERROR XJ001: Java exception: 'org.apache.derbyDemo.vti.ForeignTable:
java.lang.ClassNotFoundException'.
ij>
Any ideas what I'm doing wrong?
Thanks
Tim
Knut Anders Hatlen wrote:
Tim Dudgeon <[email protected]> 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
