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

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

Leo Martin Orfei said:

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';

My apologies if this has been responded to already as I am not currently
reading pgsql-jdbc and the archive three days behind. AFAIK there isn't
support for embedded work in jdbc, so it doesn't seem returning a cursor
directly would help (well maybe there is a way...I don't know).

In order to accomplish what I think you want to accomplish (which is to
establish a jdbc resultset object from a stored function) I've done the
following in the past:

Create a pl/pgsql function that returns a rowtype, DECLARED as follows:

-- the "table" in the following refers to an existing table definition
row table%rowtype;

In the pl/pgsql script you have something like:

-- return each row in the result set
for row in SELECT name, address FROM table loop
return next row;
end loop;
return;

Then in java call this using just a regular statement object:

// test() is the name of the stored function.
rs = statement.executeQuery("select * from test()");
while (rs.next()) {
...code to browse/process the rows...
}

Note the above examples might have a typo or two...but that's the general
idea. I have not used the stored procedure statement object as you did.
Perhaps that would be more portable.

Best,

Jim

--
Jim Wilson - IT Manager
Kelco Industries
PO Box 160
58 Main Street
Milbridge, ME 04658
207-546-7989 - FAX 207-546-2791
http://www.kelcomaine.com