Spring Integration
  1. Spring Integration
  2. INT-2945

For StoredProcedure Inbound Channel Adapter the isFunction property is not set

    Details

      Description

      For the StoredProcedure Inbound Channel Adapter the *isFunction* property is not set on the StoredProcExecutor.

        Issue Links

          Activity

          Show
          Gunnar Hillert added a comment - Issued PR: https://github.com/SpringSource/spring-integration/pull/756
          Hide
          Gunnar Hillert added a comment - - edited

          When executing a Stored Function with Oracle the function call would fail. E.g. one may observe a stacktrace such as the following:

          12:37:52.867 ERROR [task-scheduler-1][org.springframework.integration.handler.LoggingHandler] org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call FIND_ALL_COFFEE_BEVERAGES(?)}]; SQL state [99999]; error code [17004]; Invalid column type: 1111; nested exception is java.sql.SQLException: Invalid column type: 1111
          	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
          	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
          	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
          	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1030)
          	at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1064)
          	at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:388)
          	at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:351)
          	at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:181)
          	at org.springframework.integration.jdbc.StoredProcExecutor.executeStoredProcedure(StoredProcExecutor.java:335)
          	at org.springframework.integration.jdbc.StoredProcExecutor.executeStoredProcedureInternal(StoredProcExecutor.java:325)
          	at org.springframework.integration.jdbc.StoredProcExecutor.executeStoredProcedure(StoredProcExecutor.java:270)
          	at org.springframework.integration.jdbc.StoredProcPollingChannelAdapter.doPoll(StoredProcPollingChannelAdapter.java:142)
          	at org.springframework.integration.jdbc.StoredProcPollingChannelAdapter.poll(StoredProcPollingChannelAdapter.java:114)
          	at org.springframework.integration.jdbc.StoredProcPollingChannelAdapter.receive(StoredProcPollingChannelAdapter.java:98)
          	at org.springframework.integration.endpoint.SourcePollingChannelAdapter.receiveMessage(SourcePollingChannelAdapter.java:111)
          	at org.springframework.integration.endpoint.AbstractTransactionSynchronizingPollingEndpoint.doPoll(AbstractTransactionSynchronizingPollingEndpoint.java:67)
          	at org.springframework.integration.endpoint.AbstractPollingEndpoint$1.call(AbstractPollingEndpoint.java:146)
          	at org.springframework.integration.endpoint.AbstractPollingEndpoint$1.call(AbstractPollingEndpoint.java:144)
          	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          	at java.lang.reflect.Method.invoke(Method.java:597)
          	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319)
          	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
          	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
          	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
          	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
          	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
          	at $Proxy11.call(Unknown Source)
          	at org.springframework.integration.endpoint.AbstractPollingEndpoint$Poller$1.run(AbstractPollingEndpoint.java:236)
          	at org.springframework.integration.util.ErrorHandlingTaskExecutor$1.run(ErrorHandlingTaskExecutor.java:52)
          	at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:48)
          	at org.springframework.integration.util.ErrorHandlingTaskExecutor.execute(ErrorHandlingTaskExecutor.java:49)
          	at org.springframework.integration.endpoint.AbstractPollingEndpoint$Poller.run(AbstractPollingEndpoint.java:231)
          	at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:53)
          	at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
          	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:439)
          	at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
          	at java.util.concurrent.FutureTask.run(FutureTask.java:138)
          	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98)
          	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:206)
          	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
          	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
          	at java.lang.Thread.run(Thread.java:680)
          Caused by: java.sql.SQLException: Invalid column type: 1111
          	at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3950)
          	at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:135)
          	at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:304)
          	at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:393)
          	at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1569)
          	at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:198)
          	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1008)
          	... 40 more
          

          This issue interestingly manifested itself particularly with Oracle while Postgres seemed to be more tolerant with similar calls. It is important to pay attention to what calls are Stored Function calls and what are actual Stored Procedure calls.

          In this particular issue the respective underlying StoredProcExecutor class did not initialize the org.springframework.jdbc.core.simple.SimpleJdbcCall class with SimpleJdbcCall.withFunctionName(storedProcedureName) which will cause a Stored Function-specific query to be executed.

          Show
          Gunnar Hillert added a comment - - edited When executing a Stored Function with Oracle the function call would fail. E.g. one may observe a stacktrace such as the following: 12:37:52.867 ERROR [task-scheduler-1][org.springframework.integration.handler.LoggingHandler] org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call FIND_ALL_COFFEE_BEVERAGES(?)}]; SQL state [99999]; error code [17004]; Invalid column type: 1111; nested exception is java.sql.SQLException: Invalid column type: 1111 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1030) at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1064) at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:388) at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:351) at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:181) at org.springframework.integration.jdbc.StoredProcExecutor.executeStoredProcedure(StoredProcExecutor.java:335) at org.springframework.integration.jdbc.StoredProcExecutor.executeStoredProcedureInternal(StoredProcExecutor.java:325) at org.springframework.integration.jdbc.StoredProcExecutor.executeStoredProcedure(StoredProcExecutor.java:270) at org.springframework.integration.jdbc.StoredProcPollingChannelAdapter.doPoll(StoredProcPollingChannelAdapter.java:142) at org.springframework.integration.jdbc.StoredProcPollingChannelAdapter.poll(StoredProcPollingChannelAdapter.java:114) at org.springframework.integration.jdbc.StoredProcPollingChannelAdapter.receive(StoredProcPollingChannelAdapter.java:98) at org.springframework.integration.endpoint.SourcePollingChannelAdapter.receiveMessage(SourcePollingChannelAdapter.java:111) at org.springframework.integration.endpoint.AbstractTransactionSynchronizingPollingEndpoint.doPoll(AbstractTransactionSynchronizingPollingEndpoint.java:67) at org.springframework.integration.endpoint.AbstractPollingEndpoint$1.call(AbstractPollingEndpoint.java:146) at org.springframework.integration.endpoint.AbstractPollingEndpoint$1.call(AbstractPollingEndpoint.java:144) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) at $Proxy11.call(Unknown Source) at org.springframework.integration.endpoint.AbstractPollingEndpoint$Poller$1.run(AbstractPollingEndpoint.java:236) at org.springframework.integration.util.ErrorHandlingTaskExecutor$1.run(ErrorHandlingTaskExecutor.java:52) at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:48) at org.springframework.integration.util.ErrorHandlingTaskExecutor.execute(ErrorHandlingTaskExecutor.java:49) at org.springframework.integration.endpoint.AbstractPollingEndpoint$Poller.run(AbstractPollingEndpoint.java:231) at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:53) at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:439) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) at java.util.concurrent.FutureTask.run(FutureTask.java:138) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:206) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang. Thread .run( Thread .java:680) Caused by: java.sql.SQLException: Invalid column type: 1111 at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3950) at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:135) at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:304) at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:393) at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1569) at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:198) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1008) ... 40 more This issue interestingly manifested itself particularly with Oracle while Postgres seemed to be more tolerant with similar calls. It is important to pay attention to what calls are Stored Function calls and what are actual Stored Procedure calls. In this particular issue the respective underlying StoredProcExecutor class did not initialize the org.springframework.jdbc.core.simple.SimpleJdbcCall class with SimpleJdbcCall.withFunctionName(storedProcedureName) which will cause a Stored Function-specific query to be executed.

            People

            • Assignee:
              Gunnar Hillert
              Reporter:
              Gunnar Hillert
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: