X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5B3CFD8668 for ; Tue, 30 Aug 2005 17:10:44 -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 54796-03 for ; Tue, 30 Aug 2005 20:10:39 +0000 (GMT) Received: from home.nohrsc.nws.gov (home.nohrsc.nws.gov [192.46.108.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0E514D8659 for ; Tue, 30 Aug 2005 17:10:36 -0300 (ADT) Received: from noaa.gov (opps-gw.nohrsc.nws.gov [192.46.108.142]) by home.nohrsc.nws.gov (Postfix) with ESMTP id 56C16A5D2; Tue, 30 Aug 2005 15:10:37 -0500 (CDT) Message-ID: <4314BD3D.9030104@noaa.gov> Date: Tue, 30 Aug 2005 15:10:37 -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: "Thomas F. O'Connell" , pgsql-admin@postgresql.org Subject: Re: sqlstate 02000 while declaring cursor/freeing statement References: <43135FA9.3060807@noaa.gov> <4DADE9CC-4B44-4A9A-A424-E7916937610C@sitening.com> <20050830151320.GA56850@winnie.fuhr.org> In-Reply-To: <20050830151320.GA56850@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/396 X-Sequence-Number: 18715 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 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). > -- -------------------------------------------------------------------------------- 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 --------------------------------------------------------------------------------