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 "sin(EaTing)," <us...@gmail.com> on 2008/05/31 05:26:10 UTC
How to return an array of String in a Java routine?
Hi,
I want to return an array of Strings from a Java routine. How could I create
the procedure declaration and definition.
Or if possible could I create my own ResultSet in my Java routine, and
manually insert rows into that ResultSet?
Thanks!
Re: How to return an array of String in a Java routine?
Posted by "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?
Posted by 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?
Posted by "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
>>
>
>
Re: How to return an array of String in a Java routine?
Posted by "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?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
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?
Posted by "sin(EaTing)," <us...@gmail.com>.
Thanks Rick!
On Mon, Jun 2, 2008 at 8:51 PM, Rick Hillegas <Ri...@sun.com>
wrote:
> Hello,
>
> As you note, Derby does not support Array types. If you need to return a
> collection from a SQL routine, the following may help:
>
> 1) Transform the collection into a ResultSet. As Knut points out, you have
> two options here:
>
> a) Write a table function.
> b) Write a ResultSet-returning procedure
>
> 2) Alternatively, you can serialize your String[] into a byte array. This
> solution will work any time you need to use SQL routines to pass arbitrary
> objects. The return type of your function would be VARCHAR FOR BIT DATA or
> LONG VARCHAR FOR BIT DATA. That is, you would declare your function like
> this:
>
> create function MyFunction( ... )
> returns varchar( 32672) for bit data ...
>
> Hope this helps,
> -Rick
>
>
> sin(EaTing), wrote:
>
>> Thanks.
>>
>> I may use String[] in the implement of the Java Routine, but I don't know
>> how to declare the routine. It seems Java Routine in Derby does not support
>> Array types as IN or OUT parameters?
>>
>>
>
Re: How to return an array of String in a Java routine?
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hello,
As you note, Derby does not support Array types. If you need to return a
collection from a SQL routine, the following may help:
1) Transform the collection into a ResultSet. As Knut points out, you
have two options here:
a) Write a table function.
b) Write a ResultSet-returning procedure
2) Alternatively, you can serialize your String[] into a byte array.
This solution will work any time you need to use SQL routines to pass
arbitrary objects. The return type of your function would be VARCHAR FOR
BIT DATA or LONG VARCHAR FOR BIT DATA. That is, you would declare your
function like this:
create function MyFunction( ... )
returns varchar( 32672) for bit data ...
Hope this helps,
-Rick
sin(EaTing), wrote:
> Thanks.
>
> I may use String[] in the implement of the Java Routine, but I don't
> know how to declare the routine. It seems Java Routine in Derby does
> not support Array types as IN or OUT parameters?
>
Re: How to return an array of String in a Java routine?
Posted by Johnny Kewl <jo...@kewlstuff.co.za>.
----- Original Message -----
From: sin(EaTing),
To: Derby Discussion
Sent: Saturday, May 31, 2008 2:10 PM
Subject: Re: How to return an array of String in a Java routine?
Thanks.
I may use String[] in the implement of the Java Routine, but I don't know how to declare the routine. It seems Java Routine in Derby does not support Array types as IN or OUT parameters?
My mistake, I completely misunderstood the question... oops
Knut, thanks very interesting.
Re: How to return an array of String in a Java routine?
Posted by "sin(EaTing)," <us...@gmail.com>.
Thanks.
I may use String[] in the implement of the Java Routine, but I don't know
how to declare the routine. It seems Java Routine in Derby does not support
Array types as IN or OUT parameters?
On Sat, May 31, 2008 at 8:01 PM, Johnny Kewl <jo...@kewlstuff.co.za> wrote:
>
>
> ----- Original Message -----
> *From:* sin(EaTing), <us...@gmail.com>
> *To:* Derby Discussion <de...@db.apache.org>
> *Sent:* Saturday, May 31, 2008 5:26 AM
> *Subject:* How to return an array of String in a Java routine?
>
> Hi,
>
> I want to return an array of Strings from a Java routine. How could I
> create the procedure declaration and definition.
> This is a little vague
> String[] maybe?
> Or mabe you asking how to make a record holder? not sure
>
> Or if possible could I create my own ResultSet in my Java routine, and
> manually insert rows into that ResultSet?
>
> Try a disconnected recordset
> Google for CachedRowSetImpl
> You can create a cached recordset, add remove records without the dB
> and later resync with the dB if you want to.
>
> Good Luck
>
> On this site you may find some code to steal
> ---------------------------------------------------------------------------
> HARBOR : http://www.kewlstuff.co.za/index.htm
> The most powerful application server on earth.
> The only real POJO Application Server.
> See it in Action : http://www.kewlstuff.co.za/cd_tut_swf/whatisejb1.htm
> ---------------------------------------------------------------------------
>
>
Re: How to return an array of String in a Java routine?
Posted by Johnny Kewl <jo...@kewlstuff.co.za>.
----- Original Message -----
From: sin(EaTing),
To: Derby Discussion
Sent: Saturday, May 31, 2008 5:26 AM
Subject: How to return an array of String in a Java routine?
Hi,
I want to return an array of Strings from a Java routine. How could I create the procedure declaration and definition.
This is a little vague
String[] maybe?
Or mabe you asking how to make a record holder? not sure
Or if possible could I create my own ResultSet in my Java routine, and manually insert rows into that ResultSet?
Try a disconnected recordset
Google for CachedRowSetImpl
You can create a cached recordset, add remove records without the dB and later resync with the dB if you want to.
Good Luck
On this site you may find some code to steal
---------------------------------------------------------------------------
HARBOR : http://www.kewlstuff.co.za/index.htm
The most powerful application server on earth.
The only real POJO Application Server.
See it in Action : http://www.kewlstuff.co.za/cd_tut_swf/whatisejb1.htm
---------------------------------------------------------------------------