JDBC logging with P6spy and CFMX

A couple of years ago I was looking at using the open source framework P6spy to log JDBC statements sent to the driver from CFMX. Turned out that the database I was using at the time, Ca Ingres had a built in tool to do more or less the same thing, but I did get P6spy up and running. A colleague recently asked how I got it working so I'm documenting it here for anyone who is interested. Unfortunately work on the project seemts to have slowed down but there is still plenty of interest and hopefully it will be revived soon:

Place the P6spy jar on the class path, on the J2EE version you can place it under the cfusion lib directory:

<jrunhome>\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\lib

On the standard edition (which I don't use much) You should be able to enter the path to the jar file (including the name of the jar) in the Coldfusion administrator however I couldn't get it to work (despite re-starting the server) and resorted to placing it in the cfusionMX/lib directory and cycling the server.

You then need to setup the datasource. Create a new datasource but select 'other' as the driver option. Enter the following as the driver name:

com.p6spy.engine.spy.P6SpyDriver
(note that the properties file bundled with P6spy incorrectly states the name of driver as com.p6spy.engine.P6SpyDriver) .

You will need to enter the JDBC url, for SQL server this is something along the lines of:

jdbc:macromedia:sqlserver://servername:port; databaseName=mediquote; SelectMethod=direct; sendStringParametersAsUnicode=false; MaxPooledStatements=1000

If you're not sure what it should be, go into the coldfusion settings page in the administrator and scroll down till you find the settings for your existing datasource, you can then copy the JDBC url from there. You'll need to enter the rest of the datasource details - username/password etc.

Once you hit submit, if you get an error along the lines of ...

[]java.sql.SQLException: No suitable driver available for <yourdatasource>, please check the driver setting in resources file, error: com.p6spy.engine.P6SpyDriver
The root cause was that: java.sql.SQLException: No suitable driver available for <yourdatasource>, please check the driver setting in resources file, error: com.p6spy.engine.P6SpyDriver

It is likely that either it cannot find the P6spy jar/You haven't re-started cf after moving it/your jdbc url is incorrect.

If it worked you should get another error indicating that spy.properties cannot be found:

Connection verification failed for data source: <yourdatasource>
[]java.sql.SQLException: spy.properties not found in classpath
The root cause was that: java.sql.SQLException: spy.properties not found in classpath

This is the properties file included as part of the download which needs to be placed in the classpath - I've found that this has to be in one of the places mentioned above (cfusionMX/lib or cfusion-ear\cfusion-war\WEB-INF\lib ) to be picked up and is ignored if added to the classpath via the coldfusion administrator in the standard edition.

Finally you need to change the following (documented) properties in the spy.properties file

realdriver=macromedia.jdbc.MacromediaDriver - (or name of the driver you wish to use)
logfile = C:\spy.log - (path to the log file, although for Jrun it seems to ignore this and place spy.log in the Jrun/bin folder or CfusionMX root folder depending on what version of coldfusion you are using).

There are a number of other properties which allow you to filter what is logged, log only statements against particular tables (useful for auditing), log statements which match a regex and exclude/include categories of statements: error, info, batch, debug, commit, rollback etc. If you are familiar with log4j you can also utilise log4j and have it logged to any appender including the console.

Feel free to bug me if you have any issues getting it working.

links: digg this    del.icio.us    technorati    reddit