Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of msatoor@gmail.com designates
 209.85.198.246 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
        b=Ekkl9NEt/5eYKmIgiid4fkvuKRePffdlEt1vlYerAAcFo6QERrIg9WgdGhjIb7BMLo3j7ErciG+ysAJYWYlPwkXC1Rbq5hZ+1oo6CN8qJvTqfKLr1HhkQynopidscAYxbjKf1E/ylMMOtDBLJk/zLRDpkScZs/ya+A0EzhFFYdI=
Message-ID: <d9619e4a0804241118p3da8aae6x17123095787cad09@mail.gmail.com>
Date: Thu, 24 Apr 2008 11:18:29 -0700
From: "Mamta Satoor" <msatoor@gmail.com>
To: "Derby Development" <derby-dev@db.apache.org>
Subject: Table Function : Fixing the Java class while the database session is
 still on does not get reflected.......
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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