Possible to insert a null into a database column, using Configuration API?

[tekiegirl]

I don't seem to be able to find a way to insert a null into a database column, using the Configuration API. I have some columns that are not always required, so for some logging will be nulls.

The only way I have found to do this is to use a stored procedure, and pass empty strings to it for any nulls. The stored procedure then puts in null whenever there is an empty string.

Could this feature be added in some way? e.g.

var param = new DatabaseParameterInfo();
param.Name = "foo";
param.Layout = null;
target.Parameters.Add(param);

Thanks for the feature request, I'll look into this

[304NotModified]

Hi @jacquibo, This isn't possible yet. I was looking a this, but it's a bit tricky. I need some good ideas's how to solve this.

@Xharze as far I can see, most Targets assume that the Layout renderer won't return null. This makes this feature request a bit tricky.

I was thinking to create a special Layout renderer for the DatabaseTarget, e.g. ${DBNull}.

But this is a bit strange of course, because it's not available on other layout renderers. What do you think?

Edit: difficult to create a DBNullLayoutRenderer, because DBNull.Value isn't a string.

I think the best solution if we could allow the layout renderers to return
null in the database target, but I don't know if it's possible without
affecting other targets.
The reason is that you would probably need to use ifs in the layout to
achieve the desired effect if inserting null in different scenarios.

[304NotModified]

I think returning null by the layout renderes had too much impact.

To summarize the possible solutions.

Possible solutions:

1. Create a special Layout renderer (e.g. DBNullLayoutRenderer) for the all targets, e.g. ${DBNull}. - not possible, because DBNull.Value isn't a string.
2. Allow Layout return null . Unknown impact, will infect also custom Targets.
3. Create a special Layout renderer (e.g. DBNullLayoutRenderer), only for DatabaseTarget,. - not possible, got exception, layout renderer not found.
4. Use a new property on DatabaseParameterInfo: IsNull (bool) - ugly
5. Use a fixed string, which isn't in layout renderer format: e.g. $DBNull and convert that one to null in DatabaseTarget - ugly

Non of them are perfect. Any thoughts on this @Xharze?

[realityexists]

FWIW, I ran into this as well, but found a simple workaround for my particular case: NULLIF in the insert command (I'm using PostgreSQL). This doesn't allow distinguishing between empty string and null, but for my scenario it's OK. I want to store the exception text if there is an exception, NULL if there is no exception. So my config looks like this:

      <commandText>
        INSERT INTO log_message(time, level, message, exception)
        VALUES (:time, :level, :message, NULLIF(:exception, ''));
      </commandText>
      ...
      <parameter name=":exception" layout="${exception:format=tostring}" />

[304NotModified]

Yes, thanks for the info.

Note: Also SQL Server has a NULLIF

[he-dev]

I use some extensions to create the INSERT and to create the NULLIF for nullable parameters. You can find my answer on Stack Overflow.