MuleSoft 4 Anypoint Studio Dynamic Database Query Parameter Error

Question

I have to use a dynamic query to set the schema/library name for a database query and Anypoint Studio 7.4.1 keeps throwing an error stating that it is "unable to resolve value for the parameter: sql".

I'm following this doc https://docs.mulesoft.com/connectors/db/database-connector-examples#dynamic-queries.

It works, fine when I run it, but is there a way to get AnyPoint to stop showing the error?

Here is the XML

    <flow name="GetLinks" doc:id="c1f17deb-98f2-4261-93ec-cf785e310892" >
        <set-variable value="${db.dataLibrary}" doc:name="Set Library" doc:id="2a5cebbb-3047-4ede-ab0d-7a7257427e7e" variableName="library"/>
        <db:select doc:name="Select EP0001F1" doc:id="ecf2a61f-4e14-4b00-beb1-546d2fa1ba8f" config-ref="Database_Config" fetchSize="100" maxRows="500">
            <db:sql >#[&quot;select * from $(vars.library).EP0001F1 order by EPDRC desc&quot;]</db:sql>
        </db:select>
        <ee:transform doc:name="Transform Message" doc:id="c3b8bcb4-20ef-4b86-a4cd-585e7f57e6d0" >
            <ee:message >
                <ee:set-payload ><![CDATA[%dw 2.0
output application/json
---
payload map ( payload01 , indexOfPayload01 ) -> {
    loginId: trim(payload01.EPLOGINID),
    individualId: trim(payload01.EPIID),
    officeNumber: payload01.EPOFID,
    controlNumber: payload01.EPICN,
    webControlNumber: payload01.EPWCN,
    lastUpdatedBy: trim(payload01.EPULU)
}]]></ee:set-payload>
            </ee:message>
        </ee:transform>
    </flow>

The stacktrace for the error shows

Caused by: org.mule.runtime.core.api.expression.ExpressionRuntimeException: "You called the function '' with these arguments: 
  1: Null (null)
  2: String (".EP0001F1 order by EPDRC desc")

But it expects arguments of these types:
  1: String
  2: String

Trace:
  at main (Unknown)" evaluating expression: ""select * from $(vars.library).EP0001F1 order by EPDRC desc"".
Caused by: org.mule.runtime.api.el.ExpressionExecutionException: You called the function '' with these arguments: 
  1: Null (null)
  2: String (".EP0001F1 order by EPDRC desc")

But it expects arguments of these types:
  1: String
  2: String

Trace:
  at main (Unknown)

satish chennupati satish chennupati · Accepted Answer · 2019-12-12T18:26:00

you are almost correct. Did a small sample and tested it out. this is how i wrote my query and it worked for me.

["SELECT * FROM $(vars.schema). TEST_TABLE"]

the error tells me that your variable is null. Just make sure that your variable is initialized with proper value before using it in query. May be just put that in logger and make sure your var.library has something in it.

note: I tested it with order by in my query as well and that works too. I tested it against microsoft sql server. Also i got that error only when my var was null. (i got exact same error)

MuleSoft 4 Anypoint Studio Dynamic Database Query Parameter Error

2 Answers

["SELECT * FROM $(vars.schema). TEST_TABLE"]