How to configure a JDBC Appender for Red Hat Fuse 7 with Karaf

How to configure a JDBC Appender for Red Hat Fuse 7 with Karaf

For some integration projects it is helpful to persist application logs in a relational database instead of a plain log file. In this article, I show how to configure a JDBC appender for Red Hat Fuse 7 running in an Apache Karaf environment. There are instructions that describe how to persist messagess with PostgreSQL. Instead, I will show how to setup a JDBC appender for Oracle Database 11g.

I have tested this procedure with Oracle Database 11g Express Edition. A major difference I found is with the table syntax and the fact that the Oracle Database 11g sequence and trigger were required to auto-generate the primary key. Hence, users of Oracle Database 11g should find this article useful.

1. Create an org.ops4j.datasource-oracleds.cfg file in the FUSE_HOME/etc folder with the following content:

databaseName=xe
user=SYSTEM
password=administrator
dataSourceName=oracleds
url=jdbc:oracle:thin:@192.168.1.10:1521/xe
osgi.jdbc.driver.class=oracle.jdbc.OracleDriver

2. Install the Oracle driver from the Karaf terminal using the following command:

 osgi:install -s wrap:mvn:com.oracle/ojdbc6/11.2.0.3

3. Install these features:

feature:install jdbc jndi pax-jdbc-oracle

4. Check service:list for the DataSourceFactory object:

karaf@root()> service:list DataSourceFactory
[org.osgi.service.jdbc.DataSourceFactory]
-----------------------------------------
 osgi.jdbc.driver.class = oracle.jdbc.OracleDriver
 osgi.jdbc.driver.name = wrap_mvn_com.oracle_ojdbc6_11.2.0.3
 osgi.jdbc.driver.version = 0.0.0
 service.bundleid = 223
 service.id = 259
 service.scope = singleton
Provided by : 
 wrap_mvn_com.oracle_ojdbc6_11.2.0.3 (223)
Used by: 
 OPS4J Pax JDBC Config (239)

[org.osgi.service.jdbc.DataSourceFactory]
-----------------------------------------
 osgi.jdbc.driver.class = oracle.jdbc.OracleDriver
 osgi.jdbc.driver.name = oracle
 service.bundleid = 237
 service.id = 246
 service.scope = singleton
Provided by : 
 OPS4J Pax JDBC Oracle Driver Adapter (237)
Used by: 
 OPS4J Pax JDBC Config (239)

5. Check service:list for the DataSource object:

 karaf@root()> service:list DataSource
[javax.sql.DataSource]
----------------------
 databaseName = xe
 dataSourceName = oracleds
 felix.fileinstall.filename = file:/home/cpandey/NotBackedUp/Development/RedHat_Fuse_Folder/fuse7_2/fuse-karaf-7.2.0.fuse-720035-redhat-00001/etc/org.ops4j.datasource-oracleds.cfg
 osgi.jdbc.driver.class = oracle.jdbc.OracleDriver
 osgi.jndi.service.name = oracleds
 password = administrator
 pax.jdbc.managed = true
 service.bundleid = 239
 service.factoryPid = org.ops4j.datasource
 service.id = 261
 service.pid = org.ops4j.datasource.a6fadb91-16e5-43bb-8990-bbbaa553be8d
 service.scope = singleton
 url = jdbc:oracle:thin:@192.168.1.10:1521/xe
 user = SYSTEM
Provided by : 
 OPS4J Pax JDBC Config (239)
Used by: 
 OPS4J Pax Logging - Log4j v2 (8)

6. Create the event table:

CREATE TABLE "SYSTEM"."EVENTS" 
   (	"EVENT_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"EVENT_DATE" TIMESTAMP (0), 
	"EVENT_LEVEL" VARCHAR2(5), 
	"EVENT_SOURCE" VARCHAR2(128), 
	"EVENT_THREAD_ID" VARCHAR2(128), 
	"EVENT_MESSAGE" VARCHAR2(1024), 
	 CONSTRAINT "EVENT_ID_PK" PRIMARY KEY ("EVENT_ID"));

The table create statement can also be executed from the Karaf terminal:

karaf@root()> jdbc:execute oracleds 'CREATE TABLE "EVENTS" ("EVENT_ID" NUMBER(10,0) NOT NULL ENABLE, "EVENT_DATE" TIMESTAMP (0), "EVENT_LEVEL" VARCHAR2(5), "EVENT_SOURCE" VARCHAR2(128), "EVENT_THREAD_ID" VARCHAR2(128), "EVENT_MESSAGE" VARCHAR2(1024), CONSTRAINT "EVENT_ID_PK" PRIMARY KEY ("EVENT_ID"))'

I find doing that way is very useful. It would also test whether the datasource is set correctly or not.

7. In the database, we need to create a sequence and trigger.

Sequence:

create sequence events_seq start with 1 increment by 1;

Trigger:

create or replace trigger events_seq_tr
 before insert on events for each row
 when (new.event_id is null)
begin
 select events_seq.nextval into :new.event_id from dual;
END;

Note the terminating semicolon in the trigger.

8. In FUSE_HOME/etc/org.ops4j.pax.logging.cfg, set the following:

log4j2.rootLogger.appenderRef.JdbcAppender.ref = JdbcAppender

log4j2.appender.jdbc.type = JDBC
log4j2.appender.jdbc.name = JdbcAppender
log4j2.appender.jdbc.tableName = EVENTS
log4j2.appender.jdbc.cs.type = DataSource
log4j2.appender.jdbc.cs.jndiName = osgi:service/oracleds
log4j2.appender.jdbc.c1.type = Column
log4j2.appender.jdbc.c1.name = EVENT_DATE
log4j2.appender.jdbc.c1.isEventTimestamp = true
log4j2.appender.jdbc.c2.type = Column
log4j2.appender.jdbc.c2.name = EVENT_LEVEL
log4j2.appender.jdbc.c2.pattern = %level
# setNString vs setString
log4j2.appender.jdbc.c2.isUnicode = false
log4j2.appender.jdbc.c3.type = Column
log4j2.appender.jdbc.c3.name = EVENT_SOURCE
log4j2.appender.jdbc.c3.pattern = %logger
log4j2.appender.jdbc.c3.isUnicode = false
log4j2.appender.jdbc.c4.type = Column
log4j2.appender.jdbc.c4.name = EVENT_THREAD_ID
log4j2.appender.jdbc.c4.pattern = %thread
log4j2.appender.jdbc.c4.isUnicode = false
log4j2.appender.jdbc.c5.type = Column
log4j2.appender.jdbc.c5.name = EVENT_MESSAGE
log4j2.appender.jdbc.c5.pattern = %message
log4j2.appender.jdbc.c5.isUnicode = false

9. Test using this command:

karaf@root()> log:log "hey test 123"

10. Red Hat Fuse log you would get:

2019-01-04 19:26:46,098 | INFO  | pipe-log:set INFO    | o.a.k.l.core                     | 135 - org.apache.karaf.log.core - 4.2.0.fuse-720061-redhat-00001 | hey csp 123
2019-01-04 19:29:18,273 | INFO  | g:log "hey test 123" | o.a.k.l.core                     | 135 - org.apache.karaf.log.core - 4.2.0.fuse-720061-redhat-00001 | hey test 123

11. In the database, execute select * from events;:

EVENT_ID EVENT_DATE           EVENT_LEVEL   EVENT_SOURCE                 EVENT_THREAD_ID        EVENT_MESSAGE 
199	2019-01-04 19:25:42	INFO	org.apache.karaf.log.core	pipe-log:set INFO	hey csp
200	2019-01-04 19:26:08	INFO	org.apache.karaf.log.core	pipe-log:set INFO	hey csp123
201	2019-01-04 19:26:46	INFO	org.apache.karaf.log.core	pipe-log:set INFO	hey csp 123
202	2019-01-04 19:29:18	INFO	org.apache.karaf.log.core	pipe-log:log "hey test 123"	hey test 123

That’s it. I hope this article will not only help you configure a JDBC appender for persisting logs on Oracle Database 11g but also configure a datasource with Red Hat Fuse 7 in a Karaf environment. It should also help you gain some hands-on experience with Karaf commands.

Share