Announcement Announcement Module
Collapse
No announcement yet.
Handling errors from stored proc with JDBC stored proc outbound channel adapter Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Handling errors from stored proc with JDBC stored proc outbound channel adapter

    Hi,

    I have a stored proc outbound channel adapter:

    Code:
    <int-jdbc:stored-proc-outbound-channel-adapter id="storedProc" channel="channel" data-source="dataSource" 
            stored-procedure-name="USP_DO_SOMETHING" ignore-column-meta-data="true" return-value-required="false">
        <int-jdbc:sql-parameter-definition name="id" type="NUMERIC" scale="20" />
        <int-jdbc:parameter name="id" expression="payload.id"/>
    </int-jdbc:stored-proc-outbound-channel-adapter>
    The stored proc can throw an error, and this is desired behaviour as I don't want the message processing to continue if it cannot finish processing (e.g. if it cannot insert a row due to duplicate key violation).

    I have a logging channel adapter that is subscribed to the errorChannel, and it does pick up the error and logs it as expected, however, I find another LoggingHandler seems to have subscribed and it throws an exception:

    Code:
    16:35:17,702 DEBUG (task-scheduler-4) ework.integration.handler.LoggingHandler:  67 - org.springframework.integration.handler.LoggingHandler#2 received message: [Payload=org.springframework.integration.MessageHandlingException: error occurred in message handler [org.springframework.integration.jdbc.StoredProcMessageHandler#0]][Headers={timestamp=1365402917702, id=0544c51e-bd8a-47a9-8296-60eb8687e415}]
    ...
    16:35:17,712 ERROR (task-scheduler-4) ework.integration.handler.LoggingHandler: 126 - org.springframework.integration.MessageHandlingException: error occurred in message handler [org.springframework.integration.jdbc.StoredProcMessageHandler#0]
    	at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:79)
    	at org.springframework.integration.dispatcher.BroadcastingDispatcher.invokeHandler(BroadcastingDispatcher.java:121)
    	at org.springframework.integration.dispatcher.BroadcastingDispatcher.dispatch(BroadcastingDispatcher.java:112)
    	at org.springframework.integration.channel.AbstractSubscribableChannel.doSend(AbstractSubscribableChannel.java:77)
    	at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:157)
    	at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:128)
    	at org.springframework.integration.core.MessagingTemplate.doSend(MessagingTemplate.java:288)
    	at org.springframework.integration.core.MessagingTemplate.send(MessagingTemplate.java:149)
    	at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.sendMessage(AbstractReplyProducingMessageHandler.java:216)
    	at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.sendReplyMessage(AbstractReplyProducingMessageHandler.java:200)
    	at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.produceReply(AbstractReplyProducingMessageHandler.java:165)
    	at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.handleResult(AbstractReplyProducingMessageHandler.java:159)
    	at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.handleMessageInternal(AbstractReplyProducingMessageHandler.java:141)
    	at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:73)
    	at org.springframework.integration.dispatcher.UnicastingDispatcher.doDispatch(UnicastingDispatcher.java:115)
    	at org.springframework.integration.dispatcher.UnicastingDispatcher.dispatch(UnicastingDispatcher.java:102)
    	at org.springframework.integration.channel.AbstractSubscribableChannel.doSend(AbstractSubscribableChannel.java:77)
    	at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:157)
    	at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:128)
    	at org.springframework.integration.core.MessagingTemplate.doSend(MessagingTemplate.java:288)
    	at org.springframework.integration.core.MessagingTemplate.send(MessagingTemplate.java:149)
    	at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.sendMessage(AbstractReplyProducingMessageHandler.java:216)
    	at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.sendReplyMessage(AbstractReplyProducingMessageHandler.java:200)
    	at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.produceReply(AbstractReplyProducingMessageHandler.java:165)
    	at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.handleResult(AbstractReplyProducingMessageHandler.java:155)
    	at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.handleMessageInternal(AbstractReplyProducingMessageHandler.java:141)
    	at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:73)
    	at org.springframework.integration.dispatcher.UnicastingDispatcher.doDispatch(UnicastingDispatcher.java:115)
    	at org.springframework.integration.dispatcher.UnicastingDispatcher.dispatch(UnicastingDispatcher.java:102)
    	at org.springframework.integration.channel.AbstractSubscribableChannel.doSend(AbstractSubscribableChannel.java:77)
    	at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:157)
    	at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:128)
    	at org.springframework.integration.core.MessagingTemplate.doSend(MessagingTemplate.java:288)
    	at org.springframework.integration.core.MessagingTemplate.send(MessagingTemplate.java:149)
    	at org.springframework.integration.endpoint.SourcePollingChannelAdapter.handleMessage(SourcePollingChannelAdapter.java:97)
    ...
    Caused by: org.springframework.dao.TransientDataAccessResourceException: CallableStatementCallback; SQL [{call USP_DO_SOMETHING(?)}]; Unable to insert because that row already exists..; nested exception is java.sql.SQLException: Unable to insert because that row already exists.
    	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:107)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    	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:294)
    	at org.springframework.integration.jdbc.StoredProcMessageHandler.handleMessageInternal(StoredProcMessageHandler.java:112)
    	at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:73)
    	... 63 more
    Caused by: java.sql.SQLException: Unable to insert because that row already exists.
    	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
    	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
    	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
    	at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:637)
    	at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
    	at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
    	at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:561)
    	at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1066)
    	at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1)
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1014)
    	... 72 more
    (truncated because I'm over the char limit)

    I'm trying to write an integration test that verifies that the java.sql.SQLException is thrown as expected when the stored proc fails to run correctly, but that gets swallowed by the org.springframework.integration.MessageHandlingExc eption pasted above.

    Any ideas where that LoggingHandler#2 is from?

    I see this in the log, but I don't see it configured anywhere in the appContext, as far as I can see, only one thing is configured to listen to the errorChannel:

    Code:
    16:34:17,615  INFO (main) integration.endpoint.EventDrivenConsumer:  83 - Adding {logging-channel-adapter:_org.springframework.integration.errorLogger} as a subscriber to the 'errorChannel' channel
    16:34:17,615  INFO (main) egration.channel.PublishSubscribeChannel:  69 - Channel 'errorChannel' has 2 subscriber(s).
    Ideally, I'd like to just log the message from the SQL exception (e.g. "Unable to insert because that row already exists"), is that possible?

  • #2
    Hi!
    Ideally, I'd like to just log the message from the SQL exception (e.g. "Unable to insert because that row already exists"), is that possible?
    How about this one:
    http://static.springsource.org/sprin...r-advice-chain
    ExpressionEvaluatingRequestHandlerAdvice
    https://github.com/SpringSource/spri...retry-and-more
    http://blog.springsource.org/2012/10...etry-and-more/

    From other side you can provide you own error-channel ref, e.g. to the <poller>, subscribe to it and further make you logic from content of ErrorMessage:
    http://static.springsource.org/sprin...e-errorhandler

    Take care,
    Artem

    Comment


    • #3
      Thanks Artem.

      Unfortunately, jdbc:stored-proc-outbound-channel-adapter doesn't allow <request-handler-advice-chain>.

      From other side you can provide you own error-channel ref, e.g. to the <poller>, subscribe to it and further make you logic from content of ErrorMessage:
      http://static.springsource.org/sprin...e-errorhandler
      I added a second logger:
      Code:
      <int:logging-channel-adapter id="failedCauseLogger" channel="pollerError" expression="'Cause: ' + payload.cause.toString()" level="ERROR" />
      When I created a custom channel:
      Code:
      <int:channel id="pollerError"></int:channel>
      Changed my poller's error channel to that one, and the stack trace seems to be suppressed.

      But when I change the logger to subscribe to the default errorChannel, and the poller's error channel to the default, the full stack trace appears. I think it might be better to have the full stack trace in the log, I just thought that was strange.

      The mysterious LoggingHandler#2 is still there, but it just adds a few extra lines to the log, so it's not a big deal.

      I'm still not sure how I'm going to go about writing that integration test, but if I work it out, I'll post here.

      Thanks again for your help.

      Comment


      • #4
        Hi!
        jdbc:stored-proc-outbound-channel-adapter doesn't allow <request-handler-advice-chain>.
        No, it allows, if you don't declare stored-proc-outbound-channel-adapter within <chain>.
        The mysterious LoggingHandler#2 is still there,
        <errorChannel> is created on start-up internally by Framework, if there is no declared one in the context.
        As you understood this channel is PublishSubscribe. In adition Framework creates a LoggingHandler bean with ERROR logging-level and subscribes it to the <errorChannel>.
        I think it should shed light why you get 'strange' logs.

        Comment


        • #5
          Hi Artem,

          I'm not declaring the stored-proc-outbound-channel-adapter within a <chain>, but when I try to add a request-handler-advice-chain, I get the following error:

          Code:
          cvc-complex-type.2.4.a: Invalid content was found starting with element 'int-jdbc:request-handler-advice-chain'. One of '{"http://www.springframework.org/schema/integration":poller, "http://
           www.springframework.org/schema/integration/jdbc":sql-parameter-definition, "http://www.springframework.org/schema/integration/jdbc":parameter}' is expected.
          Sorry, I'm new to Spring Integration, so if I am doing something wrong, I apologise! I added it like this:

          Code:
          <int-jdbc:stored-proc-outbound-channel-adapter id="storedProc" channel="channel" data-source="dataSource" 
                  stored-procedure-name="USP_DO_SOMETHING" ignore-column-meta-data="true" return-value-required="false">
                      <int-jdbc:request-handler-advice-chain>
                  <bean class="my.CustomAdvice" />
                      </int-jdbc:request-handler-advice-chain>
              <int-jdbc:sql-parameter-definition name="id" type="NUMERIC" scale="20" />
              <int-jdbc:parameter name="id" expression="payload.id"/>
          </int-jdbc:stored-proc-outbound-channel-adapter>

          Comment


          • #6
            Well, as can be seen in the schema, the advice chain has to be the last element...

            Code:
            <xsd:sequence>
            	<xsd:element ref="integration:poller" minOccurs="0" maxOccurs="1" />
            	<xsd:element name="sql-parameter-definition" minOccurs="0"
            		maxOccurs="unbounded" type="sqlParameterDefinitionType">
            		<xsd:annotation>
            			<xsd:documentation><![CDATA[
            				If you are using a database that is fully supported,
            				you typically don't have to specify the Stored Procedure
            				parameter definitions using the 'sql-parameter-definition'
            				attribute.
            
            				Instead, those parameters can be automatically derived
            				from the JDBC Meta-data. However, if you are using
            				databases that are not fully supported or if you like
            				to provide customized parameter definitions, you can
            				set those parameters explicitly. See also the
            				'ignore-column-meta-data' attribute.
            
            				Fully Supported Databases (Stored Procedures):
            
            								* Apache Derby
            								* DB2
            								* MySQL
            								* Microsoft SQL Server
            								* Oracle
            								* PostgreSQL
            								* Sybase
            
            				Fully Supported Databases (Functions)
            
            								* MySQL
            								* Microsoft SQL Server
            								* Oracle
            								* PostgreSQL
            				]]>
            			</xsd:documentation>
            		</xsd:annotation>
            	</xsd:element>
            	<xsd:element name="parameter" minOccurs="0" maxOccurs="unbounded"
            		type="parameterSubElementType">
            		<xsd:annotation>
            			<xsd:documentation>
            				<![CDATA[
            					Provides a mechanism to provide stored procedure
            					parameters.
            				]]>
            			</xsd:documentation>
            		</xsd:annotation>
            	</xsd:element>
            	<xsd:element name="request-handler-advice-chain" type="integration:adviceChainType" minOccurs="0" maxOccurs="1" />
            </xsd:sequence>
            An xsd:sequence means the elements are ordered.

            So move it after the parameter element.
            Last edited by Gary Russell; Apr 9th, 2013, 10:10 PM.

            Comment


            • #7
              Hi Gary,

              Originally posted by Gary Russell View Post
              So move it after the parameter element.
              Even if I put it after the parameter element, I am getting:

              Code:
              cvc-complex-type.2.4.a: Invalid content was found starting with element 'int-jdbc:request-handler-advice-chain'. One of '{"http://www.springframework.org/schema/integration/jdbc":parameter}' is expected.
              I am using spring integration 2.2.1.RELEASE. We are using Microsoft SQL Server.

              I decided to just add a second logger.

              Thanks for the help from both of you!

              In case anyone else is having the same problem, with my integration tests, I couldn't work out a way to extract the SQLException from the payload.cause, as the top level exception being picked up by my test was the MessageHandlingException.

              So I added:

              Code:
                  @Rule
                  public ExpectedException thrown = ExpectedException.none();
              
              @Test
              public void test() {
              
                      thrown.expect(MessageHandlingException.class);
                      thrown.expectMessage("error occurred in message handler");
                      try {
                      // Code to trigger the message process
                      } finally {       
                      // Code to assert that the database is as I expect it to be after the stored proc fails.
                      }
              }

              Comment


              • #8
                Even if I put it after the parameter element, I am getting:
                You should to check your runtime classpath for version of Spring Integration and be sure you don't use version number in the schemaLocation of the config.
                as the top level exception being picked up by my test was the MessageHandlingException
                Correct. You live in the messaging environment and for this distributed, loosely-coupled architecture it isn't interest which Exception was caused. The endpoint catches it and wraps to the MessageHandlingException to say to message bus, that something happened there. But thanks to Exception's stack ability we always can get cause and try to find an Exception in which we are interested.
                So, why do you just stop on payload.cause? What was a cause?

                Comment


                • #9
                  Originally posted by Cleric View Post
                  You should to check your runtime classpath for version of Spring Integration and be sure you don't use version number in the schemaLocation of the config.
                  Runtime classpath says 2.2.1-RELEASE. I'm not sure what you mean by schemaLocation.

                  I am happy with my current solution, so if you'd like to answer other threads, I'm happy to finish this one. However, if you would like to understand why I get the error when trying to use the request-handler-advice-chain, I am happy to answer any questions that I can.

                  Thank you both again for your help.

                  Originally posted by Cleric View Post
                  So, why do you just stop on payload.cause? What was a cause?
                  I just stopped on payload.cause because from a logging point of view, that was enough to display the SQLException (which is what I was trying to get in the first place).

                  Not the prettiest log, but at least it gives some idea of what's wrong before you have to look at the stack trace:

                  Code:
                  Cause: org.springframework.dao.TransientDataAccessResourceException: CallableStatementCallback; SQL [{call USP_DO_SOMETHING}]; Unable to insert because that row already exists.; nested exception is java.sql.SQLException: Unable to insert because that row already exists.
                  I wanted to write an integration test to check that the correct error message was in the SQLException, but I'm now thinking that might be something better suited to DBUnit and testing the stored proc specifically - I should just check that the database state is as I expect it to be.

                  Comment


                  • #10
                    However, if you would like to understand why I get the error when trying to use the request-handler-advice-chain, I am happy to answer any questions that I can.
                    We have a test case for this (StoredProcMessageHandlerParserTests.adviceCalled( )) that works fine...

                    Code:
                    	<int-jdbc:stored-proc-outbound-channel-adapter id="storedProcedureOutboundChannelAdapter" 
                    	                                               data-source="dataSource" channel="target"
                    	                                               stored-procedure-name="testProcedure1">
                    	    <int-jdbc:sql-parameter-definition name="username" direction="IN"    type="VARCHAR"/>
                    	    <int-jdbc:sql-parameter-definition name="password" direction="OUT"                            />
                    	    <int-jdbc:sql-parameter-definition name="age"      direction="INOUT" type="INTEGER"  scale="5"/>    
                    	    <int-jdbc:sql-parameter-definition name="description" />                                          
                    	    <int-jdbc:parameter name="username"    value="kenny"   type="java.lang.String"/>
                    	    <int-jdbc:parameter name="description" value="Who killed Kenny?"/>
                    	    <int-jdbc:parameter name="password"    expression="payload.username"/>
                    	    <int-jdbc:parameter name="age"         value="30"      type="java.lang.Integer"/>
                    	    <int-jdbc:request-handler-advice-chain>
                    	    	<bean class="org.springframework.integration.jdbc.config.StoredProcMessageHandlerParserTests$FooAdvice" />
                    	    </int-jdbc:request-handler-advice-chain>
                    	</int-jdbc:stored-proc-outbound-channel-adapter>
                    Stored proc support was added in 2.1 and the advice chain was added in 2.2; so it certainly looks like it is using the 2.1 schema (by the error message in your post #7).

                    You could set -verbose on the command line and you will see which jar classes are loaded from in the console.

                    Comment

                    Working...
                    X