
Questions: 23,767 //
Answers: 52,373 //
Contributing Members: 18,028
I have a requirement of reading 3 diff queries from 3 diff .sql file residing in src/main/resources/sql folder and then creating a final query uniting them all using "union all" ie. "Query 1 union all query2 union all query 3" and then storing it in some variable or in payload and then executing it in DB Connector dynamically/parameterized. How can I do that in mule using java? Mule Runtime version:- 3.7.2 note:- I created a custom transformer for uniting all the queries and then setting it in a variable and passing the same variable in Db connector using parameterized query but it is giving me an error that "Query type must me '[Select, STORE_PROCEDURE_CALL]' but was 'DDL' (java.lang.IllegalArgumentException)]" 2) I can't use query Template in DB connector because I am getting list of code based on which I decide how many queries should be united using union all. So template is not static.
Any help or suggestions are welcome.
Mar 08, 2018 at 07:01 AM, jayshri_amar_oulkar answered with:
you can achieve this by using properties direcly inside cdata. Step 1. Add query in properties file
`insert.query=Insert into txt_table (SSO_ID,PSID,PTIN) values (#[payload.SSO_ID],#[payload.PSID],#[payload.PTIN])`
Step 2: Use property key inside insert query
**<db:insert config-ref="CSDB_Configuration" bulkMode="true" doc:name="Db call -Insert in table" doc:description="It is DB call to insert data in table">
<db:parameterized-query><![CDATA[${insert.query}]]></db:parameterized-query>
</db:insert>**
Mar 04, 2018 at 03:14 PM, mowmitamandal17 answered with:
Hi,
Please go through the post.It will be helpful for you. https://stackoverflow.com/questions/25097586/how-to-read-sql-queries-from-properties-file-in-mule-3-5-database-connector
Specially this ans: I had the same issue. As @David suggested I added a flow var and inserted the query (from a property).The only difference was I had to add the CDATA section below. I was able to pull the query from a property file after that. Here is my flow:
<set-variable variableName="openQuery" value="${opens}" doc:name="Open Var"/>
<db:select config-ref="MySQL_Configuration" doc:name="Opens">
<db:dynamic-query><![CDATA[#[flowVars.openQuery]]]></db:dynamic-query>
</db:select>
DB connector - invoke PL/SQL Object type OUT parameter 4 Answers
Execute DDL is not working in Mule 4 1 Answer
access sys ref cursor in stored procedure 1 Answer
How can we include the out parameters as an argument in Procedure calls with Dynamic queries 0 Answers
insert SOAP XML to Oracle database via a stored procedure with CLOB as input 0 Answers