From: Chris Hoover <revoohc@gmail.com>
To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-admin@postgresql.org
Subject: Re: PG 7.3.4 VS PG 8.0.3 Problem
Date: Tue, 9 Aug 2005 15:03:03 -0400
Message-ID: <1d219a6f050809120320cb1acb@mail.gmail.com> (raw)
In-Reply-To: <20050809183443.GA25232@winnie.fuhr.org>
References: <1d219a6f05080910277cbf10b2@mail.gmail.com>
<20050809183443.GA25232@winnie.fuhr.org>
Sorry, I should have given some setup details.
First off, we are running 8.0.3 from the postgresql.org rpms on RHEL AS 4.
7.3.4 is running on RHEL AS 2.1 installed from source.
What is ECPG?
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.
The application generates a sql like this (ugly I know):
SELECT *
FROM CLMHDR
WHERE
(HDR_CLM_STATUS = 'L ' OR
HDR_CLM_STATUS = 'E ' OR
HDR_CLM_STATUS = 'V ' OR
HDR_CLM_STATUS = 'P ' OR
HDR_CLM_STATUS = 'H ' OR
HDR_CLM_STATUS = 'A ' OR
HDR_CLM_STATUS = 'R ' OR
HDR_CLM_STATUS = 'T ' OR
HDR_CLM_STATUS = '* ' OR
HDR_CLM_STATUS = '* ') AND
(HDR_CREATE_DT >= '' AND HDR_CREATE_DT <= '') AND
(HDR_ATCH_IND = 'A' OR
HDR_ATCH_IND = 'E' OR
HDR_ATCH_IND = 'U' OR
HDR_ATCH_IND = 'C' OR
HDR_ATCH_IND = '') AND
HDR_USER_ID = 'some_user'
ORDER BY HDR_INV_NBR, HDR_INV_QFR;
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.
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.
thanks for your assistance,
Chris
On 8/9/05, Michael Fuhr <mike@fuhr.org> wrote:
> On Tue, Aug 09, 2005 at 01:27:55PM -0400, Chris Hoover wrote:
> > When I connect to a 7.3.4 database, the query returns 0 rows and a
> > sqlca.sqlcode of 0
> >
> > However, when I connect to an 8.0.3 database, the query returns 0 rows
> > and a sqlca.sqlcode of 100.
>
> You don't mention it but it sounds like you're using ECPG. Can you
> post a simple but complete example that works differently in 7.3.4
> and 8.0.3?
>
> I just did some tests with 7.3.10 and 8.0.3 and both returned a
> sqlca.sqlcode of 100 for a query that returned no rows. Either
> something changed between 7.3.4 and 7.3.10 or my test didn't
> duplicate what you're doing.
>
> --
> Michael Fuhr
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: revoohc@gmail.com, mike@fuhr.org
Subject: Re: PG 7.3.4 VS PG 8.0.3 Problem
In-Reply-To: <1d219a6f050809120320cb1acb@mail.gmail.com>
References: <1d219a6f05080910277cbf10b2@mail.gmail.com> <20050809183443.GA25232@winnie.fuhr.org> <1d219a6f050809120320cb1acb@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox