db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "sin(EaTing)," <uss...@gmail.com>
Subject Re: How to return an array of String in a Java routine?
Date Sun, 01 Jun 2008 07:37:54 GMT

And one of the tricky point I found in the example is if a ResultSet is
returned by a stored procedure, the Statement which generates the ResultSet
cannot be closed in the procedure, or the ResultSet won't be returned
So in the example ps1 and ps2 is not closed. Quite interesting, should they
be garbage collected properly or whether they will cause some transaction
problem if they are not closed?

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();

  PreparedStatement ps2 = conn.prepareStatement("select * from t1
where i >= ?");
  ps2.setInt(1, p2);
  data2[0] = ps2.executeQuery();


On Sun, Jun 1, 2008 at 10:25 AM, sin(EaTing), <ussraf@gmail.com> wrote:

> Thanks Knut and Johnny!
> These information really help!
> On Sat, May 31, 2008 at 8:29 PM, Knut Anders Hatlen <Knut.Hatlen@sun.com>
> wrote:
>> sin(EaTing), wrote:
>>> Hi,
>>> I want to return an array of Strings from a Java routine. How could I
>>> create the procedure declaration and definition.
>> Hi,
>> Derby doesn't support returning arrays from functions or stored
>> procedures, I'm afraid.
>>  Or if possible could I create my own ResultSet in my Java routine, and
>>> manually insert rows into that ResultSet?
>> Yes, that's possible. You can find an example on the wiki (
>> http://wiki.apache.org/db-derby/DerbySQLroutines) that shows how to
>> create a Java routine which returns a ResultSet. In the routine, you can
>> return your array of strings as a ResultSet by executing a VALUES statement.
>> To return the array {"one","two","three"}, you would execute VALUES 'one',
>> 'two', 'three'.
>> If more flexibility is needed, you can take a look at the table functions
>> that were added in Derby 10.4. They allow you to use your own implementation
>> of the java.sql.ResultSet interface. More details can be found here:
>> http://db.apache.org/derby/docs/10.4/devguide/cdevspecialtabfuncs.html
>> http://db.apache.org/derby/docs/10.4/ref/rrefcreatefunctionstatement.html
>> --
>> Knut Anders

View raw message