Re: Uinable to use Table function due to java.lang.ClassNotFoundException while class is clearly reachable..

[Rick Hillegas]

Hi Krzysztof,

Is there a full stack trace in derby.log which you could include? The 
SQL state 38000 appears when Derby wraps an exception originally thrown 
by user-written code. Is it possible that the execution logic of your 
ResultSet references a class which is not present in the jar file you 
loaded?

I have verified your final experiment: Derby lets you invoke a table 
function as a scalar function via the values clause. In this situation, 
Derby just returns the ResultSet produced by the table function. This 
looks odd to me. I don't think that you should be allowed to invoke a 
table function as a scalar function. I will log a bug against this.

Thanks,
-Rick

Krzysztof wrote:
Hello,
I'm trying to get basic table function running without much success.
Either error is misleading or I missed some basic point, could you
please advise?

public class DerbyBlobTableFunction {

 //this works
  public static int returnSth()
    {
        return 1;
    }

//does not work, see error below
 public static ResultSet getTSValuesEmpty()  throws SQLException
    {
         List<double[]> results = new ArrayList<double[]>();
        results.add(new double[]{1.0,2.0,3.0});
         ResultSet rs = new ResultSetTemplate(results);
--//ResultSetTemplate implements ResultSet but same error with Derby
impl of ResultSet
        return rs;
    }

}


then


CALL SQLJ.install_jar
('/pathto/kn.jar', 'APP.kn', 0); -- OK

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY
('derby.database.classpath', 'APP.kn'); --OK


CREATE FUNCTION MYFUNC() returns int PARAMETER STYLE JAVA reads
sql data language JAVA EXTERNAL NAME
''gaia.cu7.dal.DerbyBlobTableFunction.returnSth'; --OK

values MYFUNC() ; --OK
returns:

1


CREATE FUNCTION getTSValuesEmpty()
RETURNS TABLE   
(       
  obs      double,
  val      double,      
  valError double
)
LANGUAGE JAVA
PARAMETER STYLE DERBY_JDBC_RESULT_SET
reads SQL data --  switching to no sql does not change
EXTERNAL NAME ''gaia.cu7.dal.DerbyBlobTableFunction.getTSValuesEmpty'
--OK

then:

select * from table ( getTSValuesEmpty() ) t;

gives:

An error occurred when executing the SQL command:
select * from table ( getTSValuesEmpty() ) t
The exception 'java.lang.ClassNotFoundException:
gaia.cu7.dal.DerbyBlobTableFunction' was thrown while evaluating an
expression. [SQL State=38000, DB Errorcode=20000]
Next: Java exception: 'gaia.cu7.dal.DerbyBlobTableFunction:
java.lang.ClassNotFoundException'. [SQL State=XJ001]

Execution time: 0.04s

1 statement(s) failed.


_but_

values getTSValuesEmpty();

gives:
1
gaia.cu7.dal.resultsettempl...@10fa2e17


MacOS,  Derby 10.4.2.0, embedded mode.


Could you please enlighten me what could be wrong? Seems error message
is plain wrong as 'values' returns the reference to resultset, and
regular function from the same class works as well as values call..

Best regards,
Krzysztof