Posted by: Arthur Blake | 2010-06-23

Using log4jdbc with ColdFusion

log4jdbc has been an invaluable tool for me in the Java world for tracing and debugging SQL that my application runs in a development environment.

For a current job, I’ve been coding in ColdFusion (which is built on top of a Java stack), so I’ve added some special features to the latest release of log4jdbc to
make it easier to use in a Coldfusion environment as well.

Here are the basic steps to getting log4jdbc up and running on ColdFusion:

Locate your ColdFusion instance Java classpath folders for the ColdFusion instance(s) that you want to enable log4jdbc for.

On Windows, these folders would typically be in

C:\JRun4\servers\xxx\cfusion-ear\cfusion-war\WEB-INF\classes
C:\JRun4\servers\xxx\cfusion-ear\cfusion-war\WEB-INF\lib

Where xxx is the name of the ColdFusion instance folder that you’d like to modify (The default is cfusion if you just have a single instance.)

These folders could be in different places depending on the CF version, installation method and platform.

For simplicity, I’ll refer to these two folders as the classes folder and the lib folder in the instructions below:

We are going to be adding several jar and config files to these folders.

Download log4jdbc from http://code.google.com/p/log4jdbc/downloads/list

Any version of log4jdbc should work, but starting with the 1.2 beta 2 release, I’ve added some special features to log4jdbc that make it considerably easier to use in a ColdFusion environment.

If you are running the latest version of ColdFusion (ColdFusion 9 at the time of this writing) then you are on a Java 6 platform, so you should use the JDBC 4 version of log4jdbc.

Earlier versions of ColdFusion based on JDK 4 or 5 will need the JDBC 3 version.

Place the jar file for log4jdbc into the lib folder.

For my setup, I decided to use logback for the logging system.  You could also use log4j or any of the other slf4j supported logging systems if you want, but these instructions assume logback.

Download the latest version of slf4j and any other additional jar files for your logging system and place the appropriate jar files into the lib folder.

In my case, for using logback, I placed slf4j-api-1.6.0.jar, logback-core-0.9.21.jar and logback-classic-0.9.21.jar into the lib folder.

The download pages where these can be found are: http://slf4j.org/download.html and http://logback.qos.ch/download.html

Next, I configured log4jdbc and logback:

Both libraries will look in the classpath for configuration files, so first I created log4jdbc.properties in the classes folder:

!
! see http://code.google.com/p/log4jdbc/
!
log4jdbc.auto.load.popular.drivers=false
log4jdbc.dump.sql.maxlinelength=0

! suppress annoying exception that CF produces constantly
log4jdbc.suppress.generated.keys.exception=true


This is one of the features in log4jdbc that was added for ColdFusion- that is, the ability to configure it from a properties file rather than system properties.

It is not readily obvious how to change system properties for the JVM in CF (although it’s certainly possible.)  Besides, this way of configuring log4jdbc is much more convenient, even in the Java world.

The last option in the properties file is another option I added for ColdFusion:  Setting log4jdbc.suppress.generated.keys.exception to true suppresses an annoying exception that ColdFusion otherwise constantly generates silently, thus clogging up the log files with useless information.

I also turn off the auto loading of popular drivers which isn’t necessary because Coldfusion will already load those for you.

Next I created a logback.xml file in the classes folder to configure logback:

<?xml version="1.0" encoding="UTF-8" ?>

<!-- An example log4j configuration xml file for logback  -->
<!-- Logging levels are:                                  -->
<!-- TRACE, DEBUG, INFO, WARN, ERROR, ALL or OFF          -->

<!-- See http://logback.qos.ch/manual/ for logback manual -->

<!--  scan for changes to this configuration file, once a minute -->
<configuration scan="true" scanPeriod="10 seconds">

<appender name="sql">
<file>/logs/log.sql</file>
<append>false</append>
<encoder>
<!-- See http://logback.qos.ch/manual/layouts.html#ClassicPatternLayout -->
<pattern>%n--------  %date  --------%n%msg%n</pattern>
</encoder>
</appender>

<!-- log4jdbc sql & jdbc logging -->
<logger name="jdbc.sqlonly" additivity="false">
<level value="INFO" />
<appender-ref ref="sql" />
</logger>

<logger name="jdbc.sqltiming" additivity="false">
<level value="OFF" />
</logger>

<logger name="jdbc.audit" additivity="false">
<level value="OFF" />
</logger>

<logger name="jdbc.resultset" additivity="false">
<level value="OFF" />
</logger>

<logger name="jdbc.connection" additivity="false">
<level value="OFF" />
</logger>

<logger name="log4jdbc.debug" additivity="false">
<level value="DEBUG" />
<appender-ref ref="sql" />
</logger>

<root level="debug">
<appender-ref ref="sql" />
</root>

</configuration>


This is a simple logging configuration file that just logs the SQL that is produced to a single log file named log.sql in the C:\logs folder (make sure to create the C:\logs folder too!)

Additionally, the log4jdbc.debug log is turned on to the same log file so I can see the log4jdbc startup preamble log messages.

Next you need to modify the datasources for which you want to log the SQL:

Go to the datasources section in the ColdFusion administrator for the instance(s) you are changing.  In my case, I kept the original datasource around and then created a new datasource with _log4jdbc appended to the name.

Then in my CF code, I changed the datasource I was referencing.  This was so I could easily switch between the two.

CF doesn’t readily expose the actual internal JDBC driver that it uses for common database types, so I had to figure that out via some sleuthing.

You have to set up your datasource as a type “other” so that you can directly specify that your JDBC driver is a log4jdbc driver.

So in my case, I am using SQL Server, so I figured out (through looking inside some jar files and other trial and error) that the JDBC URL equivalent for my datasource. I created a data source of type ‘other’ and specified a URL like:

jdbc:log4jdbc:macromedia:sqlserver://host\instance:1433;databaseName=dbname

You can also load and use a directly specified, fully qualified class name for a JDBC driver from a vendor (for example, just use the Microsoft or JTDS driver on SQL Server), but I’ve found that there are some quirks in the way it handles some issues that may require you to change some code to get things working properly (not an option on the huge code base I am working with.)

Note that the jdbc:log4 part at the beginning of the JDBC URL is what makes the magic happen so that log4jdbc wraps the driver.

The URL will be different for other database types of course, and I haven’t had the need to figure out the correct URL for those types yet, so you might have to do some sleuthing to figure that out…

Set the Driver Class to net.sf.log4jdbc.DriverSpy, fill in the username and password as required by your database and you can leave the rest of the datasource settings blank.

Once this is done, restart your CF instance service so that the new jars and classpath changes will be recognized, and you should be ready to go.

SQL will be logged to the C:\logs\log.sql file.

On windows, the tail command from cygwin is particularly useful to monitor the log from a command prompt e.g. tail -f c:\logs\log.sql

I named my log file with a .sql extension on purpose so that I get syntax highlighting of the SQL in my favorite text editor for free.

You can and should of course customize this setup to your hearts content.

Read the log4jdbc manual at http://code.google.com/p/log4jdbc/ for more information on all the options and possibilities.

Good luck and I hope this works for you and helps you out as much as it has for me!


Responses

  1. Great job, Arthur! This will be immensely helpful in a number of areas.

    thanks,
    bill


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: