Thread: Re: [JDBC] query

Re: [JDBC] query

From

Chuck Davis

Date:

29 April 2017, 22:12:46

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

Re: [JDBC] query

From

Tom Lane

Date:

29 April 2017, 22:45:13

Chuck Davis <cjgunzel@gmail.com> writes:
> If I execute the select statement in psql it works as expected:
> ...
> 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

I'd lay pretty long odds that that means the program is connecting to a
different database than you're connecting to in psql.  Double-check the
connection URL it's using.

(A common variant of that is "program is using a different search_path",
but since you're failing on a schema-qualified relation name, search
path doesn't enter into it.)

            regards, tom lane

Re: query

From

Tom Lane

Date:

29 April 2017, 22:45:13

Chuck Davis <cjgunzel@gmail.com> writes:
> If I execute the select statement in psql it works as expected:
> ...
> 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

I'd lay pretty long odds that that means the program is connecting to a
different database than you're connecting to in psql.  Double-check the
connection URL it's using.

(A common variant of that is "program is using a different search_path",
but since you're failing on a schema-qualified relation name, search
path doesn't enter into it.)

            regards, tom lane

Re: [JDBC] query

From

Dave Cramer

Date:

01 May 2017, 08:32:00

Ya, I'm with Tom on this. While there may be bugs in the driver there is nothing this simple

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 29 April 2017 at 12:45, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Chuck Davis <cjgunzel@gmail.com> writes:
> If I execute the select statement in psql it works as expected:
> ...
> 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

I'd lay pretty long odds that that means the program is connecting to a
different database than you're connecting to in psql. Double-check the
connection URL it's using.

(A common variant of that is "program is using a different search_path",
but since you're failing on a schema-qualified relation name, search
path doesn't enter into it.)

regards, tom lane

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Re: query

From

Dave Cramer

Date:

01 May 2017, 08:32:00

Ya, I'm with Tom on this. While there may be bugs in the driver there is nothing this simple

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 29 April 2017 at 12:45, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Chuck Davis <cjgunzel@gmail.com> writes:
> If I execute the select statement in psql it works as expected:
> ...
> 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

I'd lay pretty long odds that that means the program is connecting to a
different database than you're connecting to in psql. Double-check the
connection URL it's using.

(A common variant of that is "program is using a different search_path",
but since you're failing on a schema-qualified relation name, search
path doesn't enter into it.)

regards, tom lane

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc