Dynamic SELECT query which decides whether to use the WHERE clause in Mule 4

Question

I am using Mule 4 and Anypoint 7 and want to setup the database connector to SELECT all customers from my SQL server database table but if the customerName query parameter is populated in the request then I want to add the WHERE clause to only return customers with the same name as the customerName query parameter otherwise it should just return all customers.

My code is below but I am struggling to get the syntax correct.

<db:select doc:name="Select Customers" doc:id="98a4aa2f-b0b6-4fb5-ab27-d70489fd532d" config-ref="db-config">
    <ee:repeatable-file-store-iterable />
    <db:sql >SELECT TOP 10 * FROM MYDB.dbo.Customer $(if (attributes.queryParams.customerName != null and isEmpty(attributes.queryParams.customerName) == false) &quot;WHERE Name = :customerName&quot; else &quot;&quot;)</db:sql>
    <db:input-parameters ><![CDATA[#[{'customerName' : attributes.queryParams.customerName}]]]></db:input-parameters>
</db:select>

How can I do this?

Thanks

Alan MacK Alan MacK · Accepted Answer · 2018-11-10T19:18:04

You were on the right path. I think you were only missing the evaluation tags around the SQL in the db:sql element.

<db:select doc:name="Select Customers" doc:id="98a4aa2f-b0b6-4fb5-ab27-d70489fd532d"
    config-ref="db-config">
    <ee:repeatable-file-store-iterable />
    <db:sql>#["SELECT TOP 10 * FROM MYDB.dbo.Customer 
         $(if (isEmpty(attributes.queryParams.customerName) == false) &quot;WHERE Name = :customerName&quot; else &quot;&quot;) "]</db:sql>
    <db:input-parameters ><![CDATA[#[{'customerName' : attributes.queryParams.customerName}]]]></db:input-parameters>
</db:select>

It is easier to debug things like this with variables, so that you can see the individual values. FWIW heres my test code:

<set-variable variableName="additionalWhereClause" 
    value='#[if ( isEmpty(attributes.queryParams.email) == false) 
                 "WHERE Email = :emailParm" 
             else "" ]' />

<set-variable variableName="selectSql" 
    value="#['SELECT FirstName, LastName, Email     
              FROM User   
              $( vars.additionalWhereClause )  
              ORDER BY Email LIMIT 10']" />

<logger level="INFO" message="queryParams: #[attributes.queryParams]" doc:id="96c62f84-2c98-4df6-829c-e00c9fcec9ca" />
<logger level="INFO" message="additionalWhereClause #[vars.additionalWhereClause]" doc:id="0d3611b4-34ae-4ebb-b931-6d31ce3804c1" />
<logger level="INFO" message="selectSql #[vars.selectSql]" doc:id="5c56342d-9674-4891-9d7e-bb32319f4ad0" />

<db:select doc:name="MySQL Query" doc:id="e60be3e6-9b51-4b3b-9dfa-4ee0af65cb03"
    config-ref="mysql-config">
    <ee:repeatable-file-store-iterable />
    <db:sql>#[ vars.selectSql ]</db:sql>
    <db:input-parameters><![CDATA[#[{'emailParm' : attributes.queryParams.email}]]]></db:input-parameters>
</db:select>

Dynamic SELECT query which decides whether to use the WHERE clause in Mule 4

1 Answers