db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Dudgeon <tdudg...@informaticsmatters.com>
Subject Re: Using Derby-style table functions
Date Wed, 22 Apr 2009 15:59:29 GMT
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 <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
> 


Mime
View raw message