0

Good morning to everybody.

I have an issue with using MEL Expression in a JDBC ORACLE database query.

In particular the Mule flow accepts a Excel file that is transformed in a Map through a Datamapper and its fields are used to make a query in a database in this form:

<jdbc-ee:query key="Fill Table" value="INSERT INTO BUFF_SBIL_ZONALE (DATA_ORA,MACRO_ZONA,SEGNO_SBIL,PRZ_MSD_VEN,PRZ_MSD_ACQ,ID_BIS,FILE_NAME,DATA_LOAD)                                                 
VALUES (TO_DATE(#[map-payload:DATA_ORA], 'DD/MM/YYYY  hh:mi:ss'),#[map-payload:MACROZONA],#[map-payload:SEGNO],#[map-payload:P_medioMSD_VEN],#[map-payload:P_medioMSD_ACQ],#[message.inboundProperties['correlationId']],#[message.InvocationProperty['originalFilename']],SYSDATE)"/>

The query seems correct but when I execute the application, I receive an exception that advice me that #[map-payload:P_medioMSD_VEN] is NULL (correctly, because in the Excel file it does not exist) and the MEL Expression cannot read a NULL value. The problem, so, is not a SQL issue but a MEL issue that does not accept a NULL database field value.

Does anybody know how to bypass the MEL Exception in order to accepts the mullable value?

Thanks in advance to everybody

The exception thrown is: *org.mule.api.expression.RequiredValueException: Expression Evaluator "bean" with expression "xy" returned null but a value was required. *

Paride Letizia
  • 330
  • 1
  • 4
  • 23
  • How about you check if the nullable field is not null before you pass it to query in MEL – Charu Khurana Oct 28 '13 at 12:19
  • First of all thank you for reply. Think about that some fields are NULL in the map and other are not nullable. in this scenario, how can I fill the nullable fields in order to put in the database the righr values (NULL for that fields that have no value and the specified value for that values that are valued). Thanks – Paride Letizia Oct 28 '13 at 13:07
  • if field is Nullable, don't specify it in insert query at all, it will get default value of NULL in db – Charu Khurana Oct 28 '13 at 13:38

2 Answers2

3

I found the solution. I'll write here just for someboby that will have the same problem.

If you want to make a MEL expression value optional (i.e. a NULL value is a right value), you should write a '?' at the end of a MEL expression.

#[map-payload:P_medioMSD_ACQ?]

Putting a '?' the value is optional and a NULL value is accepted

Paride Letizia
  • 330
  • 1
  • 4
  • 23
0

Before passing them to MEL, check if fields are NOT NULL. If you know certain fields will always be NULL, don't specify it in insert query at all, it will get default value of NULL in db

Charu Khurana
  • 4,511
  • 8
  • 47
  • 81
  • Ok, but I don't know how to specify different query dinamically. In other words, I have to specify two or more query depending on a field is null or not, right? How can I do that? How can I modify the query to accept NULL values? Look at the query please Thanks really, of all – Paride Letizia Oct 28 '13 at 14:44
  • you can use `Choice` router for that http://www.mulesoft.org/documentation/display/current/Routing+Message+Processors#RoutingMessageProcessors-Choice – Charu Khurana Oct 28 '13 at 14:46
  • There is no smarter way to allow MEL to accepts NULL values instad of build al lot of database component in a choice router? – Paride Letizia Oct 28 '13 at 14:51
  • Moreover there is a deep problem of MeL. I cannot check if a MEL Expression is NULL because the exception is thrown just when the expression is evalueted and its value is NULL – Paride Letizia Oct 29 '13 at 08:45