1

I have a Mule flow which is fetching Data from Database using Mule 3.5 Database connector ... My Mule Flow is following :-

<flow name="BestelItems" doc:name="BestelItems">
 <poll doc:name="Poll">
 <fixed-frequency-scheduler frequency="30" timeUnit="SECONDS"/>
  <db:select config-ref="Generic_Database_Configuration" doc:name="Database">
     <db:parameterized-query><![CDATA[select * from getData]]></db:parameterized-query>
 </db:select>
  </poll>
<mulexml:object-to-xml-transformer doc:name="Object to XML"/>
<logger message="Payload :- #[message.payload]" level="INFO" doc:name="Logger" />
</flow>

Now this works fine and fetch all the Data from DB and display in console using logger ..

Now the issue is, if I try to read the query from a property file then it throws an exception ... for example if I put the SQL query select * from getData in a Property file like the following :- QueryFromPropertyfile= select * from getData and then try to read the value in the flow like :-

 <db:select config-ref="Generic_Database_Configuration" doc:name="Database">
   <db:parameterized-query><![CDATA[${QueryFromPropertyfile}]]></db:parameterized-query>
 </db:select>

Then it generate the following exception :-

ERROR 2014-08-02 22:39:51,064 [pool-14-thread-1] org.mule.exception.DefaultSystemExceptionStrategy: Caught exception in Exception Strategy: Query type must me '[SELECT, STORE_PROCEDURE_CALL]' but was 'DDL'
java.lang.IllegalArgumentException: Query type must me '[SELECT, STORE_PROCEDURE_CALL]' but was 'DDL'
    at org.mule.module.db.internal.processor.AbstractDbMessageProcessor.validateQueryType(AbstractDbMessageProcessor.java:164)
    at org.mule.module.db.internal.processor.AbstractSingleQueryDbMessageProcessor.executeQuery(AbstractSingleQueryDbMessageProcessor.java:40)
    at org.mule.module.db.internal.processor.AbstractDbMessageProcessor.process(AbstractDbMessageProcessor.java:66)
    at org.mule.transport.polling.MessageProcessorPollingMessageReceiver$1.process(MessageProcessorPollingMessageReceiver.java:164)
    at org.mule.transport.polling.MessageProcessorPollingMessageReceiver$1.process(MessageProcessorPollingMessageReceiver.java:148)
    at org.mule.execution.ExecuteCallbackInterceptor.execute(ExecuteCallbackInterceptor.java:16)
    at org.mule.execution.HandleExceptionInterceptor.execute(HandleExceptionInterceptor.java:30)
    at org.mule.execution.HandleExceptionInterceptor.execute(HandleExceptionInterceptor.java:14)
    at org.mule.execution.BeginAndResolveTransactionInterceptor.execute(BeginAndResolveTransactionInterceptor.java:54)
    at org.mule.execution.ResolvePreviousTransactionInterceptor.execute(ResolvePreviousTransactionInterceptor.java:44)
    at org.mule.execution.SuspendXaTransactionInterceptor.execute(SuspendXaTransactionInterceptor.java:50)
    at org.mule.execution.ValidateTransactionalStateInterceptor.execute(ValidateTransactionalStateInterceptor.java:40)
    at org.mule.execution.IsolateCurrentTransactionInterceptor.execute(IsolateCurrentTransactionInterceptor.java:41)
    at org.mule.execution.ExternalTransactionInterceptor.execute(ExternalTransactionInterceptor.java:48)
    at org.mule.execution.RethrowExceptionInterceptor.execute(RethrowExceptionInterceptor.java:28)
    at org.mule.execution.RethrowExceptionInterceptor.execute(RethrowExceptionInterceptor.java:13)
    at org.mule.execution.TransactionalErrorHandlingExecutionTemplate.execute(TransactionalErrorHandlingExecutionTemplate.java:109)
    at org.mule.execution.TransactionalErrorHandlingExecutionTemplate.execute(TransactionalErrorHandlingExecutionTemplate.java:30)
    at org.mule.transport.polling.MessageProcessorPollingMessageReceiver.pollWith(MessageProcessorPollingMessageReceiver.java:147)
    at org.mule.transport.polling.MessageProcessorPollingMessageReceiver.poll(MessageProcessorPollingMessageReceiver.java:138)
    at org.mule.transport.AbstractPollingMessageReceiver.performPoll(AbstractPollingMessageReceiver.java:216)
    at org.mule.transport.PollingReceiverWorker.poll(PollingReceiverWorker.java:80)
    at org.mule.transport.PollingReceiverWorker.run(PollingReceiverWorker.java:49)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:351)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:178)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:724)
ERROR 2014-08-02 22:40:20,667 [pool-14-thread-1] org.mule.exception.DefaultSystemExceptionStrategy: Caught exception in Exception Strategy: Query type must me '[SELECT, STORE_PROCEDURE_CALL]' but was 'DDL'
java.lang.IllegalArgumentException: Query type must me '[SELECT, STORE_PROCEDURE_CALL]' but was 'DDL'
    at org.mule.module.db.internal.processor.AbstractDbMessageProcessor.validateQueryType(AbstractDbMessageProcessor.java:164)
    at org.mule.module.db.internal.processor.AbstractSingleQueryDbMessageProcessor.executeQuery(AbstractSingleQueryDbMessageProcessor.java:40)
    at org.mule.module.db.internal.processor.AbstractDbMessageProcessor.process(AbstractDbMessageProcessor.java:66)
    at org.mule.transport.polling.MessageProcessorPollingMessageReceiver$1.process(MessageProcessorPollingMessageReceiver.java:164)
    at org.mule.transport.polling.MessageProcessorPollingMessageReceiver$1.process(MessageProcessorPollingMessageReceiver.java:148)
    at org.mule.execution.ExecuteCallbackInterceptor.execute(ExecuteCallbackInterceptor.java:16)
    at org.mule.execution.HandleExceptionInterceptor.execute(HandleExceptionInterceptor.java:30)
    at org.mule.execution.HandleExceptionInterceptor.execute(HandleExceptionInterceptor.java:14)
    at org.mule.execution.BeginAndResolveTransactionInterceptor.execute(BeginAndResolveTransactionInterceptor.java:54)
    at org.mule.execution.ResolvePreviousTransactionInterceptor.execute(ResolvePreviousTransactionInterceptor.java:44)

Please help .. what should I need to do to read SQL queries from properties file in the new Mule 3.5 Database connector .. I have searched all through the net .. but couldn't able to find solution ...

Anirban Sen Chowdhary
  • 8,233
  • 6
  • 39
  • 81
  • How do you load the properties file? Are you sure `${QueryFromPropertyfile}` is correctly resolved? – David Dossot Aug 02 '14 at 17:27
  • Hi David .. I used context:property-placeholder to load the property file and I am able to print ${QueryFromPropertyfile} value in a logger successfully like :- print successfully the value select * from getData – Anirban Sen Chowdhary Aug 02 '14 at 17:55

2 Answers2

3

It seems like for any reason, properties can be used in DB queries (feels like a bug IMO, I suggest you report it to MuleSoft).

So another option would be to create a flow variable with the query in it and use it:

<flow name="the_flow">
  <poll>
     <fixed-frequency-scheduler frequency="30" timeUnit="SECONDS"/>
     <processor-chain>
       <set-variable variableName="selectQuery" value="${QueryFromPropertyfile}" />
       <db:select config-ref="Generic_Database_Configuration">
         <db:dynamic-query>#[flowVars.selectQuery]</db:dynamic-query>
       </db:select>
     </processor-chain>
  </poll>
  ..
</flow>
David Dossot
  • 33,403
  • 4
  • 38
  • 72
  • Hi David.. I have made the change you suggested .. But it's showing the same exception :- Query type must me '[SELECT, STORE_PROCEDURE_CALL]' but was 'DDL' ... Is this the right way I am following to call from a properties file ?? – Anirban Sen Chowdhary Aug 02 '14 at 18:24
  • Are you sure there are no other whitespaces around ${QueryFromPropertyfile} in your XML configuration? – David Dossot Aug 02 '14 at 20:45
  • Yes I removed all the white spaces and checked ... It result the same .. Wondering how this new DB connector takes from properties file – Anirban Sen Chowdhary Aug 02 '14 at 20:56
  • Spring should resolve these properties even before Mule bootstraps so the fact you use a property should be transparent to the DB connector. – David Dossot Aug 02 '14 at 21:35
  • So.. what should I do now .. what should be my approach ? .. How can I resolve it ?? – Anirban Sen Chowdhary Aug 02 '14 at 21:40
  • Can you put a breakpoint on https://github.com/mulesoft/mule/blob/mule-3.5.1/modules/db/src/main/java/org/mule/module/db/internal/parser/SimpleQueryTemplateParser.java#L71 and check the value of sql? – David Dossot Aug 02 '14 at 21:42
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/58555/discussion-between-david-dossot-and-anirban-sen-chowdhary). – David Dossot Aug 02 '14 at 21:46
0

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:

<sub-flow name="OpenSubFlow" doc:name="OpenSubFlow">
    <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>
</sub-flow>
granthbr
  • 311
  • 2
  • 8