Re: Switching databases over JDBC/ODBC

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