Re: PG 7.3.4 VS PG 8.0.3 Problem

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