Re: How to return an array of String in a Java routine?

["sin(EaTing)," <us...@gmail.com>]

Thanks Knut and Johnny!
These information really help!


On Sat, May 31, 2008 at 8:29 PM, Knut Anders Hatlen <Kn...@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
>

Re: How to return an array of String in a Java routine?

["sin(EaTing)," <us...@gmail.com>]

That's really, really great.

On Sun, Jun 1, 2008 at 4:24 PM, Knut Anders Hatlen <Kn...@sun.com>
wrote:

> sin(EaTing), wrote:
>
>> 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?
>>
>
> Hi,
>
> The result sets that are created inside a stored procedure are marked with
> a flag internally, and when such a result set is closed it will also close
> the statement that created it. So there shouldn't be any resource leak by
> using this approach.
>
> --
> Knut Anders
>

Re: How to return an array of String in a Java routine?

[Knut Anders Hatlen <Kn...@Sun.COM>]

sin(EaTing), wrote:
> 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?

Hi,

The result sets that are created inside a stored procedure are marked 
with a flag internally, and when such a result set is closed it will 
also close the statement that created it. So there shouldn't be any 
resource leak by using this approach.

-- 
Knut Anders

Re: How to return an array of String in a Java routine?

["sin(EaTing)," <us...@gmail.com>]

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), <us...@gmail.com> wrote:

> Thanks Knut and Johnny!
> These information really help!
>
>
>
> On Sat, May 31, 2008 at 8:29 PM, Knut Anders Hatlen <Kn...@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
>>
>
>