This week's book giveaway is in the Design forum.
We're giving away four copies of Design for the Mind and have Victor S. Yocco on-line!
See this thread for details.
Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Connection pooling tricky question

 
Valery Anciaux
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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,
Val�ry
 
Andy Bowes
Ranch Hand
Posts: 171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
john guthrie
Ranch Hand
Posts: 124
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks,
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 ?
Val�ry
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
Jamie
 
Mike Snow
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Valery ,
How does Oracle OCI driver solve your problem?
thanks
Mike
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic