How to access flow vars in jdbc end point in mule studio application

1
votes
<jdbc-ee:mysql-data-source name="MySQL_Data_Source" user="root" password="root" url="jdbc:mysql://localhost:3306/test" transactionIsolation="UNSPECIFIED" doc:name="MySQL Data Source"/>
<jdbc-ee:connector name="test" dataSource-ref="MySQL_Data_Source" validateConnections="true" queryTimeout="-1" pollingFrequency="0" doc:name="Database"/>
<data-mapper:config name="DataMapper" transformationGraphPath="mapper.grf" doc:name="DataMapper"/>
<flow name="EmployeeListFlow1" doc:name="EmployeeListFlow1">
    <http:inbound-endpoint exchange-pattern="request-response" host="localhost" port="8082" doc:name="HTTP" path="employeelist"/>
    <json:json-to-object-transformer doc:name="JSON to Object" returnClass="java.util.Map"/>
    <choice doc:name="Choice">
        <when expression="payload.storeId == null || payload.storeId &lt;= 0 ">
            <set-variable variableName="query" value="select e.employeeid,e.companyid,e.firstName,e.lastName from employees e where companyid = #[message.payload.companyId]" doc:name="CompanyId"/>
        </when>
        <otherwise>
            <set-variable variableName="query" value="select e.employeeid,e.companyid,e.firstName,e.lastName from employees e where employeeid in (select employeeid from employeestores where storeid =#[message.payload.storeId] )" doc:name="StoreId"/>
        </otherwise>
    </choice>
    <jdbc-ee:outbound-endpoint exchange-pattern="request-response"  queryTimeout="-1" doc:name="EmployeeListFetch" connector-ref="Theatro" queryKey="employees_list">
        <jdbc-ee:query key="employees_list" value="#[flowVars['query']]"/>
    </jdbc-ee:outbound-endpoint>
    <json:object-to-json-transformer doc:name="Object to JSON"/>
</flow>

Exception :

Root Exception stack trace: java.lang.IllegalArgumentException: No SQL Strategy found for SQL statement: #[flowVars['query']] at com.mulesoft.mule.transport.jdbc.sqlstrategy.EESqlStatementStrategyFactory.create(EESqlStatementStrategyFactory.java:109) at org.mule.transport.jdbc.JdbcMessageDispatcher.doSend(JdbcMessageDispatcher.java:69) at org.mule.transport.AbstractMessageDispatcher.process(AbstractMessageDispatcher.java:81) + 3 more (set debug level logging or '-Dmule.verbose.exceptions=true' for everything)

Here I want to build dynamically jdbc query based on input.

json data {"companyId":"1","storeId":"1"} Please help me.

2
mule
Have you tried anything? – Chris Leyva
no. I tried with this code only – user3169580

2 Answers

0
votes

try using something like the following for jdbc dynamic outbound endpoints:

<outbound-endpoint address="jdbc://select * from ${datasource.table} #[variable:where-statement]" exchange-pattern="request-response"/>

HTH

0
votes

There are 2 mistakes here,
In Choice the expression is missing []
Something like <when expression="[payload.storeId == null || payload.storeId &lt;= 0] ">
and next your query should be something like this :-

select e.employeeid,e.companyid,e.firstName,e.lastName from employees e where employeeid in (select employeeid from employeestores where storeid ='#[message.payload.storeId]' )

Where #[message.payload.storeId] should be inside '' as storeId is a String