using DBMS_APPLICATION_INFO with Jboss

Question :

using DBMS_APPLICATION_INFO with Jboss,

Answer :

Does anyone have examples of how to use  package with JBOSS?

We have a various applications which run within JBOSS and share db pools. I would like, at the start of each session these applications to identify themselves to the database using DBMS_APPLICATION_INFO so I can more easily track which sections of the application is causing database issues.

I’m not too familiar with session life cycles in JBOSS, but at the end of the day, what needs to happen is at the start and end of a transaction, this package needs to be called.

Read More  Is there any list datatype in MySQL stored procedures, or a way to emulate them?

Has anyone done this before?

,

If you are using JBoss, you can use a “valid-connection-checker”.
This class is normaly used to check the validity of the Connection.
But, as it will be invoked every time the Connection pool gives the user a Connection, you can use it to set the DBMS_ APPLICATION _INFO.

You declare such a class in the oracle-ds.xml like this:

      jdbc/myDS      jdbc:oracle:thin:@10.10.1.15:1521:SID      oracle.jdbc.driver.OracleDriver      MyEncryptDBPassword      test.MyValidConn                Oracle9i          

Your class must implement the org.jboss.resource.adapter.jdbc.ValidConnectionChecker interface.
If you use Maven, you can include this interface with the following dependency:

      jboss      jboss-common-jdbc-wrapper      3.2.3      provided    

This interface has only one method: isValidConnection.
I copy my implementation:

public SQLException isValidConnection(Connection arg0) {      CallableStatement statement;      try {          statement = arg0.prepareCall("call dbms_application_info.set_client_info('"+getInfos()+"')");          statement.execute();      } catch (SQLException e) {          // TODO Auto-generated catch block          e.printStackTrace();      }      return null;  }  

Hope it helps !

Read More  Design: Java and returning self-reference in setter methods

Benoît

That’s the answer using DBMS_APPLICATION_INFO with Jboss, Hope this helps those looking for an answer. Then we suggest to do a search for the next question and find the answer only on our site.

Disclaimer :

The answers provided above are only to be used to guide the learning process. The questions above are open-ended questions, meaning that many answers are not fixed as above. I hope this article can be useful, Thank you