Re: DECLARE CURSOR

On Mon, 18 Nov 2002 08:28:59 -0600, Haris Peco wrote:

> On Monday 18 November 2002 02:27 am, \"Matthew V.\ wrote:
>> On Sun, 17 Nov 2002 06:06:05 -0600, snpe wrote:
>> > On Sunday 17 November 2002 05:46 am, Frank Miles wrote:
>> >> On Sat, 16 Nov 2002, snpe wrote:
>> >> > On Saturday 16 November 2002 09:29 pm, Frank Miles wrote:
>> >> > > On Fri, 15 Nov 2002, snpe wrote:
>> >> > > > Hello,
>> >> > > >   When I call DECLARE CURSOR out of transaction command
>> >> > > >   success,
>> >> > > > but cursor is not created
>> >> > > >   Reference manual say that this get error :
>> >> > > > ERROR: DECLARE CURSOR may only be used in begin/end
>> >> > > > transaction blocks I don't find this text in pgsql source code
>> >> > > > What is problem ?
>> >> > >
>> >> > > According to the documentation for DECLARE CURSOR (v.7.2.x):
>> >> > >
>> >> > > "Cursors are only available in transactions. Use to BEGIN,
>> >> > > COMMIT and
>> >> > >     ROLLBACK to define a transaction block."
>> >> > >
>> >> > > This seems consistent with your error message.  Please try
>> >> > > wrapping your DECLARE inside a transaction using BEGIN,...
>> >> >
>> >> > I understand it.
>> >> > I don't understand why 'DECLARE CURSOR' success out of a
>> >> > transaction - I expect error
>> >>
>> >> What version are you using?  At least with 7.2.x, there is an
>> >> immediate error at the DECLARE statement.  Perhaps I am
>> >> misunderstanding your question?
>> >
>> > 7.3b5
>> > maybe, it is prepare for cursor out of a transaction (I hope)
>>
>> I'm getting a little confused, here, reading this.  I don't have a
>> BEGIN, COMMIT, or ROLLBACK in sight in my ESQL application, but my
>> cursor works just fine.  Under which circumstances are the BEGIN,
>> COMMIT, and ROLLBACK required?  Is that something specific to the C
>> interface?
>
> You don't use cursor, probably.
> For PostgreSQL cursor is explicit with DECLARE CURSOR in sql command It
> is like :
> BEGIN;;
> ..
> DECLARE c1 CURSOR FOR SELECT ...;
> ...
> FETCH 1 FROM c1
> ...
> COMMIT;
>
>
>
Yes, I do use a cursor.  The ESQL I mentioned means "Embedded SQL" (sorry,
thought everyone knew).  Cursors are a very big part of ESQL.  But I don't
have any BEGINS, or COMMITS (why would I? I do SELECTs, not
INSERTs/UPDATEs/DELETEs!).  The cursor declaration, and the subsequent
FETCHes, work just fine.  That's why I was wondering if I was missing
something.  The cursor works perfectly the way I wrote it, yet people in
this thread keep talking like cursors are only declareable/useable inside
transactions (BEGIN-COMMIT blocks).. I personally don't see why you would
want to waste transaction overhead unless you are modifying the data
(especially since Postgresql doesn't support updateable cursors).

In any case, whether or not it's the "correct" behavior, you don't need to
specify a BEGIN/COMMIT block to DECLARE a cursor.  The documentation the
original poster quoted appears to be in error, or outdated (I have the
same docs, and they don't match with actual behavior).  I declare my
cursor in an include file (so that it's global to the file), open the
cursor, fetch the cursor until EOF or other error, and process the data. I
don't "EXEC SQL BEGIN;" or anything anywhere.  Since I'm doing FETCHes,
there's no need for a COMMIT.

I was just wondering what the hullabaloo was all about, because I don't
get any of the errors described by previous posters, and thought maybe I
accidentally fixed something, or broke something that was supposed to
break my DECLARE...


I tried mucking around with
autocommit = off/on, but that affects neither the DECLARE nor the FETCH.
Is there supposed to be a global autocommit setting?  I couldn't find one
in the docs for 7.2.1.


--
Matthew Vanecek
perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);'
********************************************************************************
For 93 million miles, there is nothing between the sun and my shadow except me.
I'm always getting in the way of something...