Rushing to Judgement

The first part of my mini-series on Log4j and ColdFusion talked about CFLog4j and some basic background information. This post focuses on using CFLog4j and some custom-rolled Java to write logs to a database.

The JDBCAppender Object

Recent versions of Log4j include a JDBCAppender class that is used to write logs to a database. The class is able to create JDBC connections that are used for executing the log inserts. For stand-alone Java applications, this works well; however, one of the benefits of using ColdFusion is that the application server manages various resources for us, which include database connection pooling. Connection pooling allows the application server to manage a cache of connections to the database; by holding on to this pool of connections, the relatively expensive process of opening and closing connections happens less frequently. Additionally, the connection pooling options in ColdFusion provide tuning opportunities for security and performance from within the CF Administrator. It would be nice to avoid creating additional connections to the database outside of this managed pool.

As it turns out, by using libraries included with (and used by) ColdFusion, it is possible to use these managed database connections. Rather than create our own database connection for writing logs, which is all that JDBCAppender knows how to do, we can follow the recommendations outlined in the Javadoc for the class by creating a subclass with methods overriding getConnection() and closeConnection(). These methods will use the ColdFusion 8 library to retrieve a connection from the application server’s connection pool and return it when the log insert is finished. In this way we can stick to the established parameters of our CF datasources and avoid the peril associated with managing our own connections.

Creating our Custom JDBC Appender

I should start with a caveat - I am not a Java expert. Like many experienced CF developers, I have dabbled in Java over the years, mostly for integration projects, but I don’t claim that my solution is the “best” way to extend Log4j’s JDBCAppender for ColdFusion. In fact, I welcome any suggestions that can help me improve my design!

You will need to create a new java project in your IDE of choice that includes a source file for your custom JDBC appender. How you name the class is trivial, but in order for Log4j to find it you will want to place it in the package

org.apache.log4j.jdbc

In addition to any standard libraries included by default by your IDE, you will want to add build path references for:

• Log4j-1.2.15.jar (or whatever version you have decided to use)

• Cfusion.jar (typically located in coldfusion8\lib)

The two methods that must be overridden are getConnection() and closeConnection(). In addition I recommend creating a new datasource property along with getters and setters so that the Configurator object automatically populates this from the .properties file (we will get to that next). This way you don’t have to hardcode the datasource name in your class, making it more portable.

For reference I am including a working example, but I realize this could be written in a variety of ways depending on your coding style. Once you have created your Class, you will want to build the project and export it as a jar file. Make note of the location because you will need to make the file available to ColdFusion later.

package org.apache.log4j.jdbc;

import org.apache.log4j.jdbc.JDBCAppender;
import coldfusion.server.ServiceFactory;
import java.sql.*;

public class SubJDBCAppender extends JDBCAppender {
	
	protected String datasource = "";
	
	public String getDatasource() {
		return datasource;
	}
	
	public void setDatasource(String ds) {
		datasource = ds;
	}
	
	protected void closeConnection(Connection con) {
		
		if (con != null) {
			try {
				con.close();
			}
			catch (SQLException e) {
				throw new RuntimeException("Unable to close connection");
			}
		}
	}
	
	protected Connection getConnection() throws SQLException {
		
		if (connection == null) {
			// Use the ColdFusion ServiceFactory to retrieve a connection from the pool
			// This will return a JRun Connection Handle which is a subclass of Connection
			connection = ServiceFactory.getDataSourceService().getDatasource(getDatasource()).getConnection();
		}
		
		return connection;
		
	}
	

}

Properties File Differences

The RollingFileAppender is only used for writing to disk-based logs. Your new SubJDBCAppender is only used for writing to a database. These storage mediums have different constraints around their practical use. We will modify the CFLog4j.properties file to reflect these differences.

Both types of Appenders require that certain basic properties be configured in your properties file. Specifically, you will need to include configuration information for log4j.category.CFLog4j which will include the root category information. You’ve already seen how this is done for Log4j disk-based logging. In the JDBC example I suggest using this line:

log4j.category.CFLog4j=INFO,
jdbc

This describes a category of INFO (all messages of INFO level or higher will be logged) and a logger named jdbc.

Next you will need to specify the Appender class that should be used by Log4j. In this case we will be using the SubJDBCAppender class that you’ve written.

log4j.appender.jdbc=org.apache.log4j.jdbc.SubJDBCAppender

Both the RollingFileAppender and your SubJDBCAppender will make use of the same PatternLayout class. PatternLayout is a subclass of Layout, and allows the creation of customized formatting for the recording of log data using certain key characters. Full details of these characters is available in the Apache Logging Javadoc referenced earlier in these articles. I suggest leaving the log4j.appender.jdbc.layout property unchanged.

Now we get to the real differences between the two approaches. Basically everything else, other than what I point out above, can be deleted because there’s nothing like it in the database-based logging properties file. The rest of the file contains properties relevant only to logging via the SubJDBCAppender.

Remember when you wrote the code to set up the datasource property in your custom SubJDBCAppender? Here is where you put it to use. The Log4j framework uses various Configurator objects for importing declarative configuration preferences. The PropertyConfigurator will take the properties defined in the properties file and map them over to public properties on the Appender object, provided that the two have the same name. So, you will need to define the datasource in the properties file as follows:

log4j.appender.jdbc.datasource=
of your logging datasource>

The buffer size determines how frequently the Log4j framework will write entries out to the database. I prefer to set it at 1 so that every record is immediately committed. You may prefer something else but for experimenting, this is the way to go.

log4j.appender.jdbc.buffersize=1

You will have to specify the SQL statement that will be used to insert your logs. A typical insert statement looks something like this:

Log4j.appender.jdbc.sql=insert
into  (message, class, priority, log_date)
values (%m, %c, %p, %d{MM/dd/yyyy HH:mm:ss:SSS})

For reference I am including a working example properties file but you will need to create one that fits your purposes.

log4j.category.CFLog4j=INFO,jdbc

log4j.appender.jdbc=org.apache.log4j.jdbc.SubJDBCAppender

log4j.appender.jdbc.datasource=logger

log4j.appender.jdbc.layout=org.apache.log4j.PatternLayout

log4j.appender.jdbc.buffersize=1

log4j.appender.jdbc.sql=INSERT INTO BasicLog (message, class, priority, log_date) VALUES ("%m", "%c", "%p", "%d{yyyy-MM-dd HH:mm:ss}")

Other Configuration Details

Since you have exported the SubJDBCAppender as a jar file, you will need to place it somewhere so that ColdFusion can load it. You can do this either by copying it into the classpath or creating a new entry in the ColdFusion Administrator Java settings pointing to the jar file. I suggest the latter. In either case you will need to bounce the ColdFusion administrator for the changes to be available.

The table you will be logging into must also be created where it is accessible by the datasource you plan to use for writing the entries. It would be adequate to create a table with four columns for message, class, priority, and log date.

Once you have configured the ColdFusion server so that it can find your SubJDBCAppender.jar file and configured your database to include the required logging table, you will be ready to start logging to the database. I suggest using the same working test code from the disk-based CFLog4j example I talked about in my last post; that way you are starting with a functional Log4j implementation so you know you can revert back to it if necessary.

Limits of this Implementation

While the above process will work nicely to let you log to the database there are a few limitations that I am not fond of. I will be addressing both of these in my research and blogging about them in the future.

The first is that this approach is not going to work very well in a shared hosting environment, as you probably are not going to be able to modify the classpath used by ColdFusion, much less bounce the server. While I have some ideas about resolving this problem, I haven’t identified the best alternative yet.

The second problem relates to what is logged. While it is useful to log the information for which CFLog4j/Log4j are configured, this is not really adequate for my purposes. What if I want to track the user name who caused the logging code to be invoked, or their IP address? What if I want to track a specific Mach-II event or something else? I can stuff it all into the message field, but this gets crowded real fast, especially when we think about doing metrics and analytics using log data. As it turns out, Log4j provides a nice way to log additional custom properties and I will cover that in another post.

Until then, happy logging!

EDIT - I noticed that the properties file information I included was not 100% right.  Especially not for the sample text that is included in CFLog4j (has quotes, semicolons etc).  The updated properties will work fine in MySQL but in MSSQL you will need to 'SET QUOTED_IDENTIFIER OFF;' at the start of the SQL statement.