From: Michael Fuhr <mike@fuhr.org>
To: andy rost <andy.rost@noaa.gov>
Cc: pgsql-sql@postgresql.org
Subject: Re: sqlstate 02000 while declaring cursor/freeing prepared
Date: Mon, 29 Aug 2005 17:10:37 -0600
Message-ID: <20050829231037.GA8406@winnie.fuhr.org> (raw)
In-Reply-To: <43138098.40201@noaa.gov>
References: <431361D8.8050900@noaa.gov>
<20050829200935.GA98664@winnie.fuhr.org>
<43137572.4090107@noaa.gov>
<43138098.40201@noaa.gov>
On Mon, Aug 29, 2005 at 04:39:36PM -0500, andy rost wrote:
> I worked on my problem a little further and have a little more
> information to share. The declare statement that fails consistently
> follows a select statement that returns zero rows (and sqlcode 100 and
> sqlstate '02000'). If I ommit the select statement from the code or set
> sqlcode to 0 before calling the declare statement, the declare statement
> works fine.
>
> It appears as though the declare statement is not updating the sqlca
> structure. Is this by design for the ecpg options that I'm using? Did I
> pick up bad habits while using Informix?
Apparently since PostgreSQL doesn't actually have an OPEN statement,
the ECPG code generator doesn't issue the DECLARE until the code
OPENs the cursor. Observe:
% cat foo.pgc
int
main(void)
{
EXEC SQL CONNECT TO DEFAULT;
printf("before DECLARE\n");
EXEC SQL DECLARE curs CURSOR FOR SELECT 1;
printf("after DECLARE, before OPEN\n");
EXEC SQL OPEN curs;
printf("after OPEN\n");
EXEC SQL CLOSE curs;
EXEC SQL DISCONNECT;
return 0;
}
% ecpg foo.pgc
% cat foo.c
...
printf("before DECLARE\n");
/* declare curs cursor for select 1 */
#line 7 "foo.pgc"
printf("after DECLARE, before OPEN\n");
{ ECPGdo(__LINE__, 0, 1, NULL, "declare curs cursor for select 1 ", ECPGt_EOIT, ECPGt_EORT);}
#line 9 "foo.pgc"
printf("after OPEN\n");
...
Notice that "after DECLARE" actually comes *before* the DECLARE
statement is issued, so references to sqlcode and sqlstate would
see values from a previous command. I don't know if DECLARE is
supposed to affect error codes or not; I'd have to consult the
standard.
--
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, andy.rost@noaa.gov
Subject: Re: sqlstate 02000 while declaring cursor/freeing prepared
In-Reply-To: <20050829231037.GA8406@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