Interceptor to trace database calls

[john.darrah]

Hello,

I am looking to write a database trace interceptor for JBOSS specifically to trace Oracle database calls. The interceptor would work by basically enabling oracle trace for the connection being used for the database call. Right now, I can get this to work by hardcoding in a DataSource, retrieving the connection, enabling trace in the oracle connection and passing the invokation along. What I want to be able to do is grab a connection without having to hard code in any information. Is there a way I can accomplish this? I've looked at the ManagedConnectionInterceptor but can't see a way to get a connection.

Thanks,

John

[nickman]

John;

I guess you need to issue an Oracle specific call at the creation of the connection, no ? You can do this in the Data Source definition file (oracle-ds.xml). We do this to set the date format:

<new-connection-sql>Alter session SET NLS_DATE_FORMAT='mm/dd/yyyy'</new-connection-sql>

Perhaps that will work for you. If not, you could create a wrapper MBean for the DataSource. Initialize it with the JNDI location of the real data source and then bind it into JNDI at a different name space. You can request connections from your proxy, which will acquire the connection for you, but execute the required operation before it hands the connection back to you.

//Nicholas

[john.darrah]

Nicholas,

Thanks for your reply. I am a little new to jboss so forgive me if I am missunderstanding. I thought about using new-connection-sql but I wanted something more granular. What I really wanted to do was have an interceptor examine the incoming invocation and determine if the class / method was a method we wanted to trace. Methods would be flagged as interested via a properties file or mbean interface. What I wanted to do was pull the same connection out of jboss' connection pool the the calling method would use and turn on tracing. I wanted to go down the interceptor route so the tracing would work no matter what persistence code was being used (JDBC, CMP, Hibernate). The problem with writing a wrapper around DataSource is that it would require changes to application code which isn't feasable. Is there another way you can think of to accomplish what I'm trying to do?

Thanks,

John

"nickman" wrote:
John;

I guess you need to issue an Oracle specific call at the creation of the connection, no ? You can do this in the Data Source definition file (oracle-ds.xml). We do this to set the date format:

<new-connection-sql>Alter session SET NLS_DATE_FORMAT='mm/dd/yyyy'</new-connection-sql>

Perhaps that will work for you. If not, you could create a wrapper MBean for the DataSource. Initialize it with the JNDI location of the real data source and then bind it into JNDI at a different name space. You can request connections from your proxy, which will acquire the connection for you, but execute the required operation before it hands the connection back to you.

//Nicholas

[nickman]

John;

The wrapper will not change your application code. You are simply adding a new DataSource. So if your old DataSource was DsA, then you could rename it DsB and call your wrapper DsA.

Current
Application -------> DsA (DataSource)

Possible
Application -------> DsA (Wrapper) --------> DsB (DataSource)

As far as being able to selectively implement the tracing call, that would require a more sophisticated interceptor structure, I suppose. I am not sure that there is an efficient way to determine the exact method or class a request came from (without modifying the application code), except possibly to build a stack trace (by contriving an exception or something) and back-walking the stack elements. I am not sure how efficient that would by, but some nifty grepping might get you the runtime information you need to figure out if the tracing should be enabled or not.

You may also want to consider using P6. It is a wrapper around JDBC drivers that will give you a detailed log of all the SQL executed against a DataSource. It is independent of app server and persistence architecture (and its free.....).

Here is some sample output I found in our logs:

00:09:41,967 INFO [TP-Processor8] [p6spy] - 1073797781967|0|3454|statement|| Select <Some Secret SQL Here>

You can download p6Spy at http://www.p6spy.com/.

Good luck.

//Nicholas

[aloubyansky]

Have a look at this http://www.jboss.org/wiki/Wiki.jsp?page=StatisticsCollector

[rlopez]

look at IronEyeSQL with p6spy