Thread: 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

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


> 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

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é

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. Check it out.

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é

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. Check it out.

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é

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. Check it out.

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