AdoNetAppender and Clob field

Question

I have a asp.net 3.5 app that is using log4Net and AdoNetAppender. Currently the app uses a message field just like the log4net documentation http://logging.apache.org/log4net/release/config-examples.html. I would like to convert the field from varchar2 to Clob. I was trying to find some documentation on how to do this. All I could find is:

http://old.nabble.com/DbType-for-CLOB-column-using-AdoNetAppender-td1214036.html#a1214036

which wasn't too useful. Does anyone know a link or some samples on how to use a Clob file with a AdoNetAppender?

Thanks, Bill N

Answer 1

Did you try this:

http://marc.info/?l=log4net-user&m=110874200319166

basically you need to set the DbType to string and remove the Size parameter. Apparently this does not work correctly for nvarchar(max) (see here) but that does not mean it will not work for Clob.

Answer 2

I know this is an old question, but I recently needed to pass a CLOB parameter to a package procedure, using log4net. I was not able to do that using the suggestions I found online, including the one with setting the DbType to String and removing Size.

I am using an Oracle package procedure that takes a parameter of type CLOB. Using a custom AdoNetAppenderParameter I am able to pass long strings (270k+ characters) to the procedure and store them in the DB (Oracle 9i).

First of all, I had to use Oracle's Data Access Provider (after all, Microsoft's System.Data.OracleClient has been deprecated). Your project must reference Oracle.DataAccess.dll. I got the NuGet package by searching for "oracle.dataaccess" in NuGet package manager.

The library has an implementation of DbParameter, OracleParameter, that has a OracleDbType property. The type of the property is OracleDbType which is an enumeration that has the Clob value.

After adding the reference, I changed the appender's connection type to:

<connectionType value="Oracle.DataAccess.Client.OracleConnection, Oracle.DataAccess, Version=2.112.3.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />

I then created a custom AdoNetAppenderParameter that creates a new OracleParameter and sets its type to Clob:

public class OracleAdoNetAppenderParameter : AdoNetAppenderParameter
{
    public OracleDbType OracleDbType { get; set; }

    public override void Prepare(System.Data.IDbCommand command)
    {
        if (!(command is OracleCommand))
        {
            string message = string.Format("The log4net parameter of type {0} can only be used with an appender connection of type {1}. The expected command type, {2}, cannot be supplied. Please check the parent appender's connectionType property.",
                                             this.GetType(), typeof(OracleConnection), typeof(OracleCommand));

            throw new System.ArgumentException(message, "command");
        }

        var parameter = command.CreateParameter() as OracleParameter;

        parameter.ParameterName = base.ParameterName;
        parameter.OracleDbType = this.OracleDbType;

        command.Parameters.Add(parameter);
    }
}

I exposed a property, OracleDbType, so I would be able to specify it through configuration.

I initially did not expose the property, named the class OracleClobAdoNetAppenderParameter and set the OracleDbType property to Clob inside the Prepare method.

After I created the class, I added the parameter to the appender's configuration, like this:

<parameter type="YourNamespace.OracleAdoNetAppenderParameter, YourAssembly">
    <OracleDbType value="Clob" />
    <parameterName value=":yourProcedureClobParam"/>
    <layout type="..."></layout>
 </parameter>

You can use your own layout. I am passing my large string through log4net's context as a custom parameter.

Here's the final configuration I'm using:

<appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
      <connectionType value="Oracle.DataAccess.Client.OracleConnection, Oracle.DataAccess, Version=2.112.3.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
      <connectionString value="data source=xxx;User ID=xxx;Password=xxx"/>
      <commandText value="MY_PKG.LogMessage"/>
      <commandType value="StoredProcedure" />
      <!-- SERVICE_MESSAGE -->
      <parameter type="MyNamespace.OracleAdoNetAppenderParameter, MyAssembly">
        <OracleDbType value="Clob" />
        <parameterName value=":service_message"/>
        <layout type="log4net.Layout.RawPropertyLayout">
          <key value="service_message"/>
        </layout>
      </parameter>
      <!-- LOG_LEVEL -->
      <parameter>
        <parameterName value=":type"/>
        <dbType value="String"/>
        <size value="20"/>
        <layout type="log4net.Layout.PatternLayout" value="%level"/>
      </parameter>
      <!-- LOG_DATE -->
      <parameter>
        <parameterName value=":timestamp"/>
        <dbType value="DateTime"/>
        <layout type="log4net.Layout.RawTimeStampLayout" />
      </parameter>
      <!-- MESSAGE -->
      <parameter>
        <parameterName value=":message"/>
        <dbType value="String"/>
        <size value="1000"/>
        <layout type="log4net.Layout.PatternLayout" value="%message"/>
      </parameter>
      <!-- EXCEPTION -->
      <parameter>
        <parameterName value=":error"/>
        <dbType value="String"/>
        <size value="4000"/>
        <layout type="log4net.Layout.ExceptionLayout"/>
      </parameter>
......
</appender>

Hope it helps.