CODEWITHSUNDEEP
sqldatabaseselectmulemule4
Viral
Viral

Reputation: 199

Mule Soft Database Select Query with In Where Condition

I am trying to bring all employee details by passing employee id in select query. unfortunately i am not able to get pass list of employee ids.

I have list of employee ids in Array

employee_ids=[100,101,200,2003,400]

In mule i am trying to get all employee details using flowing query 

<db:select config-ref="mysql_Configuration" doc:name="get Employee details">
            <db:sql>
                select * from employee where employee_id in (:EMP_IDS)
            </db:sql>
            <db:input-parameters>
               #[{'EMP_IDS' : vars.listEmployeeIds}]
            </db:input-parameters>          

        </db:select>

It is throwing an error :

********************************************************************************
Message               : Invalid column type.
Error type            : DB:QUERY_EXECUTION

I also try to pass all employee id with comma separate value

Where value of 

listEmployeeIds =[100,101,200,2003,400] joinBy ","

Result = "100,101,200,2003,400"

<db:select config-ref="mysql_Configuration" doc:name="get Employee details">
                <db:sql>
                    select * from employee where employee_id in (:EMP_IDS)
                </db:sql>
                <db:input-parameters>
                   #[{'EMP_IDS' : vars.listEmployeeIds}]
                </db:input-parameters>          

            </db:select>

But its also giving me error As indirectly its converting into String so it is giving me:

Message               : java.sql.SQLSyntaxErrorException: invalid number
.
Error type            : DB:BAD_SQL_SYNTAX

Can anyone help me to resolved this issue?

Upvotes: 0

Views: 3149

Answers (1)

aled
aled

Reputation: 25699

It will not work that way because the Database connector uses JDBC to implement the queries, and JDBC doesn't support parameterization of the IN clause. You can use the alternative method described in the following MuleSoft KB article https://help.mulesoft.com/s/article/How-to-use-dynamic-IN-clause-in-your-query-statement-using-Database-Connector

Upvotes: 1

Related Questions