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
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
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.
Joined: May 14, 2001
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