How to invoke JDBC setApplicationContext just after “getting” a connection from the pool

javaCaravan0
javaCaravan0 used Ask the Experts™
on
I need to set an Oracle application context. I found there is a method "public void setApplicationContext(String CLIENTCONTEXT, string attribute,string value) throws SQLException;" in JDBC to set values for the client's session. I'm using Spring 2.0 JDBC Template and connection pooling in JBOSS AS 4.0.3. There are more than 100 concurrenct users of the application. I need to pass atleast two parameters to oracle procedure whenever a connection is picked from the pool. Let say User A submits a request, I'll pass values for two parameters to the DAO class. I want to know how can I set the values when a connection is picked from the pool. Will I also need to invoke "clearAllApplicationContext(‘CLIENTCONTEXT’) " just before “returning” the connection to the pool? Please note, I want to set the values for the entire session therefore I am trying to find how can I do so when the connection is picked from the pool.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,
If it is a problem with proxy connection, you can check:
http://stackoverflow.com/questions/2777071/geeting-internal-oracle-connection-from-hibernate-in-jboss
If you don't find a solution, you still can st a context in a pl/sql statement:
http://www.experts-exchange.com/Programming/Languages/Java/Q_21096805.html
Regards,
Franck.

Author

Commented:
Thanks for the reply.
Please note, I'm using JBOSS connection pooling and jdbcTemplate pulls the connection using JNDI. Driver class is defined as follows in the xml file:
<driver-class>oracle.jdbc.OracleDriver</driver-class>
I have looked at "http://stackoverflow.com/questions/2777071/geeting-internal-oracle-connection-from-hibernate-in-jboss" and tried to call the "setApplicationContext" in my DAO class.
In my DAO class I have following method:
public CallableStatement createCallableStatement(Connection conn)
I added the following lines in this method:
      String valueToSet = "SPN";
      oracle.jdbc.driver.OracleConnection nativeOraConn = null;
      nativeOraConn=  (oracle.jdbc.driver.OracleConnection)conn;
      nativeOraConn.setApplicationContext("CLIENTCONTEXT", "userLang", valueToSet);
I'm getting the following error:
The method setApplicationContect(String,String,String) is undefined for the type OracleConnection

I've looked at classes12.zip and ojdbc.jar but couldn't find setApplicationContect method.
Questions:
1.  Where is setApplicationContect method? Am I using the right Oracle JDBC driver, I have tried both above mentioned drivers.
2. Is my approach i.e. adding the logic in the above method for  setting the application context right? I need to confirm this because I'm using connection pooling.

Thanks




Hi,
1.
It is an oracle bug in jdbc driver. If you have Oracle support, then check Metalink note 812945.1
Workaround is to cast the connection to oracle.jdbc.internal.OracleConnection where it is implemented.
2.
I'm sorry I don't know connection pooling enough to answer.
Regards,
Franck.
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Author

Commented:
Thanks for your valuable input. I'm going to try it the way it is explained in the link that you have mentioned and get back to you.

Author

Commented:
I have tried by adding the following code:

      connection = (oracle.jdbc.OracleConnection) conn;
      connection = connection.physicalConnectionWithin();
      nativeOraConn=  (oracle.jdbc.internal.OracleConnection) connection;
      nativeOraConn.setApplicationContext("CLIENTCONTEXT", "userLang", valueToSet);
 
I'm getting the following error:
Error in Java File java.lang.ClassCastException: org.jboss.resource.adapter.jdbc.WrappedConnection
Contact System Administrator

please explain.
I'm sorry I can't help more about java. I'm a database man...
If it were me, I would choose the second solution (pl/sql statement:
http://www.experts-exchange.com/Programming/Languages/Java/Q_21096805.html)

Hope you will have answer from java/jboss experts.

Regards,
Franck.

Author

Commented:

Spring handles the connections, a connection object/handle is provided to us. Using that connection, I execute oracle back-end procedure as follows:
CallableStatement cs = conn.prepareCall("{ call packageBody.procedureName(parameters separated by comma)}");
Can I do something like this: (is this what you mean?)
1. First execute the following statement:
CallableStatement cs =conn.prepareCall("{call dbms_session.set_nls('NLS_LANGUAGE','AMERICAN')}");
cs.execute();
2. Then execute the following:
CallableStatement cs = conn.prepareCall("{ call packageBody.procedureName(parameters separated by comma)}");
cs.execute();
My question is whether the connection be still available after executing cs.execute(); in step 1.
Yes of course connection is there and you're in the same session, same transaction.

Author

Commented:
ok .. thank you ... I'm going to test it and get back to you.

Author

Commented:
It works! thank you for the valuable feedbacks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial