Creating comma separated strings from a list in Mule ESB

Question

I'm extracting emails from JSON formatted data using MVEL in a enricher which returns me as a list. I want to create comma separated string with these emails so I can pass it to JDBC. I tried the following,

<enricher target="#[flowVars.listEmails]" source="#[{(Details.Email in payload.People)}]" doc:name="Message Enricher">
    <json:json-to-object-transformer returnClass="java.util.HashMap" doc:name="JSON to Object"/>
</enricher>
<set-variable value="#[StringUtils.join(flowVars['listEmails'], ', ')]" variableName="strEmails"></set-variable>
<logger level="INFO" doc:name="Logger" message="$$$: output = #[StringUtils.join(flowVars['listEmails'], ',')]"/>

but when I ran the SQL Profiler, I don't see the any parameter got passed to the SQL query. When I tried it does print out the emails, but as a list. Here is the output:

INFO  2014-02-07 10:01:41,699 [[UserManagement].connector.http.mule.default.receiver.02] org.mule.api.processor.LoggerMessageProcessor: $$$: output = [abc@mail.com, def@mail.com, ghi@mail.com]

To recap, my requirement is to get the emails from a list and format it as a comma separated string, so I can pass it to JDBC query to get results from SQL Server. The query should look like:

SELECT Id, Email FROM tbl_sfcontact WHERE Email IN ('abc@gmail.com', 'def@mail.com', 'ghi@mail.com')

where the email list in the 'WHERE' section is extracted from the JSON dataset. The JDBC Query that I use now looks as follows:

<jdbc:query key="getContactByEmail" value="SELECT Id FROM tbl_sfContact WHERE Email IN (#[StringUtils.join(flowVars['strEmails'], ', ')])"/>

Anton Kupias · Accepted Answer · 2014-02-07T21:52:50.053

2

You have extra curly brackets in your enricher source that wraps the email list inside an array. It should be #[(Details.Email in payload.People)].

UPDATE:

Try adding the missing single quotation marks to the as a parameter to split, and before and after the expression. Like this:

'#[StringUtils.join(flowVars['listEmails'], '\',\'')]'

edited Feb 07 '14 at 21:52

answered Feb 07 '14 at 19:47

Anton Kupias

3,945
3
16
20

Removing the extra curly brace did remove the brackets from logger output, however, I guess it is not creating the string like 'abc@gmail.com', 'def@gmail.com' and it throws SQL Exception 'Too many parameters: expected 0, was given 1'. I've updated my question to include an example on what I want. – Tech Matrix Feb 07 '14 at 21:36
Updated my answer, but didn't test with the JDBC stuff. Works with the Logger, anyhow. – Anton Kupias Feb 07 '14 at 21:53
It does add the quotes, albeit missing the beginning quote on the first email and the ending quote on the last email. However, it is not working with the jdbc query and throwing the same error. I came across another post (https://stackoverflow.com/questions/11434619/how-to-pass-comma-delimited-string-to-jdbc-query-in-mule) and after reading that I'm not sure if this is possible using declarative functionality in mule - I may have to use java component to accomplish this. – Tech Matrix Feb 07 '14 at 22:00
OK. You might consider implementing a custom strategy for JDBC to get this to work. I did that once to be able to do some queries not supported by Mule JDBC. Don't know if my classes work for current Mule versions, but you can check my GitHub to get an idea: https://github.com/qpias/Mule – Anton Kupias Feb 07 '14 at 22:14

Creating comma separated strings from a list in Mule ESB

1 Answers1