Hi,

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

conn.close();
}


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