CODEWITHSUNDEEP
javapostgresqljdbc
user2291144
user2291144

Reputation: 65

how do i pass a cursor as parameter to stored procedure and catch it back in java

I have a stored procedure

CREATE OR REPLACE FUNCTION fun_retrieve_vector_receipt_payment_details(character varying, integer, character varying, character varying, character varying, refcursor)  RETURNS refcursor AS

this is my java code

con.setAutoCommit(false);
        String sql = "{call fun_retrieve_vector_receipt_payment_details(?,?,?,?,?,?)}";
        callableStatement = con.prepareCall(sql);
        callableStatement.setString(1, "PAYMENT");
        callableStatement.setInt(2, 41);
        callableStatement.setString(3, "2011-05-06");
        callableStatement.setString(4, "2013-05-06");
        callableStatement.setString(5, "Y");
        callableStatement.setObject(6, rs);
        callableStatement.registerOutParameter(6, Types.OTHER);

        callableStatement.execute();

        ResultSet resultSet = (ResultSet) callableStatement.getObject(6);
        //          rs = callableStatement.executeQuery();
        boolean next = resultSet.next();
        if (next)
        {
            System.out.println(resultSet.getString(1));
        }

why do i get resultset as null?
what should i pass as refcursor as parameter from java?

i am using postgres as my database

Upvotes: 1

Views: 2740

Answers (2)

Pavel Stehule
Pavel Stehule

Reputation: 45835

refcursor is a name of cursor - so it is string. So you have to get a this name, and you can pass it as string:

postgres=# begin;
BEGIN
Time: 0.398 ms
postgres=# declare xxx cursor for select * from f1;
DECLARE CURSOR
Time: 23.409 ms
postgres=# select fx('xxx');
NOTICE:  (10,20)
NOTICE:  (340,30)
 fx 
────

(1 row)

commit;

CREATE OR REPLACE FUNCTION public.fx(refcursor)
 RETURNS void
 LANGUAGE plpgsql
AS $function$ 
declare r record;
begin
  while true 
  loop 
    fetch $1 into r; 
    exit when not found;
    raise notice '%', r;
  end loop;
end;
$function$

Upvotes: 1

krokodilko
krokodilko

Reputation: 36107

Try this approach:

Statement stmt = conn.createStatement();
stmt.execute("CREATE OR REPLACE FUNCTION "
        +" fun_retrieve_vector_receipt_payment_details(x1 integer, x2 integer )"
        + " RETURNS refcursor AS '"
        + " DECLARE "
        + "    mycur refcursor; "
        + " BEGIN "
        + "    OPEN mycur FOR SELECT x FROM generate_series( x1, x2 ) x; "
        + "    RETURN mycur; "
        + " END;' language plpgsql");
stmt.close();

conn.setAutoCommit(false);

CallableStatement proc = conn.prepareCall(
         "{ ? = call fun_retrieve_vector_receipt_payment_details( ?, ?  ) }");
proc.registerOutParameter(1, Types.OTHER);
proc.setInt(2 , 13 );
proc.setInt(3 , 17 );
proc.execute();
ResultSet results = (ResultSet) proc.getObject(1);
while (results.next()) {
    System.out.println(results.getString(1));
}

results.close();
proc.close();
conn.close();

 

Run ...
13
14
15
16
17

Upvotes: 1

Related Questions