From: Michael Fuhr <mike@fuhr.org>
To: Thomas F. O'Connell <tfo@sitening.com>
Cc: andy rost <andy.rost@noaa.gov>
Cc: pgsql-admin@postgresql.org
Subject: Re: sqlstate 02000 while declaring cursor/freeing statement
Date: Tue, 30 Aug 2005 09:13:20 -0600
Message-ID: <20050830151320.GA56850@winnie.fuhr.org> (raw)
In-Reply-To: <4DADE9CC-4B44-4A9A-A424-E7916937610C@sitening.com>
References: <43135FA9.3060807@noaa.gov>
<4DADE9CC-4B44-4A9A-A424-E7916937610C@sitening.com>
On Tue, Aug 30, 2005 at 09:01:36AM -0500, Thomas F. O'Connell wrote:
> On Aug 29, 2005, at 2:19 PM, andy rost wrote:
> >
> > $declare loop1 cursor with hold for
> > select distinct ( tabname )
> > from meta ;
> >
> >results in sqlca.sqlcode = 100 and sqlca.sqlstate = '02000'
>
> [snip]
>
> I don't see anything wrong with your cursor declaration, so this just
> looks like it's indicating that no rows were returned.
See my response to the same thread in pgsql-sql about what's
happening:
http://archives.postgresql.org/pgsql-sql/2005-08/msg00274.php
The ECPG code generator doesn't issue the DECLARE statement until
the cursor is opened, so checking sqlcode/sqlstate immediately after
DECLARE but before OPEN will get the error code values from a
previous command.
Here's an excerpt from the DECLARE documentation:
The PostgreSQL server does not implement an OPEN statement for
cursors; a cursor is considered to be open when it is declared.
However, ECPG, the embedded SQL preprocessor for PostgreSQL,
supports the standard SQL cursor conventions, including those
involving DECLARE and OPEN statements.
Since declaring a cursor in PostgreSQL also opens it, ECPG apparently
defers doing anything with the cursor until it finds the OPEN
statement (i.e., EXEC SQL DECLARE generates no code, and EXEC SQL
OPEN generates a DECLARE).
--
Michael Fuhr
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: mike@fuhr.org, tfo@sitening.com, andy.rost@noaa.gov
Subject: Re: sqlstate 02000 while declaring cursor/freeing statement
In-Reply-To: <20050830151320.GA56850@winnie.fuhr.org>
References: <43135FA9.3060807@noaa.gov> <4DADE9CC-4B44-4A9A-A424-E7916937610C@sitening.com> <20050830151320.GA56850@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox