[syslog-ng] Database field types -- insert fails for null values

[Balazs Scheidler]

On Tue, 2009-03-17 at 11:06 -0700, Liam Kirsher wrote:
> Balazs,
> 
> Well, can a default value be NULL?  Would it be possible to say
> something like
> ${lat:-NULL}
> 
> For lat/lng it definitely won't work, since 0/0 is a valid value --
> but probably one we don't want -- someplace out in the Atlantic Ocean.

Please try the attached patch, it adds a new option to the sql()
destination driver: null().

Whenever syslog-ng enounters a value that matches the string specified
by null(), it will insert the NULL value.

This patch has not yet been committed anywhere, but it seems to do the
trick for me. The question is if it works for you?

With this you could do something like:

sql(... 
	columns(..., "art_id", ...) 
	values(..., "${UT.ART_ID:-@@NULL@@}", ...) 
	null("@@NULL@@")

Of course the NULL value must be something that cannot happen in your
dataset, you could perhaps use the null string, but in that case every
empty string would be INSERTed as NULLs which I'm not sure is what you
want.

The problem is that within the SQL driver, every column is a string  and
the value you specify is a template possible containing several macros.
Syslog-ng currently has no way of knowing that the given field had no
value, it only knows that the given field expanded to the zero string.

With the sample above (and the patch below), if any other field would by
accident contain the string @@NULL@@ it'd be emitted as a NULL value. I
don't see too much of a risk here, but you never know...

If this works out for you, I'm willing to integrate this patch, but
possibly only after the 3.0.2 release was made. Which you can help by
the way by testing the binaries that I've published recently.

-- 
Bazsi
-------------- next part --------------
A non-text attachment was scrubbed...
Name: q
Type: text/x-patch
Size: 4034 bytes
Desc: not available
Url : http://lists.balabit.hu/pipermail/syslog-ng/attachments/20090323/9504c922/attachment.bin