Mule 4 : Database : How to pass a SQL query as a variable in database "Query Text"?

Question

Scenario : I have formed a SQL query and stored it in a variable vars.query I want to use this variable in the DATABASE SELECT component in the SQL QUERY TEXT window.

To do that, I passed vars.query in the Query text, and ran the application. The application gets deployed successfully but in runtime fails with the DB : EXCEPTION , expected SQL query but found DDL

I have tried the following approach:

in QUERY text added :query and in the Input Parameters refereed the

{
    'query' : vars.hierarchyQuery
}

But I am getting the below error:

org.mule.runtime.core.internal.message.ErrorBuilder$ErrorImplementation
{
  description=Query type must be one of [SELECT, STORE_PROCEDURE_CALL] but query '?' is of type 'DDL'
  detailedDescription=Query type must be one of [SELECT, STORE_PROCEDURE_CALL] but query '?' is of type 'DDL'
  errorType=DB:BAD_SQL_SYNTAX
  cause=org.mule.extension.db.api.exception.connection.BadSqlSyntaxException
  errorMessage=-
  childErrors=[]
}

Answer 1

I solved the issue by doing this : In the Query Text window refer to the sql query containing variable as #[vars.query]

Answer 2

Can be solved by adding $(vars.query) to 'SQL Query Text' field

Reference: https://docs.mulesoft.com/db-connector/1.10/database-connector-select