Re: [INTERFACES] multiple db connections

On Mon, 8 Mar 1999, John Frailey wrote:

> We are trying to port our pure java e-commerce software to postgres. I
> am using database pooling with jdbc during an init stage of the main
> servlet that dishes out the html content to the users. (we must use
> pooling, because it takes too darn long to get a connection, and we
> need our web pages to respond quickly)
>

I've always wondered about connection pooling (yes, I understand you don't
want to reconnect on every query. I know I don't) Wouldn't it work just as
well using a single connection to the database? Assuming the database
connection is written to handle multiple requests (ie being thread safe).

Or is it a matter of security/transactions that says each user has to have
his own connection?

> My problem is that we use multiple databases per web request. With
> other db's, I can make a connection to a central db, and then select
> .. from <database>.<table> and make sure permissions are such, that I
> can hop databases with just one connection I get from the pool.
>

Usually (as far as I know) "select from database.table" is not different
databases but different schemas, all handled by the same database.

For example in DB2 or Oracle
"select from urban.small_table"
and
"select from john.small_table"

can all be in the database 'test', with urban.small_table and
john.small_table being different tables.

If you can switch from the 'urban' schema to the 'john' schema I don't
know (I think so). If postgres has schemas ... I'm not sure, but the JDBC
driver suggests it doesn't.

But schemas are just a namespace thing (or?), if it is access rights you
want they could be implemented by using different tables with different
rights (urban_some_table and john_some_table), no?

I'm sure I'm missing some great finesse here, so please enlighten me :)

/Urban

---
Urban Widmark urban(at)svenskatest(dot)se
Svenska Test AB +46 90 71 71 23