EJB Gurus - switching between DB schemas

[Alex Molochnikov]

Our application lets the client see the list of schemas from the given
connection, and lets the user form SQL queries that may involve multiple
schemas.

If this application were re-engineered as an stateful session bean, how
would it handle the connections? I can see two scenarios:

1. Provide a number of schema-specific DB connection descriptors, one for
each schema, and use the connection appropriate for the query.

2. Provide one communal connection descriptor (without the schema), and set
the connection to the required schema when needed, through the Connection's
setCatalog(<schema>) method.

I can see the downside in each of these two approaches (A):

A1. Too inflexible - what if a schema is added/removed from the DB? The
descriptors will have to be changed manually, and the bean redeployed.

A2. Possible conflict with other beans - what if some other bean tries to
use the same connection object with a different setCatalog() argument,
targeting the wrong schema, while the first bean is still working its way
through querying the DB?

It is entirely possible that I am missing some important functionality in
EJB that addresses this issue, since I am still learning the EJB technology.
Your input will be greatly appreciated.

Alex Molochnikov
Gestalt Corporation

 

[ClimberBear]

Hi,

For your problem, I have always used database SYNONIMS, and works fine. My
problems comes from a unique conceptual database / model deployed in several
schemas (normally Oracle, but also DB2).

From your options

A1: If you use data-source references you have not to redeploy (only
stop-start), at last in WebSphere / OAS / WebLogic

A2: I don't see the problem. setCatalog affect to ALL conections, or only to
the current connection you have retrieved from the pool?

 

[Alex Molochnikov]

A2: I don't see the problem. setCatalog affect to ALL conections, or only to
the current connection you have retrieved from the pool?

setCatalog works on the current connection. But when the bean gets the
connection, it does not have an exclusive use of it. Some other bean could
also be handed the same connection at the same time, and if the bean A sets
the schema to "Customers", while the bean B is designed to work with
"Products", what is going to happen to the queries in either bean?

At the present time, the client part of our application uses a dedicated
server process (one client - one server, running in its own VM), so there is
no conflict: each client is essetially serviced by its own database
connection. I am trying to determine what problems will arise from running
all servers (beans) in a single VM, and using different schemas in different
beans is one of these problems.

Alex.