db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Elio Bonazzi" <ebona...@earthlink.net>
Subject RE: Result Set from stored procedure
Date Fri, 24 Mar 2006 05:35:35 GMT
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.


Mime
View raw message