Skip site navigation (1) Skip section navigation (2)

Re: Newby Question - accessing refcursor.

From: Kris Jurka <books(at)ejurka(dot)com>
To: burferd <jarzabek(at)pobox(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Newby Question - accessing refcursor.
Date: 2008-09-26 05:15:20
Message-ID: Pine.BSO.4.64.0809260108001.31972@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-jdbc

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

In response to

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2008-09-26 05:39:50
Subject: Re: [JDBC] need help of getting PK after insertRow in JDBC
Previous:From: burferdDate: 2008-09-26 02:21:46
Subject: Re: Newby Question - accessing refcursor.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group