Thread: problems returning a resultset from a function

hi.

I have a problem with postgres functions.
I need return a resultset from a postgres function and
browse the resultset in a java app.
I try with this simple function:

create function test() returns catalog.refcursor as'
declare aux refcursor;
BEGIN
    OPEN aux FOR SELECT name, address FROM table;
    RETURN aux;
END;
'LANGUAGE 'plpgsql';


and the java

CallableStatement cs = null;
ResultSet rs = null;
cs = con.prepareCall("{ ? = call test()}");
cs.registerOutParameter(1, java.sql.Types.OTHER);
rs = cs.executeQuery();
rs.next();
System.out.println("name: " +rs.getString(1));

but throws te following error:

cursor "<unnamed portal 1>" does not exist

I try this too:

Statement s = null;
rs = s.executeQuery("select test()");
rs.next();
System.out.println("name: " +rs.getString(1));

and don't throws an error, but show me the following
result:

name: <unnamed portal 1>

anybody can help me with this?

thnx;





__________________________________
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail

Leo Martin Orfei wrote:

[.. declare function returning refcursor ..]

> CallableStatement cs = null;
> ResultSet rs = null;
> cs = con.prepareCall("{ ? = call test()}");
> cs.registerOutParameter(1, java.sql.Types.OTHER);
> rs = cs.executeQuery();
> rs.next();
> System.out.println("name: " +rs.getString(1));
>
> but throws te following error:
>
> cursor "<unnamed portal 1>" does not exist

Check that you have called Connection.setAutoCommit(false). If
autocommit is on, your returned cursor will be closed as soon as the
transaction (auto)commits, so the subsequent select done to fetch the
portal contents will fail.

A '? = call' escape does not return a resultset. You should use the
CallableStatement.get...() methods to retrieve the values of the out
parameter. You will likely see errors complaining about no resultset
being returned from executeQuery() once you fix the autocommit setting.

The refcursor is returned as a ResultSet (as the out-parameter value)
i.e. CallableStatement.getObject(1) will return a ResultSet that has the
contents of the refcursor.

See
http://www.postgresql.org/docs/current/static/jdbc-callproc.html for
some example code on using callable statements and refcursor-returning
functions. (note that using PGRefCursorResultSet is deprecated; just use
getString() to obtain the cursor name).

-O

Thanks a lot.
This really solve my problem.

regards.

--- Oliver Jowett <oliver@opencloud.com> wrote:

> Leo Martin Orfei wrote:
>
> [.. declare function returning refcursor ..]
>
> > CallableStatement cs = null;
> > ResultSet rs = null;
> > cs = con.prepareCall("{ ? = call test()}");
> > cs.registerOutParameter(1, java.sql.Types.OTHER);
> > rs = cs.executeQuery();
> > rs.next();
> > System.out.println("name: " +rs.getString(1));
> >
> > but throws te following error:
> >
> > cursor "<unnamed portal 1>" does not exist
>
> Check that you have called
> Connection.setAutoCommit(false). If
> autocommit is on, your returned cursor will be
> closed as soon as the
> transaction (auto)commits, so the subsequent select
> done to fetch the
> portal contents will fail.
>
> A '? = call' escape does not return a resultset. You
> should use the
> CallableStatement.get...() methods to retrieve the
> values of the out
> parameter. You will likely see errors complaining
> about no resultset
> being returned from executeQuery() once you fix the
> autocommit setting.
>
> The refcursor is returned as a ResultSet (as the
> out-parameter value)
> i.e. CallableStatement.getObject(1) will return a
> ResultSet that has the
> contents of the refcursor.
>
> See
>
http://www.postgresql.org/docs/current/static/jdbc-callproc.html
> for
> some example code on using callable statements and
> refcursor-returning
> functions. (note that using PGRefCursorResultSet is
> deprecated; just use
> getString() to obtain the cursor name).
>
> -O
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com