On 2009-03-17 at 10:16 +0200, isolderj@??? wrote:
>
> i am trying to parse the UID in one query to a variable in another query but I keep getting a error.
>
> UID = ${lookup mysql{SELECT user_id from usermaps where emailaddress = '$sender_address'}
>
> then I want to say
>
> ACTIVE_USER = ${lookup mysql{SELECT active FROM mail WHERE user_id = UID}
>
> it returns the right uid but I keep getting a
>
> MYSQL: query failed: Unknown column '<uid>' in 'where clause'. when it runs the second query.
>
> i am sure my problem is in the way I am doing the second query.
One query should be faster. If you need to get multiple values, do one
query to get them all, put them in a variable and then use ${extract} to
get the fields you want. If you're doing this in an ACL, then in an ACL
variable; if you're doing it in a Router, then set address_data to make
it available in $address_data to the transport and subsequent routers.
If you need it in multiple places in one Router, then note that there's
a query cache, so that identical queries should be cached.
Note from "9.20 SQL lookups":
----------------------------8< cut here >8------------------------------
If the result of the query contains more than one field, the data for each
field in the row is returned, preceded by its name, so the result of
${lookup pgsql{select home,name from users where id='userx'}\
{$value}}
might be
home=/home/userx name="Mister X"
----------------------------8< cut here >8------------------------------
with more text there about quoting, etc.
For this particular case, of just wanting one field, how about:
SELECT active FROM mail INNER JOIN usermaps ON user_id
WHERE usermaps.emailaddress = '${quote_mysql:$sender_address}'
Also, "exim -d" or perhaps "exim -d+expand" would have shown you the
actual query being constructed, which would have highlighted the
confusion between macros and variables which Warren pointed out.
-Phil
