Result set as a return value of a Java stored procedure

[Mirek Cervenka <mi...@yahoo.co.uk>]

Hello,

I have a following problem. I want to return a result set as a consequence of some processing by a Java stored procedure on Apache Derby DB, however, I am still getting a NULL resultset. Could anyone help me please? I would be very grateful!

Here is the code. Registration of the stored procedure is as follows:

CREATE PROCEDURE myTestProcedure() LANGUAGE JAVA PARAMETER STYLE JAVA CONTAINS SQL DYNAMIC RESULT SETS 1 EXTERNAL NAME 'StoredProcedures.myTestProcedure'

The actual implementation of the stored procedure is:

    public static void myTestProcedure(ResultSet[] rs) {
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
        try {
            Connection c = null;
            try {
                String dbUser = "blabla";
                String dbPassword = "blabla";
                c = DriverManager.getConnection("jdbc:derby://localhost:1527/myTestDatabase", dbUser, dbPassword);
                
                Statement s = c.createStatement();
                rs[0] = s.executeQuery("SELECT * FROM IMAGE");
                System.out.println("rs[0]="+rs[0]);    // here the resultset contains data and is ok.
            } finally {
                //c.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
    } 

The actual call of the stored procedure is:

....
CallableStatement cs3 = c.prepareCall("{CALL myTestProcedure()}");
boolean retVal = cs3.execute());    // returns false;
ResultSet rs = cs3.getResultSet();    //returns null
....

I was moving ahead along some tutorials. Call to procedures that do not return a resultset but return a scalar values (by parameters) works fine. I have problem just with the ResultSet. This thread (http://www-128.ibm.com/developerworks/forums/dw_thread.jsp?message=13731588&cat=19&thread=86198&treeDisplayType=threadmode1&forum=370#13731588) shows basically the same thing, it is equal by its principle but it does not work. Unfortunately, there is no further feedback to the thread

I am using JDK version 1.5.0_09 and Apache Derby 10.2.2.0. 

If anyone would have any advice, please post it!

Many thanks!

Mirek




		
___________________________________________________________ 
All New Yahoo! Mail – Tired of unwanted email come-ons? Let our SpamGuard protect you. http://uk.docs.yahoo.com/nowyoucan.html

Re: Result set as a return value of a Java stored procedure

[Daniel John Debrunner <dj...@apache.org>]

Mirek Cervenka wrote:
> Hello,
> 
> I have a following problem. I want to return a result set as a 
> consequence of some processing by a Java stored procedure on Apache 
> Derby DB, however, I am still getting a NULL resultset. Could anyone 
> help me please? I would be very grateful!
> 
> Here is the code. Registration of the stored procedure is as follows:
> 
> CREATE PROCEDURE myTestProcedure() LANGUAGE JAVA PARAMETER STYLE JAVA 
> CONTAINS SQL DYNAMIC RESULT SETS 1 EXTERNAL NAME 
> 'StoredProcedures.myTestProcedure'
> 
> The actual implementation of the stored procedure is:
> 
>     public static void myTestProcedure(ResultSet[] rs) {
>         try {
>             
> Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
>         } catch (Exception ex) {
>             ex.printStackTrace();
>         }

There's no need to load any JDBC driver in a server side procedure.

You may find this wiki page useful:

http://wiki.apache.org/db-derby/DerbySQLroutines

Dan.

Re: Result set as a return value of a Java stored procedure

[Knut Anders Hatlen <Kn...@Sun.COM>]

Mirek Cervenka <mi...@yahoo.co.uk> writes:

> Hello,
>
> I have a following problem. I want to return a result set as a
> consequence of some processing by a Java stored procedure on Apache
> Derby DB, however, I am still getting a NULL resultset. Could anyone
> help me please? I would be very grateful!
>
> Here is the code. Registration of the stored procedure is as follows:
>
> CREATE PROCEDURE myTestProcedure() LANGUAGE JAVA PARAMETER STYLE
> JAVA CONTAINS SQL DYNAMIC RESULT SETS 1 EXTERNAL NAME
> 'StoredProcedures.myTestProcedure'
>
> The actual implementation of the stored procedure is:
>
>     public static void myTestProcedure(ResultSet[] rs) {
>         try {
>             Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
>         } catch (Exception ex) {
>             ex.printStackTrace();
>         }
>         
>         try {
>             Connection c = null;
>             try {
>                 String dbUser = "blabla";
>                 String dbPassword = "blabla";
>                 c = DriverManager.getConnection("jdbc:derby://localhost:1527/myTestDatabase", dbUser, dbPassword);

Hi Mirek,

A stored procedure should use "jdbc:default:connection" as URL. The
rest of the code looks correct.

-- 
Knut Anders