Database Connector Output As Payload or Flow Variable

Database Connector is a very commonly used connector in Mule ESB. This connector allows you to connect to any relational databases using JDBC and supports DDL as well as DML queries like Select, Insert, Update, Delete, Create etc.

Usually when you use any connector in Mule, the output of that connector becomes the payload of next message processor. That's how Mule is, I know!

Consider this flow, which gets contact details over the HTTP inbound connector, then uses Database Connector to save it to MySQL database:

<flow name="mule-demoFlow2">
        <http:listener config-ref="HTTP_Listener_Configuration"
            path="/dbconn" doc:name="HTTP" />
        <set-payload value="#[message.inboundProperties.'http.query.params']"
            doc:name="Set Payload" />
        <db:insert config-ref="MySQL_Configuration" doc:name="Database">
            <db:parameterized-query><![CDATA[insert into contacts (firstname,lastname,emailid,zip) values (#[payload.firstName],#[payload.lastName],#[payload.emailid],#[payload.zip]);]]></db:parameterized-query>
        </db:insert>
        <set-payload value="#['All data saved - '+ payload]"
            doc:name="Set Payload" />
        <logger message="#[payload]" level="INFO" doc:name="Logger" />
</flow>

So, let’s send an HTTP request using Postman and look at the output.

When Mule received this request, it performed a database insertion operation using Database Connector. A result of any Insert/Update/Delete SQL is always the number of rows affected by that statement. So in our case, as we inserted one row, the output was 1 and the same got set as a payload for subsequent processors.

Before we returned the response to HTTP, we set payload to the following string. So the output we received on Postman was ‘All data saved – 1’.

<set-payload value="#['All data saved - '+ payload]"
            doc:name="Set Payload" />

But, what if we don’t want Database Connector to replace our output? Let’s say we are just writing a log entry in the database, and the original payload should keep getting processed after log entry. In that case, we don't want to lose our original payload.

There is a scope in Mule called ‘Message Enricher’ that can set the output of the processor in that scope to a flow variable. So we can wrap the db:insert call into an enricher with the target set to ‘#[flowVars.rowsInserted]’. Then the output of the insert statement (i.e. the number of rows inserted) will be set as a flow variable, leaving our original payload unchanged.

<enricher target="#[flowVars.rowsInserted]" doc:name="Message Enricher">
            <db:insert config-ref="MySQL_Configuration" source="#[message.inboundProperties.'http.query.params']" doc:name="Database">
                <db:parameterized-query><![CDATA[insert into contacts (firstname,lastname,emailid,zip) values (#[payload.firstName],#[payload.lastName],#[payload.emailid],#[payload.zip]);]]></db:parameterized-query>
            </db:insert>
</enricher>

Wait, there is another, easier way to do it. Database Connector exposes an advanced property called ‘target’ which adds enricher functionality within connector itself. The default value of ‘target’ is '#[payload]' so the output always gets set as the payload. You can set the target to any expression. Let’s set it to ‘#[flowVars.rowsInserted]’ in our original flow.

Now, if we submit the request again, then we can see a new variable ‘rowInserted’ with value ‘1’ after the database call, and our payload is still the same map with contact details. Also, the output on Postman should be – ‘All data saved – ParameterMap{[firstName=[Manik3], lastName=[M3], emailid=[mm3@test.com], zip=[123455]]}'

The target property could be very useful when we want to do different Select queries in our flow and hold the results in flow variables for later use.

So Keep Coding and see how you can use this in your flow.

Feel free to let me know your thoughts!