X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 65980D8682 for ; Tue, 30 Aug 2005 17:10:38 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 51968-10 for ; Tue, 30 Aug 2005 20:10:26 +0000 (GMT) Received: from home.nohrsc.nws.gov (home.nohrsc.nws.gov [192.46.108.2]) by svr1.postgresql.org (Postfix) with ESMTP id E091FD8689 for ; Tue, 30 Aug 2005 17:10:21 -0300 (ADT) Received: from noaa.gov (opps-gw.nohrsc.nws.gov [192.46.108.142]) by home.nohrsc.nws.gov (Postfix) with ESMTP id 9C145A5D2; Tue, 30 Aug 2005 15:10:21 -0500 (CDT) Message-ID: <4314BD2D.1040805@noaa.gov> Date: Tue, 30 Aug 2005 15:10:21 -0500 From: andy rost User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Michael Fuhr Cc: pgsql-sql@postgresql.org Subject: Re: sqlstate 02000 while declaring cursor/freeing prepared References: <431361D8.8050900@noaa.gov> <20050829200935.GA98664@winnie.fuhr.org> <43137572.4090107@noaa.gov> <43138098.40201@noaa.gov> <20050829231037.GA8406@winnie.fuhr.org> In-Reply-To: <20050829231037.GA8406@winnie.fuhr.org> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 required=5 tests=[FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200508/290 X-Sequence-Number: 22518 Just so that we can snip this thread, we've confirmed that free cursor and free statement do not affect sqlca structure elements sqlcode and sqlstate. Michael Fuhr wrote: > 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. > -- -------------------------------------------------------------------------------- Andrew Rost National Operational Hydrologic Remote Sensing Center (NOHRSC) National Weather Service, NOAA 1735 Lake Dr. West, Chanhassen, MN 55317-8582 Voice: (952)361-6610 x 234 Fax: (952)361-6634 arost@nohrsc.nws.gov http://www.nohrsc.nws.gov --------------------------------------------------------------------------------