db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Table Function : Fixing the Java class while the database session is still on does not get reflected.......
Date Thu, 24 Apr 2008 22:50:14 GMT
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
>   


Mime
View raw message