[syslog-ng] escapes in SQL statements (was Re: dbtype in SQL logging)

[Alan McKinnon]

My recommendation is that you treat the PID as a string, because that is what 
it really is. You are never going to do arithmetic on a PID.

Is there some circumstance preventing you from making the PID field a string 
in the database?



On Tuesday 22 February 2011 11:57:41 Hendrik Visage wrote:
> Hi there,
> 
>  Okay, looks like I was looking/thinking about something else, so lets
> explain my problem.
> 
>  I want the pid inseted to be a "proper" number/integer, not a
> string/character, and the fun starts with several of my sources not having
> a pid entry, thus that field I would like to be filled with NULL values.
> However, it still gets escaped, and when it gets escaped, the NULL is not
> null anymore, but the string 'NULL' :(
> 
> I was hoping that in some why I could coerce syslog-ng to understand that
> that column is a integer column, thus should not be escaped as it is
> currently done ;(
> 
> I have the following destination:
> destination d_pgsql {
>   sql(type(pgsql)
>   host("127.0.0.1") username("logwriter") password("logwriter") port("5432")
> database("syslog")
>   table("logs_${HOST}_${R_YEAR}${R_MONTH}${R_DAY}") #or whatever you want,
> example ${HOST}" for hosts, ${LEVEL}" for levels.. etc columns("datetime
> varchar(16)", "host varchar(32)", "program varchar(16)", "pid varchar(8)
> default null", "message varchar") values("$R_DATE", "$HOST", "$PROGRAM",
> "${PID:-NULL}", "$MSG")
>   indexes("datetime", "host", "program", "pid", "message"));
> };
> 
> and the following log lines:
> 
> Incoming log entry; line='<166>Feb 22 08:38:59 Vpxa: [2011-02-22
> 08:38:59.862 1B7A7B90 verbose \'App\'] [VpxaHalServices] HostChanged Event
> Fired, properties changed []' Running SQL query; query='INSERT INTO
> logs_somedevice_20110222 (datetime, host, program, pid, message) VALUES
> (\'Feb 22 11:39:27\', \'somedevice\', \'Vpxa\', \'NULL\', \'[2011-02-22
> 08:38:59.862 1B7A7B90 verbose \'\'App\'\'] [VpxaHalServices] HostChanged
> Event Fired, properties changed []\')'
-- 
Alan McKinnon
Systems Engineer^W Technician
Infrastructure Services
Internet Solutions

+27 11 575 7585


Please note: This email and its content are subject to the disclaimer as displayed at the following link http://www.is.co.za/legal/E-mail+Confidentiality+Notice+and+Disclaimer.htm. Should you not have Web access, send a mail to disclaimers at is.co.za and a copy will be emailed to you.