Re: problems returning a resultset from a function

Thanks a lot.
This really solve my problem.

regards.

--- Oliver Jowett <oliver(at)opencloud(dot)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