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 Dheeraj Dhiman <dh...@gmail.com> on 2006/04/13 06:01:50 UTC
Can A Function or procedure return multiple values !!
I m using apache derby for my Project !!
In this project we want to make a function
which accecpt three long argument & i want to return Object[] ( Object Array
) !!
Can this is possible in apache derby if yes then pls give example !!
My Case is :::
create function myprocedure( id1 bigint , id2 bigint , id3 bigint ) returns
Object[] parameter style java reads sql data language java external name '
myclass.myProcedureMethod' ;
class myclass{
public static Object[] myProcedureMethod(long id1 , long id2 , long id3){
return new Object[32];
}
}
Thanks !!
Re: Can A Function or procedure return multiple values !!
Posted by Rajesh Kartha <ka...@gmail.com>.
Daniel John Debrunner wrote:
>Rajesh Kartha wrote:
>
>
>
>>Hi Dheeraj,
>>
>>There could be other ways, but one that I could think of, would be to
>>convert the
>>Object[] into byte[] (using ByteArrayOutputStream and ObjectOutputStream)
>>in your myclass.myProcedureMethod().
>>
>>the function definition will have to modified to use
>>
>>RETURNS LONG VARCHAR FOR BIT DATA
>>
>>
>
>I don't think this will work with LONG VARCHAR FOR BIT DATA. Derby may
>allow a function to be defined using a return type of LONG VARCHAR FOR
>BIT DATA or BLOB but I think it fails at runtime. As part of DEBRY-438
>I've started looking at the BLOB issue.
>
>Also the correct Java type for LONG VARCHAR FOR BIT DATA is
>java.io.InputStream and not byte[].
>
>This trick may work with VARCHAR FOR BIT DATA, but then you would be
>limited to arounf 32k of bytes.
>
>Dan.
>
>
>
>
>
Thanks Dan, I forgot to mention the 32K limit on the data type in my mail
The LONG VARCHAR FOR BIT DATA did work for me,
Here is what I tried:
ij version 10.1
ij> connect 'jdbc:derby:funcDB';
ij> drop function retOBJ;
0 rows inserted/updated/deleted
ij> CREATE FUNCTION retOBJ (A BIGINT ,B BIGINT , C BIGINT ) RETURNS
LONG VARCHAR FOR BIT DATA EXTERNAL NAME 'Functions.returnObject'
LANGUAGE JAVA PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> values retOBJ(2,3,4);
1
--------------------------------------------------------------------------------------------------------------------------------
aced0005757200135b4c6a6176612e6c616e672e4f626a6563743b90ce589f1073296c0200007870000000037372000e6a6176612e6c616e672e4c6f6e673b8&
1 row selected
the Functions.returnObject() looks like this.
public static byte[] returnObject(long a, long b, long c) throws IOException
{
Object[] obj=new Object[3]; //create just three objects
obj[0]=new Long(a);
obj[1]=new Long(b);
obj[2]=new Long(c);
ByteArrayOutputStream bao=new ByteArrayOutputStream();
ObjectOutputStream oout=new ObjectOutputStream(bao);
oout.writeObject(obj);
bao.close();
oout.close();
return bao.toByteArray();
}
An Observation:
------------------
If I change the return type of the returnObject() to InputStream like:
public static InputStream returnObject(long a, long b, long c) throws
IOException{
....
return new ByteArrayInputStream(bao.toByteArray());
}
I get an error message expecting byte[] as the return type:
ij> CREATE FUNCTION retOBJ (A BIGINT ,B BIGINT , C BIGINT ) RETURNS
LONG VARCHAR FOR BIT DATA EXTERNAL NAME 'Functions.returnObject'
LANGUAGE JAVA PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> values retOBJ(2,3,4);
ERROR 42X50: No method was found that matched the method call byte[]
Functions.returnObject(long, long, long), tried all combinations of
object and primitive types and any possible type conversion for any
parameters the method call may have. The method might exist but it is
not public and/or static, or the parameter types are not method
invocation convertible.
Regards,
Rajesh
Re: Can A Function or procedure return multiple values !!
Posted by Daniel John Debrunner <dj...@apache.org>.
Rajesh Kartha wrote:
> Hi Dheeraj,
>
> There could be other ways, but one that I could think of, would be to
> convert the
> Object[] into byte[] (using ByteArrayOutputStream and ObjectOutputStream)
> in your myclass.myProcedureMethod().
>
> the function definition will have to modified to use
>
> RETURNS LONG VARCHAR FOR BIT DATA
I don't think this will work with LONG VARCHAR FOR BIT DATA. Derby may
allow a function to be defined using a return type of LONG VARCHAR FOR
BIT DATA or BLOB but I think it fails at runtime. As part of DEBRY-438
I've started looking at the BLOB issue.
Also the correct Java type for LONG VARCHAR FOR BIT DATA is
java.io.InputStream and not byte[].
This trick may work with VARCHAR FOR BIT DATA, but then you would be
limited to arounf 32k of bytes.
Dan.
Re: Can A Function or procedure return multiple values !!
Posted by Rajesh Kartha <ka...@gmail.com>.
Hi Dheeraj,
There could be other ways, but one that I could think of, would be to
convert the
Object[] into byte[] (using ByteArrayOutputStream and ObjectOutputStream)
in your myclass.myProcedureMethod().
the function definition will have to modified to use
RETURNS LONG VARCHAR FOR BIT DATA
Later in your java program that is invoking this function this byte[]
can be converted back into
Object[] ( using ByteArrayInputStream and ObjectInputStream )
something like:
{...
ByteArrayInputStream bio=new ByteArrayInputStream(rs.getBytes(1));
ObjectInputStream oi=new ObjectInputStream(bio);
Object [] obj=new Object[32];
obj=(Object [])oi.readObject();
...
}
Some other useful information on functions and procedures:
http://wiki.apache.org/db-derby/DerbySQLroutines
Hope this helps. Do post to the list, should you have any further questions.
-Rajesh
Dheeraj Dhiman wrote:
> I m using apache derby for my Project !!
> In this project we want to make a function
> which accecpt three long argument & i want to return Object[] ( Object
> Array ) !!
> Can this is possible in apache derby if yes then pls give example !!
>
> My Case is :::
>
> create function myprocedure( id1 bigint , id2 bigint , id3 bigint )
> returns Object[] parameter style java reads sql data language java
> external name 'myclass.myProcedureMethod' ;
>
> class myclass{
> public static Object[] myProcedureMethod(long id1 , long id2 , long id3){
>
> return new Object[32];
> }
> }
>
> Thanks !!
Re: Can A Function or procedure return multiple values !!
Posted by Daniel John Debrunner <dj...@apache.org>.
Dheeraj Dhiman wrote:
> I m using apache derby for my Project !!
> In this project we want to make a function
> which accecpt three long argument & i want to return Object[] ( Object Array
> ) !!
> Can this is possible in apache derby if yes then pls give example !!
No, Derby does not support array types.
A procedure can return one or more JDBC ResultSets from server-side JDBC
queries. I've added some examples to this page today.
http://wiki.apache.org/db-derby/DerbySQLroutines
Dan.