Date: Mon, 29 Aug 2005 17:10:37 -0600
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
Message-ID: <20050829231037.GA8406@winnie.fuhr.org>
References: <431361D8.8050900@noaa.gov>
	<20050829200935.GA98664@winnie.fuhr.org>
	<43137572.4090107@noaa.gov> <43138098.40201@noaa.gov>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <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