Can't get list of databases with \list

[Christopher Frank-2]

I recently installed PostgreSQL 8.2.11-1 on my Windows machine for use with my java application. I need to get a list of the databases available so that the user can choose one. Issuing a query of "\list" using jdbc doesn't seem to work:

Connection connection = DriverManager.getConnection("jdbc:postgresql:postgres", "user", "pass");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("\\list");

which produces:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "\"

Why is this and how can I fix it?

[Tom Lane-2]

Christopher Frank <c.frank@...> writes:
> Issuing a query of "\list" using jdbc doesn't seem to work:

Nope, it won't, because \list is a psql-ism.

If you want to exactly duplicate that you could run psql with -E and
look at the queries it issues and copy them (but beware that they are
backend-version-dependent).  However, I was under the impression that
JDBC had its own set of standard metadata inquiry operations.  You'd
probably be better off using those if they cover what you need.

I'm not sure how many JDBC hackers hang out here; you might be better
advised to ask on the pgsql-jdbc list.

                        regards, tom lane

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

[Christopher Frank-2]

On Mon, Nov 2, 2009 at 2:30 PM, Tom Lane <tgl@...> wrote:

Christopher Frank <c.frank@...> writes:
> Issuing a query of "\list" using jdbc doesn't seem to work:

Nope, it won't, because \list is a psql-ism.

If you want to exactly duplicate that you could run psql with -E and
look at the queries it issues and copy them (but beware that they are
backend-version-dependent).  However, I was under the impression that
JDBC had its own set of standard metadata inquiry operations.  You'd
probably be better off using those if they cover what you need.

I'm not sure how many JDBC hackers hang out here; you might be better
advised to ask on the pgsql-jdbc list.

                       regards, tom lane

Thanks Tom. Your recommendation eventually led me to this thread. The query mentioned there is basically what \list does, just a little simpler. It looks like I can probably use that without worrying about different versions.

As for JDBC, there may be something that gives equivalent information, but I couldn't find it.

[Richard Broersma]

On Mon, Nov 2, 2009 at 12:34 PM, Christopher Frank <c.frank@...> wrote:

>> I'm not sure how many JDBC hackers hang out here; you might be better
>> advised to ask on the pgsql-jdbc list.
>>
>>                        regards, tom lane
>
> As for JDBC, there may be something that gives equivalent information, but I
> couldn't find it.

http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html

getCatalogs()


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

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

[Christopher Frank-2]

On Mon, Nov 2, 2009 at 3:51 PM, Richard Broersma <richard.broersma@...> wrote:

On Mon, Nov 2, 2009 at 12:34 PM, Christopher Frank <c.frank@...> wrote:

>> I'm not sure how many JDBC hackers hang out here; you might be better
>> advised to ask on the pgsql-jdbc list.
>>
>>                        regards, tom lane
>

> As for JDBC, there may be something that gives equivalent information, but I
> couldn't find it.

http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html

getCatalogs()


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

I had tried that but it only returned one database, the database to which I was already connected. There should have been 3 others.

[Richard Broersma]

On Mon, Nov 2, 2009 at 1:05 PM, Christopher Frank <c.frank@...> wrote:


> I had tried that but it only returned one database, the database to which I
> was already connected. There should have been 3 others.

That's strange, there was only one record returned to the resultset?


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

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