Switching databases over JDBC/ODBC

You can establish a new connection without dropping the old one. Thus you
can be connected to 2 databases at the same time. Just use the appropriate
connection for the database you want.

Bayless

----- Original Message -----
From: "Scott Bailey" <artacus@comcast.net>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Friday, May 29, 2009 6:34 PM
Subject: [GENERAL] Switching databases over JDBC/ODBC

conceptually if you consider the DB parameters which are necessary to establish a SQL connection
you would need to accomodate 'BOTH' sets of these DB parameters
JDBC spec does'nt address the need to 'switch' so Type4 JDBC driver writers concentrated on one connection to one database using a 'known' set of Database specific parameters

an interesting concept tho'

anyone?
Martin Gainty
______________________________________________
Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Ez az
üzenet bizalmas. Ha nem ön az akinek szánva volt, akkor kérjük, hogy
jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának
készítése nem megengedett. Ez az üzenet csak ismeret cserét szolgál és
semmiféle jogi alkalmazhatósága sincs. Mivel az electronikus üzenetek
könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet
ezen üzenet tartalma miatt.

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

Date: Fri, 29 May 2009 16:34:31 -0700
From: artacus@comcast.net
To: pgsql-general@postgresql.org
Subject: [GENERAL] Switching databases over JDBC/ODBC

Is there an equivalent to psql's \connect database or MySQL/MS SQL's use
database command that will work with JDBC? It doesn't seem like I should
need to drop the connection and establish a new one just to switch
databases.

Scott

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

_________________________________________________________________
Windows Live™: Keep your life in sync.
http://windowslive.com/explore?ocid=TXT_TAGLM_BR_life_in_synch_052009

Scott Bailey wrote:

Is there an equivalent to psql's \connect database or MySQL/MS SQL's use
database command that will work with JDBC? It doesn't seem like I should
need to drop the connection and establish a new one just to switch
databases.

the \connect command disconnects from the current database, and connects
to a new one, this causes a new postgres server process to be spawned to
replace the one servicing the old connection.

does '\connect' work with Type-IV JDBC Database?
can you effectively 'drop' the first DB connection
and somehow read a different set of DB parameters to connect to a new DB?

thanks,
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

Date: Fri, 29 May 2009 17:07:03 -0700
From: pierce@hogranch.com
To: artacus@comcast.net
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Switching databases over JDBC/ODBC

Scott Bailey wrote:

Is there an equivalent to psql's \connect database or MySQL/MS SQL's use
database command that will work with JDBC? It doesn't seem like I should
need to drop the connection and establish a new one just to switch
databases.

the \connect command disconnects from the current database, and connects
to a new one, this causes a new postgres server process to be spawned to
replace the one servicing the old connection.

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

_________________________________________________________________
Hotmail® has a new way to see what's up with your friends.
http://windowslive.com/Tutorial/Hotmail/WhatsNew?ocid=TXT_TAGLM_WL_HM_Tutorial_WhatsNew1_052009

Martin Gainty wrote:

does '\connect' work with Type-IV JDBC Database?
can you effectively 'drop' the first DB connection
and somehow read a different set of DB parameters to connect to a new DB?

\connect is a psql command, nothing to do with JDBC.

In Java, you would Close() a JDBC connection, then getConnection() to
create a new one.

true
scott was asking if you can utilise the same JDBC Type IV DB connection for both
the original DB connection and utilise that same connection to connect to another
DB (with a fresh set of database parameters)
my answer is i do not know of a way to use the same connection for 2 different DB's
but perhaps a vendor/contractor/consultant such as yourself may know of connection mechanism which would dynamically switch the original connection from old DB parameters to new DB parameters?

Martin Gainty
______________________________________________
Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Ez az
üzenet bizalmas. Ha nem ön az akinek szánva volt, akkor kérjük, hogy
jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának
készítése nem megengedett. Ez az üzenet csak ismeret cserét szolgál és
semmiféle jogi alkalmazhatósága sincs. Mivel az electronikus üzenetek
könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet
ezen üzenet tartalma miatt.

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

Date: Fri, 29 May 2009 18:24:10 -0700
From: pierce@hogranch.com
To: mgainty@hotmail.com
CC: artacus@comcast.net; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Switching databases over JDBC/ODBC

Martin Gainty wrote:

does '\connect' work with Type-IV JDBC Database?
can you effectively 'drop' the first DB connection
and somehow read a different set of DB parameters to connect to a new DB?

\connect is a psql command, nothing to do with JDBC.

In Java, you would Close() a JDBC connection, then getConnection() to
create a new one.

_________________________________________________________________
Hotmail® has a new way to see what's up with your friends.
http://windowslive.com/Tutorial/Hotmail/WhatsNew?ocid=TXT_TAGLM_WL_HM_Tutorial_WhatsNew1_052009

Martin Gainty wrote:

true
scott was asking if you can utilise the same JDBC Type IV DB
connection for both
the original DB connection and utilise that same connection to connect
to another
DB (with a fresh set of database parameters)
my answer is i do not know of a way to use the same connection for 2
different DB's
but perhaps a vendor/contractor/consultant such as yourself may know
of connection mechanism which would dynamically switch the original
connection from old DB parameters to new DB parameters?

and, for the 3rd time, one connection == one database. you close that
connection and open a new one to connect to a different database or as a
different user. thats how postgresql works, it has no other way of
doing that. this is independent of JDBC, its an inherent design
characteristic of the database server.

John R Pierce wrote:

Scott Bailey wrote:

Is there an equivalent to psql's \connect database or MySQL/MS SQL's use
database command that will work with JDBC? It doesn't seem like I should
need to drop the connection and establish a new one just to switch
databases.

the \connect command disconnects from the current database, and connects
to a new one, this causes a new postgres server process to be spawned to
replace the one servicing the old connection.

Well at work we've got Oracle, MySQL, MS SQL and Postgres. So I
generally use Aqua Data Studio because it works with all of them. For
MySQL and MS SQL you register a single connection to the server and can
switch to any database. But with Postgres, you have to register a new
connection for every database because you can't switch once connected.

I just figured that if even Microsoft can do it, surely we can.

Scott

Scott Bailey wrote:

Well at work we've got Oracle, MySQL, MS SQL and Postgres. So I
generally use Aqua Data Studio because it works with all of them. For
MySQL and MS SQL you register a single connection to the server and
can switch to any database. But with Postgres, you have to register a
new connection for every database because you can't switch once
connected.

I just figured that if even Microsoft can do it, surely we can.

you could always use SCHEMA instead of DATABASE if thats what you want.

btw, in Oracle, you need a different connection for different database
instances, too.

John R Pierce wrote:

Scott Bailey wrote:

Well at work we've got Oracle, MySQL, MS SQL and Postgres. So I
generally use Aqua Data Studio because it works with all of them. For
MySQL and MS SQL you register a single connection to the server and
can switch to any database. But with Postgres, you have to register a
new connection for every database because you can't switch once
connected.

I just figured that if even Microsoft can do it, surely we can.

you could always use SCHEMA instead of DATABASE if thats what you want.

btw, in Oracle, you need a different connection for different database
instances, too.

Well, that's true. The difference with Oracle is that you can only have
a single database per server instance. (Which is really dumb IMO) But
that being the case, schema are regularly used to partition the server
in Oracle. On DMBS's that support multiple databases you never see
SCHEMA being used this way.

Scott Bailey wrote:

John R Pierce wrote:

Scott Bailey wrote:

Well at work we've got Oracle, MySQL, MS SQL and Postgres. So I
generally use Aqua Data Studio because it works with all of them. For
MySQL and MS SQL you register a single connection to the server and
can switch to any database. But with Postgres, you have to register a
new connection for every database because you can't switch once
connected.

I just figured that if even Microsoft can do it, surely we can.

you could always use SCHEMA instead of DATABASE if thats what you want.

btw, in Oracle, you need a different connection for different database
instances, too.

Well, that's true. The difference with Oracle is that you can only have
a single database per server instance. (Which is really dumb IMO) But
that being the case, schema are regularly used to partition the server
in Oracle. On DMBS's that support multiple databases you never see
SCHEMA being used this way.

Well the point here is that at least in MySQL(maybe also in MSSQL no
idea about that one) a "database" is really much more like a schema in
PostgreSQL. In the former you basically have

instance -> databases -> objects(tables whatever) in the later you have
cluster -> databases -> schema -> objects.
In general the need to switch between several databases in the same app
is sometimes a sign that one should have used schemas instead during the
design phase.

Stefan