Why this simple query is failing in a mule flow?

Question

I have to make some changes in a existing mule flow with little knowledge and although I've spent some days reading online documentation and possible solutions to this, I cannot figure out why this query is failing, as I also have more dynamic queries in my flow with #[xxx] parameters. The query is as follows:

select times from user_request where 
ip_address=SUBSTR(#message.inboundProperties.MULE_REMOTE_CLIENT_ADDRESS],2,INSTR(#[message.inboundProperties.MULE_REMOTE_CLIENT_ADDRESS], ':')-2) 
    and request_date=CAST(CURRENT_DATE as varchar2(8))

And the error I got is:

Message : Index: 0 (java.lang.IndexOutOfBoundsException). Payload : {fecha_solicitud=2016-06-22, moneda=USD, client_id=RIVERA, user_ip=127.0.0.1, request_times=0} Payload Type : java.util.LinkedHashMap Element : /OANDAFlow/processors/3 @ oanda:oanda.xml:126 Element XML : select times from user_requestwhere ip_address=SUBSTR(#[message.inboundProperties.MULE_REMOTE_CLIENT_ADDRESS],2,INSTR(#[message.inboundProperties.MULE_REMOTE_CLIENT_ADDRESS], ':')-2)and request_date=CAST(CURRENT_DATE as varchar2(8))>

Note: The transformation to varchar of the date is because the column request_date is varchar.

I've tried this query directly in the Oracle SQL developer replacing #[message.inboundProperties.MULE_REMOTE_CLIENT_ADDRESS] with and example like /127.0.0.1:55406 and it worked fine so why through mule is failing???

Answer 1

One of the fields in your query expects a string value try to put a single quote..it would work ,

Try this select times from user_request where ip_address=SUBSTR('#message.inboundProperties.MULE_REMOTE_CLIENT_ADDRESS]',2,'INSTR(#[message.inboundProperties.MULE_REMOTE_CLIENT_ADDRESS]', ':')-2) and request_date=CAST(CURRENT_DATE as varchar2(8))