db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Table Function : Fixing the Java class while the database session is still on does not get reflected.......
Date Thu, 24 Apr 2008 18:18:29 GMT
Hello,

What I am experiencing might be an expected behavior and if not, the
issue might be how user functions are implemented in Derby (ie both
scalar and table functions) and not specific to table functions.

I have defined a table function as follows
CREATE FUNCTION testFunctionTable ()
RETURNS TABLE
(
	tableId INT
)
LANGUAGE JAVA PARAMETER STYLE DERBY_JDBC_RESULT_SET
READS SQL DATA
EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.DERBY_716_BuddyTesting.read';

As can be seen from the definition above, the return table is expected
to have an int column.

The implementation of DERBY_716_BuddyTesting.read looks as follows
    public  static  ResultSet   read()
    throws SQLException
    {
        Connection conn = null;
        Statement stm = null;
        conn = DriverManager.getConnection("jdbc:default:connection");
        stm = conn.createStatement();
        //The table function is defined to return a ResultSet with int
column but we are
        //returning a string which can't be converted to int datatype
        ResultSet rs = stm.executeQuery("values current_user");
        return rs;
    }

The method above returns a ResultSet with a string value which can't
be converted to int and hence when I try to use the table function in
a select statement, I get following behavior
select * from table(testFunctionTable()) a1;
ERROR 22018: Invalid character string format for type int.
java.sql.SQLDataException: Invalid character string format for type int.

I went ahead and fixed the method to return a int value as follows and
then recompiled it
    public  static  ResultSet   read()
    throws SQLException
    {
        Connection conn = null;
        Statement stm = null;
        conn = DriverManager.getConnection("jdbc:default:connection");
        stm = conn.createStatement();
        ResultSet rs = stm.executeQuery("values 1");
        return rs;
    }

I thought I would see the fix the next time in the same database
session if I execute the same select again. But that didn't work.
Next, I dropped the function and recreated it and then issued the
select statement but it still gave the same exception. In order to
pick up the changes made to DERBY_716_BuddyTesting.read, I had to
reboot the database. Is this expected behavior?

thanks,
Mamta

Mime
View raw message