Re: problems returning a resultset from a function

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