Обсуждение: PG 7.3.4 VS PG 8.0.3 Problem
I am having an issue with 7.3.4 vs 8.0.3. I have done a simple conversion of one of my databases from 7.3.4 to 8.0.3. I used the 8.0.3 pg_dump to dump the database and load via psql 8.0.3. However, I am having a problem. I have a query that our application generates that is returning different results back to our application. 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. Why is the 8.0.3 database returning a different code? Our application is checking the code returned, and since it is getting a 100, it is not performing as expected (as compared to our 7.3.4 production system). Anyway, the only thing that has changed in the environment is the PostgreSQL db. The Application and odbc drivers are all the same. Can someone please enlighten me as to what is going on? Thanks, Chris
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
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 >
Michael Fuhr <mike@fuhr.org> writes:
> 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.
According to the CVS logs, ecpg hasn't changed at all in the 7.3
branch since 7.3 release. However, it could be that Chris is comparing
against the behavior of a program built with an older (pre-7.3) ecpg ...
regards, tom lane
Chris Hoover <revoohc@gmail.com> writes:
> 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.
Hmm. I seriously doubt that the server's response to the query has
changed any. What seems more likely is that the ODBC driver has changed
its behavior. Can you try linking your app with the older ODBC driver
and running it against the newer server?
Also, pgsql-odbc is probably a better place to ask about it.
regards, tom lane
Tom, I'll send this over to the odbc list. However, the odbc driver is liked into our cobol environment. I did not change the driver. We are running the 7.2.5 odbc driver. This is why I am pointing to a difference in the db responses. The entire environment is the exactly same except for the database versions (I am actually running the exact same code and odbc driver against the 2 db's). I can have the application connect via odbc to my 7.3.4 instance and get the sqlcode of 0. I can have the same application with the same odbc connect via odbc to my 8.0.3 instance and I get the sqlcode of 100. This is what is pointing me to a problem with one of the db's. It appears that the sqlca code has had to of changed between the 7.3.4 and 8 code and is now behaving in a different (and unexpected) manner. BTW, which version is correct? Should a no rows return query give a 0 or a 100? Chris On 8/9/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Chris Hoover <revoohc@gmail.com> writes: > > 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. > > Hmm. I seriously doubt that the server's response to the query has > changed any. What seems more likely is that the ODBC driver has changed > its behavior. Can you try linking your app with the older ODBC driver > and running it against the newer server? > > Also, pgsql-odbc is probably a better place to ask about it. > > regards, tom lane >
Chris Hoover <revoohc@gmail.com> writes:
> This is what is pointing me to a problem with one of the db's. It
> appears that the sqlca code has had to of changed between the 7.3.4
> and 8 code and is now behaving in a different (and unexpected) manner.
There is no "sqlca" in the backend protocol at all, so I really dunno
what is causing the change in behavior.
> BTW, which version is correct? Should a no rows return query give a 0
> or a 100?
I suspect 100 is correct, but you'd have to go read the ODBC spec to
be sure.
regards, tom lane
On Tue, Aug 09, 2005 at 02:44:08PM -0600, Michael Fuhr wrote: > 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.) ...none of which might be relevant if ODBC defines things differently. -- Michael Fuhr
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
Chris Hoover wrote: >Sorry, I should have given some setup details. > > >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. > > > We are also using Cobol (Micro Focus NetExpress) and we haven't noticed any changes when moving from version 7.x to version 8. Another question is whether sqlcode was returning good result until now? As far as I know (and it works that way with us) sqlcode _should_ have a value of 100 when query returns no rows. Value of 0 indicates there were no errors, and value of 100 means 'no rows found'. With Regards Dragan Matic
Ok, I think I have narrowed this down even further. I went and installed a 7.3.4 db on our RHEL 4 server and is exibiting the same behavior. I believe there is something with RHEL 2.1 and PostgreSQL that causes the wrong sqlcode to be returned. That is the only thing I can figure out. I'm not sure where to go from here. Any ideas? Chris On 8/10/05, Dragan Matić <mlists@panforma.co.yu> wrote: > Chris Hoover wrote: > > >Sorry, I should have given some setup details. > > > > > >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. > > > > > > > We are also using Cobol (Micro Focus NetExpress) and we haven't noticed > any changes when moving from version 7.x to version 8. > Another question is whether sqlcode was returning good result until now? > As far as I know (and it works that way with us) sqlcode _should_ have a > value of 100 when query returns no rows. Value of 0 indicates there were > no errors, and value of 100 means 'no rows found'. > > With Regards > > Dragan Matic > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Hello. I have some bad experience with RHEL 2 and PostgreSQL ODBC. I have seen there a following problem: An ODBC application gets SUCCESS regardless of wether the SQL command succeeded or not! I don't know, wether the psqlodbc behaviour was caused by application's ODBC calling convention, or is there something fatally wrong. I do remember, that psql command line tool worked. Isql (ODBC command line tool for UnixODBC) did not notice errors either (please check, if you need this information). So it seemed, that the RHEL2's psqlodbc is bad, and nobody uses it. The problem is solveable by replacing the psqlodbc driver with a working one. Regards, Marko Ristola Chris Hoover wrote: >Ok, I think I have narrowed this down even further. I went and >installed a 7.3.4 db on our RHEL 4 server and is exibiting the same >behavior. I believe there is something with RHEL 2.1 and PostgreSQL >that causes the wrong sqlcode to be returned. That is the only thing >I can figure out. > >I'm not sure where to go from here. Any ideas? > >Chris > >On 8/10/05, Dragan Matić <mlists@panforma.co.yu> wrote: > > >>Chris Hoover wrote: >> >> >> >>>Sorry, I should have given some setup details. >>> >>> >>>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. >>> >>> >>> >>> >>> >>We are also using Cobol (Micro Focus NetExpress) and we haven't noticed >>any changes when moving from version 7.x to version 8. >>Another question is whether sqlcode was returning good result until now? >>As far as I know (and it works that way with us) sqlcode _should_ have a >>value of 100 when query returns no rows. Value of 0 indicates there were >>no errors, and value of 100 means 'no rows found'. >> >>With Regards >> >>Dragan Matic >> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 1: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly >> >> >> > >---------------------------(end of broadcast)--------------------------- >TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > >