Unable to retrieve the values from the database using Mule

Question

I am trying to pass multiple query parameters through browser as

http://localhost:8081/test?SourceQueue=mqinput&PayloadMsgId=11004a90Test0001-3

My flow is as follows, I am setting the query params to the payload using set payload transformer and then passing those values to the stored procedure. But I am not able to retrieve the values from the database. The payload is null after the database connector.

My config xml for the flow is as follows:

    <set-payload value="SourceQueue=#[message.inboundProperties.'http.query.params'.SourceQueue],PayloadMsgId=#[message.inboundProperties.'http.query.params'.PayloadMsgId]" doc:name="Set Payload"/>
    <logger message="#[payload]----------------before db" level="INFO" doc:name="Logger"/>
   <db:stored-procedure config-ref="Generic_Database_Configuration" doc:name="Database">
        <db:parameterized-query><![CDATA[{CALL E_Enquiry(:SourceQueue1,:PayloadMsgId1)}]]></db:parameterized-query>
        <db:in-param name="PayloadMsgId1" type="VARCHAR" value="#[payload.SourceQueue]"/>
        <db:in-param name="SourceQueue1" type="VARCHAR" value="#[payload.PayloadMsgId]"/>

    </db:stored-procedure>
    <logger message="#[payload]----output" level="INFO" doc:name="Logger"/>

My stored procedure is as follows:

CREATE OR REPLACE PROCEDURE E_Enquiry 
    (IN SourceQueue1 VARCHAR(30) DEFAULT NULL,
     IN PayloadMsgId1 VARCHAR(100) DEFAULT NULL) 
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN 
    DECLARE C1 CURSOR WITH RETURN TO CLIENT FOR 
        select PayloadMsgId,ExceptionId,EventSource,E_Message.InterfaceId,
            CreationTime,SourceProtocol,ErrorMessage,Severity,InterfaceName 
        from E_Message,E_Config
        where (SourceQueue=SourceQueue1 and PayloadMsgId=PayloadMsgId1); 
    open c1; 
END;

Please help to resolve this.

Anirban Sen Chowdhary Anirban Sen Chowdhary · Accepted Answer · 2016-03-30T14:38:29

Your expression :-

<set-payload value="SourceQueue=#[message.inboundProperties.'http.query.params'.SourceQueue],PayloadMsgId=#[message.inboundProperties.'http.query.params'.PayloadMsgId]" doc:name="Set Payload"/>

is not correct and you cannot achieve this by setting it into payload as the payload is a String format for now and not an Object.

The ideal solution will be using variables like the following :-

<set-variable variableName="SourceQueuevar" value="#[message.inboundProperties.'http.query.params'.SourceQueue]" doc:name="Set SourceQueuevar"/>

 <set-variable variableName="PayloadMsgIdvar" value="#[message.inboundProperties.'http.query.params'.PayloadMsgId]" doc:name="Set PayloadMsgIdvar"/>

Once this is set, you can easily use it anywhere, in you SQL or logger in the following expression like :- #[flowVars.SourceQueuevar] #[flowVars.PayloadMsgIdvar]

Unable to retrieve the values from the database using Mule

2 Answers