Stored proc outbound gateway null parameter handli

Question

How can i make this code work even if payload.second is null (optional parameter).

<int-jdbc:stored-proc-outbound-gateway id="outGtw" 
                                       data-source="someDs" 
                                       request-channel="someChannel" 
                                       reply-channel="someOtherChannel" 
                                       stored-procedure-name="someStoredProcedure"
                                       ignore-column-meta-data="true">
  <int-jdbc:sql-parameter-definition name="first"/>
  <int-jdbc:sql-parameter-definition name="second"/>
  <int-jdbc:sql-parameter-definition name="outParam" direction="OUT" type="NVARCHAR"/>
  <int-jdbc:parameter name="first" expression="payload.first"/>
  <int-jdbc:parameter name="second" expression="payload.second"/>
</int-jdbc:stored-proc-outbound-gateway>

Getting error when this is null -

   Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: Required input parameter 'second' is missing
    at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:211) ~[spring-jdbc-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1115) ~[spring-jdbc-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1173) ~[spring-jdbc-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:381) ~[spring-jdbc-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:342) ~[spring-jdbc-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:190) ~[spring-jdbc-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.integration.jdbc.StoredProcExecutor.executeStoredProcedureInternal(StoredProcExecutor.java:328) ~[spring-integration-jdbc-4.0.4.RELEASE.jar:na]
    at org.springframework.integration.jdbc.StoredProcExecutor.executeStoredProcedure(StoredProcExecutor.java:297) ~[spring-integration-jdbc-4.0.4.RELEASE.jar:na]
    at org.springframework.integration.jdbc.StoredProcOutboundGateway.handleRequestMessage(StoredProcOutboundGateway.java:60) ~[spring-integration-jdbc-4.0.4.RELEASE.jar:na]
    at org.springframework.integration.handler.AbstractReplyProducingMessageHandler$AdvisedRequestHandler.handleRequestMessage(AbstractReplyProducingMessageHandler.java:313) ~[spring-integration-core-4.0.4.RELEASE.jar:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6.0_31]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) ~[na:1.6.0_31]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) ~[na:1.6.0_31]
    at java.lang.reflect.Method.invoke(Method.java:597) ~[na:1.6.0_31]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) ~[spring-aop-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.integration.handler.advice.AbstractRequestHandlerAdvice$1.cloneAndExecute(AbstractRequestHandlerAdvice.java:92) ~[spring-integration-core-4.0.4.RELEASE.jar:na]
    at org.springframework.integration.handler.advice.RequestHandlerRetryAdvice$2.doWithRetry(RequestHandlerRetryAdvice.java:88) ~[spring-integration-core-4.0.4.RELEASE.jar:na]
    at org.springframework.retry.support.RetryTemplate.doExecute(RetryTemplate.java:263) ~[spring-retry-1.1.1.RELEASE.jar:na]
    at org.springframework.retry.support.RetryTemplate.execute(RetryTemplate.java:193) ~[spring-retry-1.1.1.RELEASE.jar:na]
    at org.springframework.integration.handler.advice.RequestHandlerRetryAdvice.doInvoke(RequestHandlerRetryAdvice.java:85) ~[spring-integration-core-4.0.4.RELEASE.jar:na]
    ... 88 common frames omitted

Answer 1

Thank you for the StackTrace. Looking into that I see only the single place when you can't have the parameter - ExpressionEvaluatingSqlParameterSourceFactory:

public boolean hasValue(String paramName) {
        try {
            Object value = doGetValue(paramName, true);
            if (value == ERROR) {
                return false;
            }
        }
        catch (ExpressionException e) {
            if (logger.isDebugEnabled()) {
                logger.debug("Could not evaluate expression", e);
            }
            if (this.cache) {
                values.put(paramName, ERROR);
            }
            return false;
        }
        return true;
    }

The CallMetaDataContext#matchInParameterValuesWithCallParameters can't populate the second parameter because that hasValue() returns false. And this happens only in case of an ExpressionException.

Switch on DEBUG logging for the org.springframework.integration.jdbc category and you'll the reason.

I guess you show here just only the simple expression (payload.second) representation do not complicate this SO question.