I am attempting to move a database from DB2 to Postgres and I program with java. Using 9.6.2 with JDBC driver 42 on jdk 8u121 on Linux.
I have a statement as follows:
rs = stat.executeQuery("SELECT U.CONO, CONAME, EMPNO, USERID FROM INFOSYS.USERS U, INFOSYS.COMPANY C WHERE USERID='infosysadmin' AND PASSWORD = 'infosyasdmin' AND U.CONO = C.CONO");
If I execute the select statement in psql it works as expected:
nordic=# SELECT U.CONO, CONAME, EMPNO, USERID FROM INFOSYS.USERS U, INFOSYS.COMPANY C WHERE USERID='infosysadmin' AND PASSWORD ='infosysadmin' AND U.CONO = C.CONO;
cono | coname | empno | userid
------+----------------------------------------------------+-------+--------------
5 | Nordic Services, Inc. | 0 | infosysadmin
(1 row)
If I execute the statement in my program (and this has been working for years on DB2) I get the following exception:
org.postgresql.util.PSQLException: ERROR: relation "infosys.company" does not exist
Position: 60
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2412)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2125)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:297)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:301)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:287)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:264)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:231)
at com.yakridge.server.QFQueries.QFQueryUsers.runQueryFormatQuery(QFQueryUsers.java:63)
It clearly does exist and it does work via psql. I'm at a loss.....Is there a bug in JDBC driver?
Thanks for any pointers.
Chuck