File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Connection pooling tricky question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Connection pooling tricky question" Watch "Connection pooling tricky question" New topic

Connection pooling tricky question

Valery Anciaux
Ranch Hand

Joined: May 14, 2001
Posts: 45
We are currently not using connection pooling in our applications, but are pushing the use of CMP EJB, and so, the use of connection pooling as well.
The Oracle DBA is reluctant to use such a means because of the consequences of connection pooling if the Oracle database is not aware of the real userid behind a connection.
They would like us to investigate further in finding a solution whereby web technology is combined with keeping the knowledge of the end-user, at Oracle level, for example.
I am aware that using connection pooling is always depriving the Oracle DBA from specific auditing information about the real user logged in the database, but I am trying to find a solution that suits both DBA and the development team.

Is there another way to use CMP without connection pooling ? For example, by using a self made connector as a datasource ?
Thanks in advance,
Andy Bowes
Ranch Hand

Joined: Jan 14, 2003
Posts: 171
Hi Val�ry
There really isn't a good way to do what your DBA want's. The only way to do this is to create Connections to the database that are specific to the client that log's into your application. This would also mean that you need a database account defined for each end-user which would be a huge nightmare to manage and a potential security hole.
The performance hit of making and dropping connections is considerable which is why DB connection pooling was introduced.
The way that we tend to do this is to have a single DB user but our framework will automatically set a column called 'last modified by' with the userid of the actual end user.
If your DBA insists on separate DB user accounts he is really still living in the old 2-tier client server era. You can also expect a large bill from your DB vendor to provide you with enough licences to support several hundred concurrent DB Users.

Andy Bowes<br />SCJP, SCWCD<br />I like deadlines, I love the whoosing noise they make as they go flying past - Douglas Adams
john guthrie
Ranch Hand

Joined: Aug 05, 2002
Posts: 124
it's been a while since i played with this stuff (maybe 3 years), but i remember sybase having a method of setting a userid from within a connection for precisely this reason.
and i seem to remember that sybase was chasing an oracle feature at the time, meaning that this could be done in oracle as well. have your dba do some research on this. from what i remember it wasn't a jdbc thing, but something actually inthe oracle server.
sorry to be so vague.
Valery Anciaux
Ranch Hand

Joined: May 14, 2001
Posts: 45
Well, it seems that new Oracle OCI driver solves the problem is we use an Oracle connection pool.
Now, the question is : How to use an Oracle connection pool with BEA Weblogic server, as it is the application server we use ?
Does the J2EE specification authorize the use of a custom datasource (ie, a datasource that refers to a custom connection pool) while the application server already provides its own implementation of connection pooling ?
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

These methods are available to associate a user with a connection. Have a read through Instantiating OracleConnectionCacheImpl and Setting Properties. There is a lot of other information on Connection pooling there too, if you want to read through it.
Mike Snow

Joined: May 07, 2003
Posts: 1
Hi Valery ,
How does Oracle OCI driver solve your problem?
I agree. Here's the link:
subject: Connection pooling tricky question
It's not a secret anymore!