Table Function : Fixing the Java class while the database session is still on does not get reflected.......

[Mamta Satoor <ms...@gmail.com>]

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

Re: Table Function : Fixing the Java class while the database session is still on does not get reflected.......

[Rick Hillegas <Ri...@Sun.COM>]

Hi Mamta,

I see the same behavior with scalar functions. I ran the following 
experiment:

1) I compiled a scalar function which returned the constant 0.

2) I brought up the network server.

3) I invoked an ij script which ran the function, retrieving the value 0 
as expected.

4) I recompiled the function after changing it to return the constant -1 
instead.

4) I reran the ij script. The function still returned the old 0 value.

I believe that the first time the function is run, its current class 
definition gets stuck in Derby. This is probably surprising to users. I 
think there are 2 workarounds:

1) Bounce the server as you did.

2) Put the function into a jar file and load the jar file into the 
database. You can then transactionally replace the jar file with a new 
version which contains your changed function. Your client should then 
pick up the changed version.

Regards,
-Rick

Mamta Satoor wrote:
> 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
>