Use, Set Catalog and JDBC questions

[Frank Cavaliero]

Hi,

I have an application that requires to connect to each database available
in PostgreSQL. I have the following questions:


1. Is there a USE DATABASE command or something of the sort (similar to
MySQL) that allows you to quickly connect to a database without having to
reconnect using the username,password and database again ? In Java, we
are using set catalog to do this in MySQL.

2. Based on #1 above, would the latest JDBC driver support the ability to
create this type of connection?


Thanks,
Frank
Database Administrator

[Adrian Klaver]

Post by Frank Cavaliero
Hi,
I have an application that requires to connect to each database
1. Is there a USE DATABASE command or something of the sort (similar to
MySQL) that allows you to quickly connect to a database without having
to reconnect using the username,password and database again ? In Java,
we are using set catalog to do this in MySQL.
2. Based on #1 above, would the latest JDBC driver support the ability
to create this type of connection?

Not sure if this will do what want?:
http://jdbc.postgresql.org/documentation/91/datasource.html#ds-intro

Post by Frank Cavaliero
Thanks,
Frank
/Database Administrator/

[Frank Cavaliero]

Hi Adrian,

Thanks for the response. The situation is more like the following:

Using the JDBC driver, I connect to database TEST1 and immediately,
without having to pass username credentials again, I want to use database
TEST2. In MySQL, you can simply run: use TEST2. Wondering if
PostgreSQL has something similar.

Thanks,
Frank









From:
Adrian Klaver <***@gmail.com>
To:
Frank Cavaliero/Boston/***@IBMUS
Cc:
pgsql-***@postgresql.org, pgsql-***@postgresql.org
Date:
02/25/2013 01:48 PM
Subject:
Re: [GENERAL] Use, Set Catalog and JDBC questions

Post by Frank Cavaliero
Hi,
I have an application that requires to connect to each database
1. Is there a USE DATABASE command or something of the sort (similar to
MySQL) that allows you to quickly connect to a database without having
to reconnect using the username,password and database again ? In Java,
we are using set catalog to do this in MySQL.
2. Based on #1 above, would the latest JDBC driver support the ability
to create this type of connection?

Not sure if this will do what want?:
http://jdbc.postgresql.org/documentation/91/datasource.html#ds-intro

Post by Frank Cavaliero
Thanks,
Frank
/Database Administrator/

[Adrian Klaver]

Post by Frank Cavaliero
Hi Adrian,
Using the JDBC driver, I connect to database TEST1 and immediately,
without having to pass username credentials again, I want to use
database TEST2. In MySQL, you can simply run: use TEST2. Wondering
if PostgreSQL has something similar.

You can do it in the psql client like this, though that will not help
with JDBC:

***@ford:~$ psql -d test -U postgres
psql (9.0.5)
Type "help" for help.

test=# \c production
You are now connected to database "production".
production=#


I do not use the JDBC driver much, but from what I read in the link I
sent you, you can set up a non-pooling DataSource to which you can add
predefined datasources and then switch as needed.

Post by Frank Cavaliero
Thanks,
Frank

[Frank Cavaliero]

Hi Adrian,

Thanks a lot! I will certainly look into the multiple datasources as an
option.


-Frank







From:
Adrian Klaver <***@gmail.com>
To:
Frank Cavaliero/Boston/***@IBMUS
Cc:
pgsql-***@postgresql.org, pgsql-***@postgresql.org
Date:
02/25/2013 02:16 PM
Subject:
Re: [GENERAL] Use, Set Catalog and JDBC questions

Post by Frank Cavaliero
Hi Adrian,
Using the JDBC driver, I connect to database TEST1 and immediately,
without having to pass username credentials again, I want to use
database TEST2. In MySQL, you can simply run: use TEST2. Wondering
if PostgreSQL has something similar.

You can do it in the psql client like this, though that will not help
with JDBC:

***@ford:~$ psql -d test -U postgres
psql (9.0.5)
Type "help" for help.

test=# \c production
You are now connected to database "production".
production=#


I do not use the JDBC driver much, but from what I read in the link I
sent you, you can set up a non-pooling DataSource to which you can add
predefined datasources and then switch as needed.

Post by Frank Cavaliero
Thanks,
Frank

[Adrian Klaver]

Post by Frank Cavaliero
Hi Adrian,
Thanks a lot! I will certainly look into the multiple datasources as
an option.

Just remember, as John pointed out, a MySQL database and a Postgres
database are not equivalent. You will not be able to do cross database
operations(with the core tools). If you want that then you will need to
use Postgres schemas instead. There is work going forward on a Postgres
foreign data
wrapper(http://www.postgresql.org/docs/devel/static/postgres-fdw.html)
that will allow cross database operations, but it is only in the
development code and is very basic at this point. There is also the
dblink module(http://www.postgresql.org/docs/9.2/static/dblink.html).

Post by Frank Cavaliero
-Frank

[k***@rice.edu]

Post by Frank Cavaliero
Hi Adrian,
Using the JDBC driver, I connect to database TEST1 and immediately,
without having to pass username credentials again, I want to use database
TEST2. In MySQL, you can simply run: use TEST2. Wondering if
PostgreSQL has something similar.
Thanks,
Frank

Hi Frank,

The "USE xxx;' is a non-standard MySQL extension to the SQL language. This
functionality is not available at the SQL layer and must be supported by
the connection application itself, I think. For example, you can use
"\c xxx" in psql to perform that function. If JDBC does not support it
natively, you would need to open a new connection to the new database.

Regards,
Ken