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 AE4FBD8536 for ; Tue, 30 Aug 2005 13:56:12 -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 08520-03 for ; Tue, 30 Aug 2005 16:56:10 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id ECDCFD8490 for ; Tue, 30 Aug 2005 12:13:30 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j7UFDLxY024803 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Tue, 30 Aug 2005 09:13:23 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j7UFDKcV056957; Tue, 30 Aug 2005 09:13:20 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j7UFDKM1056956; Tue, 30 Aug 2005 09:13:20 -0600 (MDT) (envelope-from mfuhr) Date: Tue, 30 Aug 2005 09:13:20 -0600 From: Michael Fuhr To: "Thomas F. O'Connell" Cc: andy rost , pgsql-admin@postgresql.org Subject: Re: sqlstate 02000 while declaring cursor/freeing statement Message-ID: <20050830151320.GA56850@winnie.fuhr.org> References: <43135FA9.3060807@noaa.gov> <4DADE9CC-4B44-4A9A-A424-E7916937610C@sitening.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4DADE9CC-4B44-4A9A-A424-E7916937610C@sitening.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 required=5 tests=[AWL=0.005] X-Spam-Level: X-Archive-Number: 200508/388 X-Sequence-Number: 18707 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