SQL Update statement with MEL expression - Mule

Question

I have a SQL UPDATE statement, to update a particular record to the Database within a mule flow. I am having problems with including MEL expression within the statement and the SQL is not getting executed in my flow.

I am trying the next command:

UPDATE CUSTOMERS SET STATUS=#[flowVars['TxnStatus']] WHERE REF_NUM=#[flowVars['ReferenceNumber']]

where TxnStatus and ReferenceNumber are flow variables in my mule Flow. It looks seemingly simple, but the record is not updated.

When I try a similar SELECT statement with MEL expressions it does retrieve the value for me. Please let me know your thoughts.

Thanks,

Added config file:

<?xml version="1.0" encoding="UTF-8"?>

   <jdbc-ee:mssql-data-source name="MuleDB_DataSource" user="${MuleDB_User}" password="${MuleDB_Password}" url="${MuleDB_URL}" transactionIsolation="UNSPECIFIED" doc:name="MS SQL Data Source"/>
    <jdbc-ee:connector name="Database" dataSource-ref="MuleDB_DataSource" validateConnections="true" queryTimeout="-1" pollingFrequency="10000" doc:name="Database" transactionPerMessage="false">
    </jdbc-ee:connector>
    <flow name="ExceptionFlowFlow1" doc:name="ExceptionFlowFlow1">
        <vm:inbound-endpoint exchange-pattern="one-way" path="Exception" doc:name="VM"/>
        <set-variable variableName="ExceptionPayload" value="#[payload]" doc:name="ExceptionPayload"/>
        <set-variable variableName="TxnStatus" value="Failure" doc:name="Variable"/>        
        <logger message="#[variable:TxnStatus]" level="INFO" category="Status" doc:name="Logger"/>
        <jdbc-ee:outbound-endpoint exchange-pattern="one-way" queryKey="Update_Status" queryTimeout="-1" connector-ref="Database" doc:name="Update_Status">
            <jdbc-ee:query key="Update_Status" value="UPDATE CUSTOMERS SET STATUS=#[flowVars['TxnStatus'] WHERE PAYMENT_REFERENCE_NUMBER=#[flowVars['TxnReference']"/>
         </jdbc-ee:outbound-endpoint>
        <logger message="#[payload]" level="INFO" doc:name="Logger"/>
        <catch-exception-strategy doc:name="Catch Exception Strategy">
            <logger message="#[exception]" level="INFO" category="ExceptionFlow failed with the exception --" doc:name="Logger"/>
        </catch-exception-strategy>
    </flow>
</mule>

1) look for error messages in the Mule console 2) turn on logging for your database and look at the incoming SQL statement. — Anton Kupias
@AntonKupias - Thanks for your reply. I do not have access to the database logs, and on the mule console, I am not seeing any error message. — jvas
Try to log the statement & vars with logger, as well as the class names for the vars. JDBC conversions between numbers and strings can be an issues as well if you set vars with a different data type than the db expects. Var classes/values and db field types are the minimum information to get started with reproducing the problem if you have no error logs. — Anton Kupias

Neeraj Neeraj · Accepted Answer · 2014-06-06T12:05:53

I can see difference between value you used in query key and command given by you above

value="UPDATE CUSTOMERS SET STATUS=#[flowVars['TxnStatus'] WHERE PAYMENT_REFERENCE_NUMBER=#[flowVars['TxnReference']"/>

Both are missing end ] in query value. Correct it and try again.

SQL Update statement with MEL expression - Mule

3 Answers