MULE ESB: Saving result from Sql query in Session Variable

Question

I have created a sample mule application that fetches one row from my database. It fetches USER_NAME and USER_ID from the Database. when I convert the result to JSON or XML I get the output as

[{"USER_ID":"U001","USER_NAME":"Dharmin"}]

Now i want to save USER_ID and USER_NAME in Session variables. Can someone guide me ?

edit: updated the basic flow image

@Anirban was asking you to post the flow XML, not an image. If you switch to the Configuration XML tab you can get the code. That will show all of your settings as well. — SteveS, Sep 11 '14 at 17:58

Anirban Sen Chowdhary · Answer 1 · 2014-09-10T16:10:18.017

1

After converting to JSON Add this :-

<json:json-to-object-transformer returnClass="java.util.HashMap" doc:name="JSON to Object"/>

and after that put the value into session variable using the following :-

<set-session-variable doc:name="Session Variable" value="message.payload.USER_ID" variableName="USER_ID"/>

and

<set-session-variable doc:name="Session Variable" value="message.payload.USER_NAME" variableName="USER_NAME"/>

edited Sep 10 '14 at 16:10

answered Sep 10 '14 at 15:59

Anirban Sen Chowdhary

8,233
6
39
81

i get "Failed to transform from "json" to "java.util.HashMap". Message payload is of type: String (org.mule.api.transformer.TransformerMessagingException). Message payload is of type: String" – Dharmin Shah Sep 10 '14 at 17:29
Could you pls update you question with your Mule flow ? I can then suggest the solution based on your flow – Anirban Sen Chowdhary Sep 10 '14 at 17:31

score 1 · Answer 2 · answered Jan 11 '17 at 21:51

Do you need to use the results from the DB as a Json?

If not, don't even bother converting the values to JSON before saving them to the sessionVars. Access them directly from the Payload after the DB call:

And here's the configuration XML:

<flow name="testsFlow">

    <db:select config-ref="ORacle_DBCP_Config" doc:name="inputdata">
        <db:parameterized-query><![CDATA[SELECT 'U001' AS USER_ID, 'Dharmin' AS USER_NAME FROM DUAL]]></db:parameterized-query>
    </db:select>
    <set-session-variable variableName="userName" value="#[payload[0].USER_NAME]" doc:name="userName"/>
    <set-session-variable variableName="userID" value="#[payload[0].USER_ID]" doc:name="userID"/>
    <logger message="#[&quot;UserID: &quot; + sessionVars.userID + &quot; | UserName: &quot; + sessionVars.userName]" level="INFO" doc:name="Output the test"/>
</flow>

The output of the logger is:

processor.LoggerMessageProcessor: UserID: U001 | UserName: Dharmin

MULE ESB: Saving result from Sql query in Session Variable

2 Answers2