X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 825295287F for ; Tue, 9 Aug 2005 17:44:25 -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 16204-08 for ; Tue, 9 Aug 2005 20:44:15 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 69AD352AA2 for ; Tue, 9 Aug 2005 17:44:12 -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 j79Ki9YI045458 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Tue, 9 Aug 2005 14:44:11 -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 j79Ki98n026025; Tue, 9 Aug 2005 14:44:09 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j79Ki8xq026024; Tue, 9 Aug 2005 14:44:08 -0600 (MDT) (envelope-from mfuhr) Date: Tue, 9 Aug 2005 14:44:08 -0600 From: Michael Fuhr To: Chris Hoover Cc: pgsql-admin@postgresql.org Subject: Re: PG 7.3.4 VS PG 8.0.3 Problem Message-ID: <20050809204408.GA25741@winnie.fuhr.org> References: <1d219a6f05080910277cbf10b2@mail.gmail.com> <20050809183443.GA25232@winnie.fuhr.org> <1d219a6f050809120320cb1acb@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1d219a6f050809120320cb1acb@mail.gmail.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200508/124 X-Sequence-Number: 18443 On Tue, Aug 09, 2005 at 03:03:03PM -0400, Chris Hoover wrote: > What is ECPG? Embedded SQL in C. I mentioned it because it's the only thing in the standard PostgreSQL source code or documentation that contains the string "sqlca": http://www.postgresql.org/docs/8.0/static/ecpg.html > Anyway, I'm not sure how to give you a simple example. We are running > a cobol application that is connecting to PostgreSQL via odbc. The > application runs on one linux box and the database on a seperate one. What ODBC driver and version are you using? Are you using different versions of the driver, or are you using the same version of the driver to connect to different versions of PostgreSQL? > When this query is run against either version of PG, it does not > return any rows (obviously due to the issue with hdr_create_dt). I > actually walked through the code using our cobol debugger to find this > issue. When it checks the sqlca.sqlcode record returned by this query > in 7.3.4, it has a 0. In 8.0.3, it has a 100. sqlca.sqlcode is a client-side variable, so somehow the driver is setting it based on the characteristics of the query's response. In ECPG, for example, sqlca.sqlcode would be set to 100 (ECPG_NOT_FOUND) if the libpq function PQntuples() returns a value less than 1, indicating that the query returned no tuples. The ECPG libraries in 7.3 and 8.0 both behave this way. You'll need to find out how your ODBC driver assigns this value, and why the assigned value might have changed. You might find better help on the pgsql-odbc mailing list. > I know that we are going to have to get this bad query fixed in our > code, but I am very curious why the same query generating the same > results would return different sqlca records. I am really hoping > there might be a way to make it behave like 7.3.4 does since this > problem sql is imbedded quite deeply in our application and the fix > will not be trivial. I wonder if the sqlcode value of 0 on which you're relying is correct, since SQL-92 mentions a value of 100 as meaning "no data." (BTW, SQL-92 deprecates SQLCODE, and SQL:1999 appears to have removed it altogether.) -- Michael Fuhr