This is how we solved appending to a DB. We send a JMS message to a topic. Then have a Message Bean listen to the topic and the bean inserts the logging statement into the database. The bean also resends the message out for anything configured for the bean (Chainsaw in particular only is setup to listen to this bean). For the most part this has been successful.
Benefits of this are: 1) You decouple the logging to database (slow) from the application. This way your app can simply spit the message to the JMS provider and keep on going, no need to wait for the database transaction to complete. We have taken this a step farther and have a completely seperate application with only the MessageBean that sits on it's own dedicated server. Thus, lots of applications send to this topic and this bean keeps busy all day shoving the information into the database. 2) You get some fail-over options. Since JMS allows setting a message to be notified upon delivery and upon consumption you can configure this for some pretty good fail-over. 3) You can have more than one thing listen to a topic, thus your logs can be pushed out to multiple subscribers and processed differently for each subscriber. This is simply an alternative approach to a strict DBAppender. Chow, Aaron > -----Original Message----- > From: Evans Mark-PT1167 [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 03, 2005 11:48 AM > To: 'Log4J Users List' > Subject: How to use DBAppender, hsqldb.sql issues > > > I figured out how to programmatically create a DBAppender and > write log messages to a database. See the sample code (far) > below. In a nutshell, I had to get the DBPerfTest() working > first. Then I worked to get the programmatic version > working. Who knew you had to call activateOptions() on the > appender and connection source? A few more comments in the > javadocs could have saved me a day of work. I'm really > looking forward to a beta or first release of 1.3 because it > has some very cool features. I hope my experience > (documented below) will help others. > > I had a problem with the Hypersonic table creation script, > org/apache/log4j/db/dialect/hsqldb.sql. It contains # > characters as comments. HSQLDB uses // or /* */ for > comments. It also drops the 3 logging tables. This causes > an error the first time executed since the tabled don't > exist. They should have an "IF EXISTS" at the end. Or, they > could be removed from this file and moved to a > "drop_hsqldb.sql" file to prevent accidental loss of data. > Also, the hsqldb.sql file is missing caller_filename, > caller_class, caller_method, and caller_line from the > logging_event table. I'm not sure what their datatypes > should be but I made them VARCHAR (except caller_line which I > made CHAR(4)). I looked at the Oracle script to make my > guesses. Perhaps a DB person can clean that up. See my > updated version below. > > In order to get this thing going, I had to run the sample > from the tests/src/java/org/apache/log4j/db/ and > tests/input/db/ areas. These are in CVS. To access these > files from a browser, go to > http://cvs.apache.org/viewcvs.cgi/logging-log4j/ then browse > down the tests path. I used the driver manager > append-with-drivermanager1.xml linked from here > http://cvs.apache.org/viewcvs.cgi/logging-log4j/tests/input/db > /. The config file references a file input/db/db.properties. > That file does not exist. I figured out that it contains > key=value property pairs used in the > append-with-drivermanager1.xml file. My sample contains: > > driverClass=org.hsqldb.jdbcDriver > url=jdbc:hsqldb:hsql://localhost/xdb > user=sa > password= > > Of course, if you want to run the > tests/src/java/org/apache/log4j/db/DBPerfTest.java program to > test logging messages to a database, you have to either run > the test in Junit, or whip up your own quickie call frame. > Here's what I added to my hacked version of DBPerfTest to run > without JUnit (easier to run in JBuilder or some such dev > environment): > > public static void main(String[] args) { > try { > DBPerfTest dbPerfTest = new DBPerfTest("xyz"); > dbPerfTest.setUp(); > dbPerfTest.testLoop(); > dbPerfTest.tearDown(); > } > catch (Exception e) { > System.err.println("Exception!"); > e.printStackTrace(); > } > } > > > Here's the relevant portion of the DBAppender test program: > > package logservertest; > > import org.apache.log4j.Logger; > import java.sql.Connection; > import java.sql.DriverManager; > import org.apache.log4j.db.DBAppender; > import org.apache.log4j.db.DriverManagerConnectionSource; > > public class JdbcTest { > static Logger logger = Logger.getLogger(JdbcTest.class); > > public JdbcTest() { > } > > public static void main(String[] args) { > > org.apache.log4j.PropertyConfigurator.configure(System.getProp > erty("log4j.configuration")); > > DriverManagerConnectionSource connectionSource > = new DriverManagerConnectionSource(); > > connectionSource.setDriverClass("org.hsqldb.jdbcDriver"); > > connectionSource.setUrl("jdbc:hsqldb:hsql://localhost/xdb"); > connectionSource.setUser("sa"); > connectionSource.setPassword(""); > connectionSource.activateOptions(); // DON'T > FORGET TO ACTIVATE CONNECTION SOURCE! > > DBAppender dbAppender = new DBAppender(); > dbAppender.setLocationInfo(true); > dbAppender.setConnectionSource(connectionSource); > dbAppender.activateOptions(); // DON'T FORGET > TO ACTIVATE APPENDER! > > logger.addAppender(dbAppender); > > JdbcTest jdbcTest = new JdbcTest(); > > jdbcTest.doit2(); > } > > public void doit2() { > logger.debug("Debug message."); > logger.info("Info message."); > logger.warn("Warn message."); > logger.error("Error message."); > logger.fatal("Fatal message."); > } > } > > > My modified hsqldb.sql from org.apache.log4j.db.dialect: > > // This SQL script creates the required tables by > // org.apache.log4j.db.DBAppender and org.apache.log4j.db.DBReceiver. > // > // > // It is intended for HSQLDB. > // > > DROP TABLE logging_event_exception IF EXISTS; > DROP TABLE logging_event_property IF EXISTS; > DROP TABLE logging_event IF EXISTS; > > > CREATE TABLE logging_event > ( > sequence_number BIGINT NOT NULL, > timestamp BIGINT NOT NULL, > rendered_message LONGVARCHAR NOT NULL, > logger_name VARCHAR NOT NULL, > level_string VARCHAR NOT NULL, > ndc LONGVARCHAR, > thread_name VARCHAR, > reference_flag SMALLINT, > caller_filename VARCHAR, // New. M. Evans > caller_class VARCHAR, // New. M. Evans > caller_method VARCHAR, // New. M. Evans > caller_line CHAR(4), // New. M. Evans > event_id INT NOT NULL IDENTITY > ); > > > CREATE TABLE logging_event_property > ( > event_id INT NOT NULL, > mapped_key VARCHAR(254) NOT NULL, > mapped_value LONGVARCHAR, > PRIMARY KEY(event_id, mapped_key), > FOREIGN KEY (event_id) REFERENCES logging_event(event_id) > ); > > CREATE TABLE logging_event_exception > ( > event_id INT NOT NULL, > i SMALLINT NOT NULL, > trace_line VARCHAR NOT NULL, > PRIMARY KEY(event_id, i), > FOREIGN KEY (event_id) REFERENCES logging_event(event_id) > ); > > > -----Original Message----- > From: Evans Mark-PT1167 > Sent: Tuesday, February 01, 2005 4:56 PM > To: [email protected] > Subject: How do I use DBAppender? > > > I've searched high and low on how to use DBAppender. All I > can find is an email here > http://java2.5341.com/msg/79650.html whose responses say to > look at examples at tests/src/java/org/apache/log4j/db/ and > tests/input/db/. Neither of these exist in the log4j 1.3 > alpha 6 distribution. Where can I find these files and where > can I find info on how to configure DBAppender? > > Thanks, > Mark Evans > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] >
