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 = [[email protected], [email protected], [email protected]]

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 ('[email protected]', '[email protected]', '[email protected]')

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 Anton Kupias · Accepted Answer · 2014-02-07T19:47:22

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'], '\',\'')]'

Creating comma separated strings from a list in Mule ESB

1 Answers