You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Elio Bonazzi <eb...@earthlink.net> on 2006/03/24 04:34:26 UTC
Result Set from stored procedure
I would like to call a stored procedure and to be able to navigate through
the result set returned by the stored procedure. I saw the getResultSet()
method in the CallableStatement Derby implementation.... But how do I code
the java stored procedure so that the result set from the select statement
is made accessible to the CallableStatement? In Oracle I would open a
RefCursor and return it, in MySql I do an orphan select, but how do I code
a java stored procedure that returns a result set? I could not find any
example on the net. Please help!
If Derby supports such a feature, I would definitely use it for a major
project for the financial industry.
Thanks in advance
RE: Result Set from stored procedure
Posted by Elio Bonazzi <eb...@earthlink.net>.
Dan,
Thanks very much for your exhaustive answer. I do really appreciate your
prompt feedback. It is now 12:34 AM in New York, where I live, so tomorrow
first thing I will test the example you so kindly provided.
Thanks again
Elio
-----Original Message-----
From: Daniel John Debrunner [mailto:djd@apache.org]
Sent: Friday, March 24, 2006 12:24 AM
To: Derby Discussion
Subject: Re: Result Set from stored procedure
Elio Bonazzi wrote:
> I would like to call a stored procedure and to be able to navigate
> through the result set returned by the stored procedure. I saw the
> getResultSet() method in the CallableStatement Derby implementation....
> But how do I code the java stored procedure so that the result set from
> the select statement is made accessible to the CallableStatement? In
> Oracle I would open a RefCursor and return it, in MySql I do an orphan
> select, but how do I code a java stored procedure that returns a result
> set? I could not find any example on the net. Please help!
> If Derby supports such a feature, I would definitely use it for a major
> project for the financial industry.
Derby follows the SQL standard part 13 (aka SQL-J part 1) for returning
ResultSets through Java procedures. Any procedures written this way will
work on other database engines such as DB2 and Oracle.
Each ResultSet is returned through a separate argument to the java
method for the procedure that is a ResultSet[] with one element. Here is
a simple example:
SQL create statement
create procedure DRS2(p1 int, p2 int)
parameter style JAVA
language java
READS SQL DATA
dynamic result sets 2
external name
'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'
Body of Java method.
public static void selectRows(int p1, int p2, ResultSet[] data1,
ResultSet[] data2) throws SQLException {
Connection conn =
DriverManager.getConnection("jdbc:default:connection");
PreparedStatement ps1 = conn.prepareStatement("select * from t1 where
i = ?");
ps1.setInt(1, p1);
data1[0] = ps1.executeQuery();
PreparedStatament ps2 = conn.prepareStatement("select * from t1 where
i >= ?");
ps2.setInt(1, p2);
data2[0] = ps2.executeQuery();
conn.close();
}
A couple of things to note:
The ResultSets are returned to the application, through the
CallableStatement, in the order they were created.
The ResultSet must be open and generated from the default connection
(jdbc:default:connection) in order to be returned. Any other ResultSets
will be ignored.
Closing the Statement that created the ResultSet within the procedure's
method will close the ResultSet, so don't do that! However closing the
connection is ok.
The PreparedStatement's or other Statement objects in the method must be
created in the body of the method, do not cache them as static
variables, that will not work. So unlike a JDBC client application a
Java method for a procedure or a function cannot hold onto JDBC objects
after it completes.
The database engine (Derby) creates the one element ResultSet arrays
that hold the return of the ResultSet.
Returning less ResultSets than defined by the dynamic result sets clause
is ok, only fill in the number of arrays you need. So in this example to
only return one ResultSet either just set data1[0] to the ResultSet or
data2[0] to the ResultSet. Remember the order is driven by the order of
creation, not the order of the method parameters.
Hope this helps,
Dan.
Re: Result Set from stored procedure
Posted by Daniel John Debrunner <dj...@apache.org>.
Elio Bonazzi wrote:
> I would like to call a stored procedure and to be able to navigate
> through the result set returned by the stored procedure. I saw the
> getResultSet() method in the CallableStatement Derby implementation....
> But how do I code the java stored procedure so that the result set from
> the select statement is made accessible to the CallableStatement? In
> Oracle I would open a RefCursor and return it, in MySql I do an orphan
> select, but how do I code a java stored procedure that returns a result
> set? I could not find any example on the net. Please help!
> If Derby supports such a feature, I would definitely use it for a major
> project for the financial industry.
Derby follows the SQL standard part 13 (aka SQL-J part 1) for returning
ResultSets through Java procedures. Any procedures written this way will
work on other database engines such as DB2 and Oracle.
Each ResultSet is returned through a separate argument to the java
method for the procedure that is a ResultSet[] with one element. Here is
a simple example:
SQL create statement
create procedure DRS2(p1 int, p2 int)
parameter style JAVA
language java
READS SQL DATA
dynamic result sets 2
external name
'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'
Body of Java method.
public static void selectRows(int p1, int p2, ResultSet[] data1,
ResultSet[] data2) throws SQLException {
Connection conn =
DriverManager.getConnection("jdbc:default:connection");
PreparedStatement ps1 = conn.prepareStatement("select * from t1 where
i = ?");
ps1.setInt(1, p1);
data1[0] = ps1.executeQuery();
PreparedStatament ps2 = conn.prepareStatement("select * from t1 where
i >= ?");
ps2.setInt(1, p2);
data2[0] = ps2.executeQuery();
conn.close();
}
A couple of things to note:
The ResultSets are returned to the application, through the
CallableStatement, in the order they were created.
The ResultSet must be open and generated from the default connection
(jdbc:default:connection) in order to be returned. Any other ResultSets
will be ignored.
Closing the Statement that created the ResultSet within the procedure's
method will close the ResultSet, so don't do that! However closing the
connection is ok.
The PreparedStatement's or other Statement objects in the method must be
created in the body of the method, do not cache them as static
variables, that will not work. So unlike a JDBC client application a
Java method for a procedure or a function cannot hold onto JDBC objects
after it completes.
The database engine (Derby) creates the one element ResultSet arrays
that hold the return of the ResultSet.
Returning less ResultSets than defined by the dynamic result sets clause
is ok, only fill in the number of arrays you need. So in this example to
only return one ResultSet either just set data1[0] to the ResultSet or
data2[0] to the ResultSet. Remember the order is driven by the order of
creation, not the order of the method parameters.
Hope this helps,
Dan.