db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Need stored procedure help!
Date Wed, 15 Dec 2010 12:10:43 GMT
sivagururaja <sivagururaja@gmail.com> writes:

> Dear All,
>
> Is it possible to pass java.util.ArrayList<Integer> or int[] to stored
> procedure?
> I know a little bit Apache stored procedure. I believe that i can set the
> input parameter using the following,
> CallableStatement.setObject(parametername, Object);
> But i don't know, what is corresponding datatype to be used in the stored
> procedure (like varchar(), integer).

Derby doesn't have a built-in array or list data type, but you can
create your own list type with a statement like this (requires Derby
10.6 or higher):

CREATE TYPE LIST EXTERNAL NAME 'java.util.List' LANGUAGE JAVA

And then you can add a stored procedure that takes a list:

public class MyProcedures {
    public static void proc(java.util.List lst) {
        System.out.println("Got a list with " + lst.size() + " elements");
    }
}

CREATE PROCEDURE PROC (L LIST)
    EXTERNAL NAME 'MyProcedures.proc'
    LANGUAGE JAVA
    PARAMETER STYLE JAVA

Finally, you'd call the stored procedure from Java code like this:

    ArrayList lst = new ArrayList();
    lst.add("one element");
    lst.add("two elements");
    lst.add("three elements");

    PreparedStatement ps = conn.prepareStatement("call proc(?)");
    ps.setObject(1, lst);
    ps.execute();

Hope this helps,

-- 
Knut Anders

Mime
View raw message