db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Using Derby-style table functions
Date Thu, 23 Apr 2009 13:02:28 GMT
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 <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