On Mon, Apr 05, 2010 at 09:25:36PM +1200, Jim Cheetham said:
> I'm using a sqlite query in a match_domain condition in an ACL for check_rcpt, but it's failing.
>
> Testing from the commandline suggests that I can't do a "where" clause in the SQL query ...
I suspect that what's happening is not that the where clause is failing,
but the sql return is not a domain list. I do something roughly like
the following to get domains out of sql in one place (names changed to
your example to make it easier):
domainlist active_domains = ${sg { ${sqlite; /etc/exim4/mta.db select name from domains where status="active"; }}{\\n}{:}}
And then wherever I need to match I can use:
domains = +active_domains
Cheers,
> Here's the DB table, and the working query :-
> > $ sqlite3 -header /etc/exim4/mta.db 'select * from domains'
> > name|status
> > local.dom|active
> > unpaid.dom|inactive
> > good.dom|active
> > bad.dom|blocked
> > ok.dom|active
> >
> > $ sqlite3 /etc/exim4/mta.db 'select name from domains where status="active"'
> > local.dom
> > good.dom
> > ok.dom
>
> Running this through exim -be shows the problem ... only 'ok.dom' should pass, 'bad.dom' should fail.
> > $ exim -be '${if match_domain{ok.dom}{sqlite; /etc/exim4/mta.db select name from domains where status="active";}{yes}{no}}'
> > yes
> > $ exim -be '${if match_domain{bad.dom}{sqlite; /etc/exim4/mta.db select name from domains where status="active";}{yes}{no}}'
> > yes
>
> Any clues as to what I'm doing wrong would be appreciated!
>
> I'm using the standard Ubuntu 9.04 provision of exim4-daemon-heavy :-
> > $ exim -bV
> > Exim version 4.69 #1 built 11-Feb-2009 13:51:47
> > Copyright (c) University of Cambridge 2006
> > Berkeley DB: Berkeley DB 4.6.21: (September 27, 2007)
> > Support for: crypteq iconv() IPv6 PAM Perl Expand_dlfunc GnuTLS move_frozen_messages Content_Scanning Old_Demime
> > Lookups: lsearch wildlsearch nwildlsearch iplsearch cdb dbm dbmnz dnsdb dsearch ldap ldapdn ldapm mysql nis nis0 passwd pgsql sqlite
> > Authenticators: cram_md5 cyrus_sasl dovecot plaintext spa
> > Routers: accept dnslookup ipliteral iplookup manualroute queryprogram redirect
> > Transports: appendfile/maildir/mailstore/mbx autoreply lmtp pipe smtp
> > Fixed never_users: 0
> > Size of off_t: 8
> > Configuration file is /etc/exim4/exim4.conf
>
> -jim
>
>
> --
> ## List details at http://lists.exim.org/mailman/listinfo/exim-users
> ## Exim details at http://www.exim.org/
> ## Please use the Wiki with this list - http://wiki.exim.org/
>
--
--------------------------------------------------------------------------
| Stephen Gran | You will be awarded a medal for |
| steve@??? | disregarding safety in saving someone. |
| http://www.lobefin.net/~steve | |
--------------------------------------------------------------------------
