aspose file tools*
The moose likes JDBC and the fly likes Identifying a new physical connection versus a logical connection in a jdbc connection pool Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Identifying a new physical connection versus a logical connection in a jdbc connection pool " Watch "Identifying a new physical connection versus a logical connection in a jdbc connection pool " New topic
Author

Identifying a new physical connection versus a logical connection in a jdbc connection pool

Fergal Colreavy
Greenhorn

Joined: Feb 03, 2012
Posts: 2
Hi,

I'm working on a number of services that connect to an Oracle db using a connection pool via oracle.jdbc.pool.OracleDataSource. What I'm trying to figure out is, when the getConnection() method is called, is it possible to identify whether the connection that is established to the db is a new physical connection or indeed an recycled logical connection? I was thinking of using AOP to capture the getConnection() event and verify if a new physical connection is made, I';m just wondering if this is even possible?

Thanks!
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

It sorts of depends on what you intend to do. In Oracle, you could use the DBMS_APPLICATION_INFO package to "mark" connections. Upon retrieving a connection from the pool, you'd check the client or module info. If it was not set, you'd know the connection is new, and subsequently set the info to a specific value of yours. A small benefit of this is that it help identify the connections of your application in the database and would be independent of the connection pool. The downside is that it is be Oracle specific.

If you just need to set up a new connection somehow (eg. alter NLS settings), an on-logon trigger might do the trick as well without a need to modify your application.
Fergal Colreavy
Greenhorn

Joined: Feb 03, 2012
Posts: 2
Thanks for your reply. To give some background, that I am trying to do is to set a session variable value using DBMS_SESSION.SET_CONTEXT, but only set it if a certain module is connecting to the db. I have two client applications connecting to the DB, using the same db credentials, but separate JDBC connection configurations. What I want to do is each time one of the client applications makes a physical connection to the pool (i.e. setting up a new session) to call my DBMS_SESSION.SET_CONTEXT stored proc to set a certain session variable value for that particular application calling the db. Rather than do this every time getConnection is called, I want it to call the stored proc each time a new physical connection is made via the connection pool
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Identifying a new physical connection versus a logical connection in a jdbc connection pool