Announcement Announcement Module
Collapse
No announcement yet.
help stored-proc-outbound-channel-adapter and writing out param using an interceptor Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • help stored-proc-outbound-channel-adapter and writing out param using an interceptor

    Hi all,

    I defined a stored-proc-outbound-channel-adapter that takes 2 params, 1 input as string and 1 output as int.
    It works well but I would like to write out the output parameter value using a wire-tap interceptor, so I tried as below:

    Code:
        <int:channel id="bpListMessageTypeChannel">
            <int:interceptors>
              <int:wire-tap channel="bpListMessageTypeChannel-logger"/>
            </int:interceptors>
        </int:channel>
    
        <int-jdbc:stored-proc-outbound-channel-adapter channel="bpListMessageTypeChannel"
           data-source="dataSource" stored-procedure-name="${stored-procedure-name.bp}" auto-startup="true"
           ignore-column-meta-data="true">
           <int-jdbc:sql-parameter-definition name="I_XML"
                                               direction="IN"
                                               type="VARCHAR"/>
           <int-jdbc:sql-parameter-definition name="O_ERR_CODE "
                                               direction="OUT"
                                               type="INTEGER"/>
           <int-jdbc:parameter name="I_XML" expression="payload"/>
        </int-jdbc:stored-proc-outbound-channel-adapter>
    
        <int:logging-channel-adapter id="bpListMessageTypeChannel-logger"     expression="'### BP List Message Type Channel ### '     + 'O_ERR_CODE=' + this.o_err_code" level="INFO"/>
    But I'm not sure about the correct syntax: ... + 'O_ERR_CODE=' + this.o_err_code"

    Could anyone suggest me any hints about that ?

    Thanks and regards
    nuvola

  • #2
    Hello

    Well, all OUT stored procedure parameter are returned inside a Map<String, Object>, which will be returned as Message's payload after adapter invocation.
    So, you can configure you logger like this:
    HTML Code:
     <int:logging-channel-adapter id="bpListMessageTypeChannel-logger" expression="'### BP List Message Type Channel ### ' + 'O_ERR_CODE=' + payload.O_ERR_CODE" level="INFO"/>
    But let's step back. Do you realy think that your <int-jdbc:stored-proc-outbound-channel-adapter/> return procedure's out-parameters into bpListMessageTypeChannel?
    This adapter is unidirectional. It's about adapting messaging system with DB through stored procedure. So, your bpListMessageTypeChannel takes care about request message with parameters for procedure. And that's all.
    Сonsider any outbound-channel-adapter as void method.

    Hope, I'm clear.

    Cheers,
    Artem Bilan

    Comment


    • #3
      Hello,

      thanks for the clarification.

      I see that if I enable org.springframework.integration.jdbc at DEBUG level from logs I can get details about:
      - StoredProcMessageHandler --> received message
      - ExpressionEvaluatingSqlParameterSource --> resolved expression payload
      - StoredProcMessageHandler --> ignores return values, but the called Stored Procedure ...returned the following data: '{O_ERR_CODE =1}

      as shown below.

      It would be nice to get the same info using as in my example a logging-channel-adapter *** without ***enabling org.springframework.integration.jdbc at DEBUG level :-)

      Code:
      02.mag.2012 14:31:11,137 - (LISTENER SERVICE) (org.springframework.jms.listener.DefaultMessageListenerContainer#0-1) [DEBUG ]-[AbstractMessageHandler             (67  )] - org.springframework.integration.jdbc.StoredProcMessageHandler#0 received message: [Payload=<?xml version="1.0" encoding="ISO-8859-1"?><message ...>][Headers={timestamp=1335961871105, id=423b7ded-304a-4fa9-8c20-4ce49a5fda34, JMSXDeliveryCount=1,...]
      
      02.mag.2012 14:31:12,308 - (LISTENER SERVICE) (org.springframework.jms.listener.DefaultMessageListenerContainer#0-1) [DEBUG ]-[ExpressionEvaluatingSqlParameterSourceFactory$ExpressionEvaluatingSqlParameterSource(136 )] - Resolved expression payload to <?xml version="1.0" encoding="ISO-8859-1"?><message ...">
      
      02.mag.2012 14:31:12,980 - (LISTENER SERVICE) (org.springframework.jms.listener.DefaultMessageListenerContainer#0-1) [DEBUG ]-[StoredProcMessageHandler           (100 )] - The StoredProcMessageHandler ignores return values, but the called Stored Procedure 'MY_CREATOR.PKG_WRITE.PROCESS_BP' returned the following data: '{O_ERR_CODE =1}'
      Thanks and regards
      nuvola

      Comment


      • #4
        Hello,

        in order to get OUT stored procedure parameters returned as Message's payload after adapter invocation, I tried to add to my stored-proc-outbound-channel-adapter a channel-interceptor:

        Code:
            <int:channel id="bpListMessageTypeChannel"/>
        
            <int:channel-interceptor pattern="*ListMessageTypeChannel" order="-1">
              <beans:bean class="ch.integration.jms.interceptor.LoggingJDBCChannelInterceptor"/>
            </int:channel-interceptor>
        
            <int-jdbc:stored-proc-outbound-channel-adapter channel="bpListMessageTypeChannel"
               data-source="dataSource" stored-procedure-name="${stored-procedure-name.bp}" auto-startup="true" ignore-column-meta-data="true">
               <int-jdbc:sql-parameter-definition name="I_XML"
                                                   direction="IN"
                                                   type="VARCHAR"/>
               <int-jdbc:sql-parameter-definition name="O_ERR_CODE "
                                                   direction="OUT"
                                                   type="INTEGER"/>
               <int-jdbc:parameter name="I_XML" expression="payload"/>
            </int-jdbc:stored-proc-outbound-channel-adapter>
        Below my Interceptor class:

        Code:
        public class LoggingJDBCChannelInterceptor extends ChannelInterceptorAdapter {
        ...
            @Override
            public Message<?> preSend(Message<?> message, MessageChannel channel) {
        ...
        }
        
            @Override
            public void postSend(Message<?> message, MessageChannel channel, boolean sent) {
                logger.info("#### - postSend - payload=" + message.getPayload().toString());
            }
        
            @Override
            public Message<?> postReceive(Message<?> message, MessageChannel channel) {
                logger.info("#### - postReceive - payload=" + message.getPayload().toString());
                return message;
            }
        But from logs I see that postSend is called but the payload doesn't contain the OUT stored procedure parameters:

        Code:
        04.mag.2012 13:55:04,833 - (LISTENER SERVICE) (org.springframework.jms.listener.DefaultMessageListenerContainer#0-1) [DEBUG ]-[StoredProcMessageHandler           (100 )] - The StoredProcMessageHandler ignores return values, but the called Stored Procedure 'USER_CREATOR.PKG_WRITE.PROCESS_BP' returned the following data: '{O_ERR_CODE =1}'
        
        04.mag.2012 13:55:04,833 - (LISTENER SERVICE) (org.springframework.jms.listener.DefaultMessageListenerContainer#0-1) [INFO  ]-[LoggingJDBCChannelInterceptor      (61  )] - #### - postSend - payload=<?xml version="1.0" encoding="ISO-8859-1"?>
        <message>
          <header>
            <type>AdvExtract_bp</type>...
        I'm trying to understand where I am wrong ...

        Any help ss greatly appreciated!!!

        Thanks and regards
        nuvola

        Comment


        • #5
          Hmm, adapters do not return anything. Adapters are unidirectional components which means there is no reply from the adapter.
          Also, in any event postSend does not mean a reply. It simply gives you an opportunity to do something after successful (the one that did not result in exception) send.
          Could you explain your use case better? What are you trying to accomplish?

          Comment


          • #6
            My use case:

            - in my flow there is a step where I receive an xml string and I call a stored procedure in order to update a db
            - the store procedure as an output parameter O_ERR_CODE that tells us if the call was successfully or not
            - in the log I would like to trace the O_ERR_CODE value so we can check easily if the call was wrong or not

            I see O_ERR_CODE value only (as said) if I set org.springframework.integration.jdbc at DEBUG level:

            Code:
            04.mag.2012 13:55:04,833 - (LISTENER SERVICE) (org.springframework.jms.listener.DefaultMessageListenerContainer#0-1) [DEBUG ]-[StoredProcMessageHandler           (100 )] - The StoredProcMessageHandler ignores return values, but the called Stored Procedure 'USER_CREATOR.PKG_WRITE.PROCESS_BP' returned the following data: '{O_ERR_CODE =1}'
            But we cannot leave org.springframework.integration.jdbc at DEBUG level otherwise we see also additional info as the message to be processed, in addition normally we are not allowed to use DEBUG level in production environments.
            So I'm trying to find out a way to catch O_ERR_CODE value using an interceptor ... or if there is other ways ...

            Comment


            • #7
              Hello

              What's problem to use here <int-jdbc:stored-proc-outbound-gateway/> ?

              Comment


              • #8
                Hi Cleric,

                it works, as you suggested!

                I used <int-jdbc:stored-proc-outbound-gateway/> instead of <int-jdbc:stored-proc-outbound-channel-adapter channel>.

                Thanks!
                nuvola

                Code:
                    <int-jdbc:stored-proc-outbound-gateway request-channel="personListMessageTypeChannel" reply-channel="personListMessageType-stored-proc-result"
                       data-source="dataSource"
                       stored-procedure-name="${stored-procedure-name.pers}"
                       auto-startup="true"
                       ignore-column-meta-data="true">
                       <int-jdbc:sql-parameter-definition name="I_XML"
                                                           direction="IN"
                                                           type="VARCHAR"/>
                       <int-jdbc:sql-parameter-definition name="O_ERR_CODE "
                                                           direction="OUT"
                                                           type="INTEGER"/>
                       <int-jdbc:parameter name="I_XML" expression="payload"/>
                    </int-jdbc:stored-proc-outbound-gateway>
                    <int:channel id="personListMessageType-stored-proc-result"/>
                    <int:logging-channel-adapter channel="personListMessageType-stored-proc-result"
                       expression="'#### [Channel=personListMessageTypeChannel][Headers=' + headers + '][Payload=' + payload + ']'"
                       level="INFO"/>
                Logging

                Code:
                08.mag.2012 14:27:41,370 - (LISTENER SERVICE) (org.springframework.jms.listener.DefaultMessageListenerContainer#0-1) [INFO  ]-[LoggingHandler                     (136 )] - #### [Channel=personListMessageTypeChannel][Headers={timestamp=1336480061370, ...}][Payload={O_ERR_CODE =1}]

                Comment

                Working...
                X