db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Cursor as out parameter in stored function
Date Wed, 07 Aug 2013 18:30:44 GMT
On 8/7/13 6:37 AM, Vargan wrote:
> Hi folks,
>
> I'm using Derby as in-memory database for unit-testing.
> There's a way to define a stored procedure as a CURSOR as out parameter? if
> yes, how? I tried in many ways, but I got always exceptions.
>
> Thanks!
Hi Samuele,

If what you're trying to do is return a ResultSet from a procedure, the 
following code may help:

First compile the static Java method which implements the procedure:

import java.sql.*;
public class RSReturningProc
{
     /** Procedure which returns a ResultSet */
     public static void resultSetReturningProc( String tableName, 
ResultSet[] returnedResultSets )
         throws SQLException
     {
         Connection  conn = DriverManager.getConnection( 
"jdbc:default:connection" );
         PreparedStatement   ps = conn.prepareStatement
             (
              "select * from sys.sysconglomerates c, sys.systables t\n" +
              "where t.tableid = c.tableid\n" +
              "and t.tablename = ?\n"
              );
         ps.setString( 1, tableName );

         returnedResultSets[ 0 ] = ps.executeQuery();
     }
}

...then declare and invoke the procedure like this:

connect 'jdbc:derby:memory:db;create=true';

create procedure resultSetReturningProc( tableName varchar( 128 ) )
language java parameter style java reads sql data
result sets 1
external name 'RSReturningProc.resultSetReturningProc';

call resultSetReturningProc( 'SYSCOLUMNS' );


Alternatively, you may discover that a table function is a more natural 
approach to the problem you are trying to solve. For more information on 
table functions, please see the section titled "Programming Derby-style 
table functions" in the Derby Developer's Guide: 
http://db.apache.org/derby/docs/10.10/devguide/index.html

Hope this helps,
-Rick
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Cursor-as-out-parameter-in-stored-function-tp133073.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>


Mime
View raw message