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, Jun 03 '14 at 10:22
@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, Jun 03 '14 at 11:56
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, Jun 03 '14 at 12:32

score 1 · Answer 1 · answered Jun 06 '14 at 12:05

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.

score 0 · Accepted Answer · answered Jun 03 '14 at 08:41

0

I've had similar issues when trying to use a flow variable as an SQL input value. Try replacing #[flowVars['varName']] with #[variable:varName] like so:

UPDATE CUSTOMERS SET STATUS=#[variable:TxnStatus] WHERE REF_NUM=#[variable:ReferenceNumber]

This has worked for me. Hope that helps!

answered Jun 03 '14 at 08:41

danw

1,608
4
29
48

Sorry. :( This is not working for me... Any other suggestions? – jvas Jun 03 '14 at 10:16
Can you amend your question to share your config file? Perhaps there is an error elsewhere. – danw Jun 03 '14 at 10:20
@jvas I believe your error is down to your SQL statement: `UPDATE CUSTOMERS SET STATUS=#[flowVars['TxnStatus'] WHERE PAYMENT_REFERENCE_NUMBER=#[flowVars['TxnReference']` - you're referring to a variable `TxnReference` that hasn't been set anywhere in the config file you've provided - therefore nothing will be updated as the `WHERE` clause in your statement won't point to anything – danw Jun 03 '14 at 13:16
Sorry I missed to mention that. It is a variable from the incoming mule message at the VM endpoint – jvas Jun 03 '14 at 14:28
Try logging it immediately after the `vm` endpoint to see if it's still in the invocation scope: ``, or alternatively try ` – danw Jun 03 '14 at 15:00
I am able to view the variable using a logger. I have resolved the issue for now, please see my comment below. Thanks for your help. – jvas Jun 04 '14 at 09:50

score 0 · Answer 3 · edited May 28 '15 at 08:01

0

UPDATE audit_detail
SET
  status = '3'
WHERE
  request_id = #[flowVars['ses_request_id']
AND
  message_id = #[flowVars['ses_message_id']

edited May 28 '15 at 08:01

Rookie007

1,229
2
18
50

answered May 28 '15 at 07:16

user3777874

11
1

SQL Update statement with MEL expression - Mule

3 Answers3

Linked