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.
