Mule Sql Query - passing parameters to the IN operator

Question

I'm trying to create a mule flow that does a lot of DB (Microsoft SQL Server 2008 DB) operations.

The first DB query I do returns a list of IDs

select id from first_table;

I run the payload through a Java transformer that builds an ArrayList: [1,2,3,4,5] and then save it as a variable indices

Then I try to use this Array in another query

select * from another_table where first_table_fk in (#[flowVars.indices]);

But no matter what I do I keep getting the error

The conversion from UNKNOWN to UNKNOWN is unsupported.

Mule can't handle the java.util.ArrayList (or a regular array, int[]) when using the IN operator.

I then tried creating the whole query as a string variable (and then referencing it in the ).

This results with an exception: java.lang.IllegalArgumentException: No SQL Strategy found for SQL statement: #[flowVars.queryString]

Can you please help me with my problem (using the IN operator in Mule sql queries) ?

I am using Mule studio 3.4.0 CE.

Thanks!

Per-Axel Felth Per-Axel Felth · Accepted Answer · 2014-06-15T21:12:27

Mule uses statements (from java.sql) and thus doesn't support using IN in that way. Every occurance of MEL-expression (#[]) will be replaced by a question mark (?) and the actual values will be passed as parameters. I suggest you solve this by building the query dynamically and assign it to the JDBC-connector, and the execute it using a generic endpoint.

Create a Java class like this:

public class CustomQueryBuilder implements Callable {
    @Override
    public Object onCall(MuleEventContext eventContext) throws Exception {
        JdbcConnector c = (JdbcConnector) eventContext.getMuleContext().getRegistry().lookupConnector("JDBC_connector");

        StringBuilder query = new StringBuilder();
        String queryBase = "select * from another_table where first_table_fk in (";
        query.append(queryBase);

        int numIndices = ((ArrayList<Integer>)eventContext.getMessage().getInvocationProperty("indices")).size();
        ArrayList<String> indices = new ArrayList<String>();
        for(int i=0; i<numIndices; i++) {
                indices.add("#[flowVars.indices[" + i + "]");
        }
        query.append(StringUtils.join(indices, ", "));
        query.append(")");

        String finalQuery = query.toString();

        MessageDigest md = MessageDigest.getInstance("MD5");
        String queryDigest = String.format("%1$032X",new BigInteger(1, md.digest(finalQuery.getBytes("UTF-8"))));

        if (!c.getQueries().containsKey(queryDigest)) {
                c.getQueries().put(queryDigest, finalQuery);
        }

        eventContext.getMessage().setInvocationProperty("generatedQueryKey", queryDigest);

        return eventContext.getMessage();
    }
}

Use it in your flow after you set the "indices" flow var.
```
<component class="CustomQueryBuilder"/>
```
Then insert an generic outbound endpoint instead of your jdbc outbound endpoint and reference the created query.
```
<outbound-endpoint exchange-pattern="request-response" address="jdbc://#[flowVars.generatedQueryKey]" />
```

Mule Sql Query - passing parameters to the IN operator

1 Answers