log to redshift using log4net's adonetappender

Question

I'm trying to log to my redshift server using log4net's builtin adonetappender. When the timestamp field is removed the log will successfully be saved. However, the format of the timestamp seems to be causing it to fail. I have tried many different formats that match what redshift claims to support.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<!-- This section contains the log4net configuration settings -->
<log4net>

<!-- Define some output appenders -->

<appender name="DebugAppender" type="log4net.Appender.DebugAppender">
  <immediateFlush value="true" />
  <layout type="log4net.Layout.SimpleLayout" />
</appender>

<appender name="RedshiftAppender" type="log4net.Appender.AdoNetAppender">
  <bufferSize value="1" />
  <connectionType value="System.Data.Odbc.OdbcConnection,System.Data,version=1.0.3300.0,publicKeyToken=b77a5c561934e089,culture=neutral" />
  <connectionString value="Driver={Amazon Redshift (x86)};..." />
  <commandText value="INSERT INTO hep_config.server_log (tm,src,lvl,msg) VALUES ($1,$2,$3,$4)" />
  <parameter>
    <parameterName value="$1" />
    <dbType value="DateTime" />
    <layout type="log4net.Layout.PatternLayout" value="%date{yyyyMMdd HH':'mm':'ss'.'fff}" />
  </parameter>
  <parameter>
    <parameterName value="$2" />
    <dbType value="String" />
    <size value="128" />
    <layout type="log4net.Layout.PatternLayout" value="%thread" />
  </parameter>
  <parameter>
    <parameterName value="$3" />
    <dbType value="String" />
    <size value="64" />
    <layout type="log4net.Layout.PatternLayout" value="%level" />
  </parameter>
  <parameter>
    <parameterName value="$4" />
    <dbType value="String" />
    <size value="4000" />
    <layout type="log4net.Layout.PatternLayout" value="%message" />
  </parameter>
</appender>

<root>
  <level value="ALL" />
  <appender-ref ref="DebugAppender" />
  <appender-ref ref="RedshiftAppender" />
</root>

</log4net>

</configuration>

Answer 1

I tried and couldn't get it working with the Redshift driver. However, the PostgreSQL ODBC Driver does work. Try the following:

Change your connection string to use the PostgreSQL ODBC Driver. Note I'm on 64-bit windows, so you will need to adjust the driver based on your version.

<connectionString value="Driver={PostgreSQL Unicode(x64)};..." />

Change your layout for the DateTime to use RawUtcTimeStampLayout.

<layout type="log4net.Layout.RawUtcTimeStampLayout" />

NOTE: Timestamp values in Redshift are UTC

UPDATE

I was able to get it to work with the Redshift driver. The MSDN documentation for OdbcCommand.Parameters states:

When CommandType is set to Text, the .NET Framework Data Provider for ODBC does not support passing named parameters to an SQL statement or to a stored procedure called by an OdbcCommand. In either of these cases, use the question mark (?) placeholder.

and

The order in which OdbcParameter objects are added to the OdbcParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

Using this information you can try the following:

<commandText value="INSERT INTO log.log_test(tm,src,lvl,msg) VALUES (?, ?, ?, ?)" />

At this point it doesn't matter what you name the parameters, as the order of the parameters matter. I did continue to use RawUtcTimeStampLayout, but I changed the dbType to string.