Re: Newby Question - accessing refcursor.

On Thu, 25 Sep 2008, burferd wrote:

> So, you saying that when I create my original database connection,
> I need to set Connection.setAutoCommit(false)
>
> I have not seen that in any of the examples/tutorials I have looked at.
> I would expect that to have an effect on updating the database, not fetching
> from the database.
> How will that affect other stored procedure fetches?
>

Cursors have transaction lifetime by default, so you must have an open
transaction to make them live between initial creation and the subsequent
data fetch.  You don't need to start a transaction at connection open, you
can wait until immediately before the stored procedure call.  This is not
described well in the stored procedure documention, but is a code comment
in the example here:

http://jdbc.postgresql.org/documentation/83/callproc.html#callproc-resultset-refcursor

It is also noted in the section "getting results based upon a cursor" and
I imagine it is also mentioned in the server documentation.

http://jdbc.postgresql.org/documentation/83/query.html#query-with-cursor

I'm not sure why you're concerned about other stored procedure calls.  You
can commit and re-enable autocommit as soon as you've retrieved the data
from the refcursor.

Kris Jurka