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
