Mule 4 dynamic queries in the Database Connector

Question

In my flow in Mule 4 I am trying to query a database for specific data. For example I want to run a query like this:

SELECT * FROM mulesoft WHERE plant = CCCNNB;

The thing is both plant and CCNNB need to be dynamic. They will come through an API request. I can handle the value to be dynamic, but I get empty results whenever I try to make the field dynamic. I first create a variable which stores the json from the request:

set-variable value="#[payload]" doc:name="Set Variable" doc:id="8ed26865-d722-4fdb-9407-1f629b45d318" variableName="SORT_KEY"/>

Request looks like this:

{
 "FILTER_KEY": "plant",
 "FILTER_VALS": "CCNNB"
}

Afterwards in the db connector I configure the following:

<db:select doc:name="Select" doc:id="13a66f51-2a4e-4949-b383-86c43056f7a3" config-ref="Database_Config">
        <db:sql><![CDATA[SELECT * FROM mulesoft WHERE :filter_key = :filter_val;]]></db:sql>
        <db:input-parameters ><![CDATA[#[{
        "filter_val": vars.SORT_KEY.FILTER_VALS,
        "filter_key": vars.SORT_KEY.FILTER_KEY
    }]]]></db:input-parameters>

Replacing :filter_key with plant works but as soon as I try to make it dynamic I get nothing in the response. It does not fail though, response code is 200 but I get nothing inside it. How can I make this work?

score 2 · Accepted Answer · edited Mar 08 '22 at 13:35

2

You can directly use the stored variables in the query itself.

Query Should be an expression in DataWeave.

#["SELECT * FROM $(vars.table) WHERE $(vars.SORT_KEY.FILTER_KEY) = :filter_val"]

<db:select config-ref="Database_Config">
  <db:sql><![CDATA[#["SELECT * FROM $(vars.table) WHERE $(vars.SORT_KEY.FILTER_KEY) = :filter_val"]]]></db:sql>
    <db:input-parameters ><![CDATA[#[{
                                 "filter_val": vars.SORT_KEY.FILTER_VALS
                          }]]]>
    </db:input-parameters>          
</db:select>

edited Mar 08 '22 at 13:35

aled

21,330
3
27
34

answered Mar 08 '22 at 06:43

Karthik

2,181
4
10
28

1

This was it. Thank you. I had no idea you can use this Mule Expression Language – davidb Mar 08 '22 at 08:50
The name is not correct. The Mule Expression Language (MEL) was the language for expression in Mule 3.x. MEL has been deprecated and it is not supported in Mule 4.x, which uses DataWeave 2.x as its expression language. This question is about Mule 4.x. You can find more in the documentation: https://docs.mulesoft.com/dataweave/2.4/ Note that by using dynamic expressions in SQL queries you assume the responsibility of avoiding SQL injection security vulnerabilities. – aled Mar 08 '22 at 13:33

Anurag Sharma · Answer 2 · 2022-03-08T10:50:42.527

There is another way also to read values from payload to build a dynamic query as below

#["SELECT * FROM mulesoft 
         WHERE " ++ vars.SORT_KEY.FILTER_KEY ++ " = '" ++ vars.SORT_KEY.FILTER_VALS ++ "'"]

Below is the XML that is created for this, as a POC

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:os="http://www.mulesoft.org/schema/mule/os"
    xmlns:salesforce="http://www.mulesoft.org/schema/mule/salesforce"
    xmlns:db="http://www.mulesoft.org/schema/mule/db"
    xmlns:xml-module="http://www.mulesoft.org/schema/mule/xml-module"
    xmlns:http="http://www.mulesoft.org/schema/mule/http"
    xmlns:ee="http://www.mulesoft.org/schema/mule/ee/core"
    xmlns="http://www.mulesoft.org/schema/mule/core"
    xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/ee/core http://www.mulesoft.org/schema/mule/ee/core/current/mule-ee.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/os http://www.mulesoft.org/schema/mule/os/current/mule-os.xsd">

    <http:listener-config name="HTTP_Listener_config1"
        doc:name="HTTP Listener config"
        doc:id="6d5de64b-1355-4967-9352-4b324f02c7ad">
        <http:listener-connection host="0.0.0.0"
            port="8081" />
    </http:listener-config>

    <db:config name="Database_Config" doc:name="Database Config"
        doc:id="d5c4d49c-aef3-4d4a-a7b5-470da3354127">
        <db:my-sql-connection host="localhost"
            port="3306" user="root" password="admin123" database="Mysql" />
    </db:config>
    <flow name="testFlow"
        doc:id="8cfea1b0-d244-40d9-989c-e136af0d9f80" initialState="started">
        <http:listener doc:name="Listener"
            doc:id="265e671b-7d2f-4f3a-908c-8065a5f36a07"
            config-ref="HTTP_Listener_config1" path="test" />
        <set-variable value="#[payload]" doc:name="Set Variable"
            doc:id="265a16c5-68d4-4217-8626-c4ab0a3e38e5" variableName="SORT_KEY" />
        <db:select doc:name="Select"
            doc:id="bdf4a59c-0bcc-46ac-8258-f1f1762c4e7f"
            config-ref="Database_Config">
            <db:sql><![CDATA[#["SELECT * FROM mulesoft.mulesoft WHERE " ++ vars.SORT_KEY.FILTER_KEY ++ " = '" ++ vars.SORT_KEY.FILTER_VALS ++ "'"]]]></db:sql>
        </db:select>
        <ee:transform doc:name="Transform Message"
            doc:id="72cbe69f-c52e-4df9-ba5b-dd751990bc08">
            <ee:message>
                <ee:set-payload><![CDATA[%dw 2.0
output application/json
---
payload]]></ee:set-payload>
            </ee:message>
        </ee:transform>
    </flow>
</mule>

Explanation of the Flow

I am using the payload that is in Question
Seting a variable name "SORT_KEY", value of this varibale is complete payload that we receive.
then creating a dynamic query inside the Db connector
using transform message sending the data as response, that we received from DataBase

score 1 · Answer 3 · answered Apr 04 '22 at 14:55

So, there are several issues here. One, the creation of the sql statement. You can do DW inside the DB:SELECT component if you want, as shown by previous answers. Either the

#["SELECT * FROM myTable" ++ vars.myWhere]

OR

#["SELECT * FROM myTable $(vars.myWhere)"]

work.

The problem you will run into is that DataSense doesn't like this. Without the literal text for the column names, DataSense can't figure out what columns to retrieve so it raises an error "Unable to resolve value for the prameter: sql". This leaves an error in your code, which always gives me angst. I wish Mulesoft would resolve this problem.

BTW, if you do dynamic SQL, you should STILL use input parameters for each value to avoid SQL injections.

I have an "Idea" posted here to fix the bogus error: https://help.mulesoft.com/s/ideas#0872T000000XbjkQAC

Mule 4 dynamic queries in the Database Connector

3 Answers3