[LOG4J2-1083] JDBC Appender : support for NULL values when using the pattern

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: Appenders
• Assignee: Unassigned
• Reporter: Benjamin Jaton
• Created: 23/Jul/15 21:08
• Updated: 23/Jul/15 21:08

Description

I am using the JDBCAppender:

{
  "type" : "Jdbc",
  "name" : "MyDatabaseAppender",
  "tableName" : "LOGS",
  "ignoreExceptions" : "false",
  "ConnectionFactory" : {
    "class" : "test.ConnectionFactory",
    "method" : "getDatabaseAppenderDataSource"
  },
  "Column" : [
     { "name" : "ID", "pattern" : "%X{ID}", "isUnicode" : "false" },
     { "name" : "NUM", "pattern" : "%X{NUM}", "isUnicode" : "false" }
 ]
}

As you can see, I am using the ThreadContext to pass the variables.

ThreadContext.put("ID", id++);
ThreadContext.put("NUM", "41");

The NUM column is of type BIGINT, and is nullable.

I am trying to set it to NULL with:
ThreadContext.put("NUM", "NULL");
or
ThreadContext.put("NUM", null);
or
ThreadContext.put("NUM", "");
or
ThreadContext.remove("NUM");

Every time I get

Caused by: org.apache.derby.client.am.SqlException: Error for batch element #0: Invalid character string format for type BIGINT.
    at org.apache.derby.client.am.Statement.completeExecute(Unknown Source)

I believe this is because, in

org.apache.logging.log4j.core.appender.db.jdbc.JdbcDatabaseManager.writeInternal()

we always do

this.statement.setString(i++, column.layout.toSerializable(event));

So we always end up feeding the String "" or "NULL" to the statement.

I think the simplest way to do this would be to accept a new parameter like "setToNullIfEmpty".
It's also possible to get the meta data from the DB (type+nullable?) but that's much more work.