How to get handle the Data using MEL if Select SQL returns Null

Question

I have a minor issue in Mule .. I am using Select SQL query like the following :-

 <jdbc-ee:query key="RetriveQuery" value="Select NAME,ID from getData where ID=111 "/>

Now from here I will get the values using MEL like #[message.payload[0].NAME] and

#[message.payload[0].ID]

Now it may be the case that ID=111 doesn't exists .. So, In that case how can I check whether the query returned a blank payload or not using MEL ??..

So far I tried with choice router like the following :-

<choice doc:name="Choice">
      <when expression="#[message.payload[0].ID == null]">

    //Then print in logger like Sorry!! Data not available

Else Print the data in logger using MEL

Whenever I am using this I am getting following exception :-

Exception stack is:
1. Index: 0, Size: 0 (java.lang.IndexOutOfBoundsException)
  java.util.ArrayList:604 (null)
2. [Error: message.payload[0].ID: array index out of bounds.]
[Near : {... message.payload[0].ID == null ....}]
             ^
[Line: 1, Column: 1] (org.mule.mvel2.PropertyAccessException)
  org.mule.mvel2.optimizers.impl.refl.ReflectiveAccessorOptimizer:426 (http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/mvel2/PropertyAccessException.html)
3. Execution of the expression "message.payload[0].ID == null" failed. (org.mule.api.expression.ExpressionRuntimeException)
  org.mule.el.mvel.MVELExpressionLanguage:202 (http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/api/expression/ExpressionRuntimeException.html)
4. Execution of the expression "message.payload[0].ID == null" failed. (org.mule.api.expression.ExpressionRuntimeException). Message payload is of type: ArrayList (org.mule.api.MessagingException)
  org.mule.execution.ExceptionToMessagingExceptionExecutionInterceptor:32 (http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/api/MessagingException.html)
--------------------------------------------------------------------------------
Root Exception stack trace:
java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
    at java.util.ArrayList.rangeCheck(ArrayList.java:604)
    at java.util.ArrayList.get(ArrayList.java:382)
    at org.mule.mvel2.optimizers.impl.refl.ReflectiveAccessorOptimizer.getCollectionProperty(ReflectiveAccessorOptimizer.java:776)
    + 3 more (set debug level logging or '-Dmule.verbose.exceptions=true' for everything)

Please help .. How can I get whether ID exists or not using MEL ???

score 3 · Accepted Answer · answered Sep 20 '14 at 17:27

3

You can use message.payload.isEmpty() is the decision expression.

answered Sep 20 '14 at 17:27

David Dossot

33,403
4
38
72

score 0 · Answer 2 · answered Jul 23 '15 at 14:13

So, thanks to David,
The working code is :-

<jdbc-ee:query key="RetriveQuery" value="Select NAME,ID from getData where ID=111 "/>

<choice doc:name="Choice">
      <when expression="#[message.payload.isEmpty()]">

    //Then print in logger like Sorry!! Data not available

How to get handle the Data using MEL if Select SQL returns Null

2 Answers2