Re: PG 7.3.4 VS PG 8.0.3 Problem - Mailing list pgsql-admin

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
>