db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rajesh Kartha <karth...@gmail.com>
Subject Re: Can A Function or procedure return multiple values !!
Date Thu, 13 Apr 2006 18:34:05 GMT
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




Mime
View raw message