Thread: Re: 8.0 beta4: Exception when including catalog qualifier in SELECT from

The following statements work fine in 7.4

=20

                                    Connection conn =3D
DriverManager.getConnection("jdbc:postgresql://localhost:5432/psdb","xxx
","");

                                    Statement stmt =3D
conn.createStatement();

                                    stmt.execute("CREATE TABLE
\"public\".tt (ans bool)");

                                    stmt.close();

=20

                                    stmt =3D conn.createStatement();

                                    ResultSet rs =3D
stmt.executeQuery("SELECT \"psdb \".\"public\".\"tt\".\"ans\" FROM
\"psdb \".\"public\".\"tt\"");

                                    while (rs.next()) {

=20
System.out.println(rs.getBoolean(1));

                                    }

                                    rs.close();

=20

=20

But in 8.0 Beta4 we get the following exception at the SELECT
executeQuery line:

=20

java.sql.SQLException: ERROR: syntax error at end of input

      at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecu
torImpl.java:1187)

      at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImp
l.java:990)

      at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:
138)

      at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stateme
nt.java:347)

      at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdb
c2Statement.java:279)

      at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2St
atement.java:191)

      at BoolTest.main(BoolTest.java:30)

=20

=20

OS =3D Windows XP Pro

Java version 1.5.0-b64

JDBC driver: pgdev.307.jdbc3.jar

=20

This seems to be related to having the catalog qualifier.  There is no
problem when just the schema is included. i.e.=20

            ResultSet rs =3D stmt.executeQuery("SELECT
\"public\".\"tt\".\"ans\" FROM \"public\".\"tt\"");

works OK.

=20

Also, there is no problem with UPDATE or INSERT statements when the
catalog is included.  Only in the SELECT statement.

=20

I did note that the driver does return false to
connection.getMetaData().supportsCatalogsInDataManipulation(), so, there
is actually no reason to expect that catalog qualifiers would be
supported. However, as noted, it did work OK in 7.4 (even though the
supportsCatalogsInDataManipulation() returned false there also).

=20

Same type of error is returned just running fully qualified SQL
statements in the Query Tool in pgAdmin.

=20

Thanks.

=20

-al laferriere

On Mon, 1 Nov 2004, Laferriere, Al wrote:

>                                     ResultSet rs =
> stmt.executeQuery("SELECT \"psdb \".\"public\".\"tt\".\"ans\" FROM
> \"psdb \".\"public\".\"tt\"");
>
> java.sql.SQLException: ERROR: syntax error at end of input
>
>
> Also, there is no problem with UPDATE or INSERT statements when the
> catalog is included.  Only in the SELECT statement.

Doubtful:

jurka=# insert into catalog.schem.tab.col values (1,2,3);
ERROR:  improper qualified name (too many dotted names):
catalog.schem.tab.col

> I did note that the driver does return false to
> connection.getMetaData().supportsCatalogsInDataManipulation(), so, there
> is actually no reason to expect that catalog qualifiers would be
> supported. However, as noted, it did work OK in 7.4 (even though the
> supportsCatalogsInDataManipulation() returned false there also).
>

This did not work in 7.4 either, it even gives a much better error message
than 8.0's "syntax error"

jurka=# select * from catalog.schem.tab;
ERROR:  cross-database references are not implemented


Kris Jurka

Kris Jurka <books@ejurka.com> writes:
> This did not work in 7.4 either, it even gives a much better error message
> than 8.0's "syntax error"

> jurka=# select * from catalog.schem.tab;
> ERROR:  cross-database references are not implemented

I'm not sure why 8.0 wasn't handling this properly --- I suspect that
the hack I used awhile ago to suppress a reduce/reduce conflict was
actually just confusing bison so much it didn't recognize that the
grammar was ambiguous.  Anyway, I've fixed it in CVS tip.

            regards, tom lane