[exim] handling nulls in SQL lookups

Kezdőlap
Üzenet törlése
Válasz az üzenetre
Szerző: Ian P. Christian
Dátum: 2006-07-23 07:23 -000
Címzett: exim-users@exim.org
Tárgy: [exim] handling nulls in SQL lookups
I'm doing as follows:


SPAM_COUNT    = SELECT SUM(spam_score) FROM spam WHERE host =
'${quote_sqlite:$sender_host_address}' AND created_at > (${tod_epoch} -
300);


  warn
          set acl_m3  = ${lookup sqlite {SQLITE_DB SPAM_COUNT}{$value}}
          log_message = Spam total: $acl_m3



However, this will obviously return nothing if the host has never mailed
before, and the 'Spam total' reports as NULL.

Other then hacking the SQL statement to return a 0 (which I've
previously done with MySQL, but can't get sqlite to play nice) - is
there something I'm missing to default the value to 0 when there's a null?

Thanks,


Ian


btw - exim seems to do NO error reporting with sqlite problems - if the
database file is invalid, if the permissions are wrong, or if the query
is wrong, there are no errors reported - I'll have to bug about this at
some point.

--
Ian P. Christian ~ http://pookey.co.uk