Announcement Announcement Module
Collapse
No announcement yet.
stored-proc-inbound-channel-adapter wit returning-resultset returning SqlOutParameter Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • stored-proc-inbound-channel-adapter wit returning-resultset returning SqlOutParameter

    Hi,

    I try to use stored-proc-inbound-channel-adapter with returning-resultset.
    The problem is that the returning-resultset is converted to SqlOutParameter, but i should be SqlReturnResultSet.

    When JdbcTemplate is invoking call method the condition if (parameter.isResultsParameter()) is false and the parameter is added to call parameters.


    I use PostgreSql 8.3., Spring Integration 2.1.3.RELEASE and Spring 3.1.2.RELASE.

    Here is my app context.

    How to force to return a result set?

    Code:
    	<context:property-placeholder location="jdbc.properties"/>
    	
    		<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    		<property name="driverClass" value="${jdbc.driverClassName}" />
    	    <property name="jdbcUrl" value="${jdbc.url}"/>
    	    <property name="user" value="${jdbc.username}"/>
    	    <property name="password" value="${jdbc.password}"/>
    	</bean>
    	
    	<int-jdbc:stored-proc-inbound-channel-adapter id="notifications_inbound"
    		stored-procedure-name="aquirenotifications" data-source="dataSource" channel="notifications" is-function="false" ignore-column-meta-data="true" expect-single-result="false">
    		<int:poller fixed-delay="50000"/>
    		<int-jdbc:sql-parameter-definition name="pnotification_type" type="CHAR" direction="IN"/>
    		<int-jdbc:sql-parameter-definition name="papp_code" type="CHAR" direction="IN"/>
    		<int-jdbc:sql-parameter-definition name="pexcluded_app_codes" type="VARCHAR" direction="IN"/>
    		<int-jdbc:sql-parameter-definition name="pmax_results" type="INTEGER" direction="IN" />
    		
    		<int-jdbc:parameter name="pnotification_type" value="EMAIL" />
    		<int-jdbc:parameter name="papp_code" value="TEST1" />
    		<int-jdbc:parameter name="pexcluded_app_codes" value="JAKAS" />
    		<int-jdbc:parameter name="pmax_results" value="2" type="java.lang.Integer"/>
    		<int-jdbc:returning-resultset name="out" row-mapper="my.com.NotificationResultMapper" />
    	</int-jdbc:stored-proc-inbound-channel-adapter>
    
    	<int:channel id="notifications"></int:channel>
    
    	<int:router id="notification_router" expression="payload.notificationType"
    		input-channel="notifications">
    		<int:mapping value="EMAIL" channel="email" />
    		<int:mapping value="SMS" channel="sms" />
    	</int:router>
    
    	<int:channel id="email"></int:channel>
    	<int:channel id="sms"></int:channel>
    Last edited by wojciech_kochman; Oct 17th, 2012, 10:15 AM.

  • #2
    Hi,

    Just a quick status update. As you may be aware, PostgreSQL is not fully supported by the underlying SimpleJdbcCall class (in regards to automatically retrieving the parameter metadata) and thus, PostgreSQL was not part of the testing we did for the Stored Procedure adapter.

    http://static.springsource.org/sprin...le-jdbc-call-2

    However, I will shortly commit a sample to the Spring Integration Samples repository that uses PostgreSQL Stored Procedures/Functions. I got it to work in regards to Stored Procedures as well. It is important to ensure that the ResultSet is returned as part of a Transaction. Otherwise you may see an Exception like this:

    Code:
    Caused by: org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist
    I did my sample using PostgreSQL 9.2.1 and JDBC driver version 9.1-901-1.jdbc4. Here is the relevant Stored Procedure Outbound Gateway declaration:

    Code:
    <int-jdbc:stored-proc-outbound-gateway
    	id="outbound-gateway-storedproc-find-all" data-source="dataSource" ignore-column-meta-data="true"
    	request-channel="findAllProcedureRequestChannel" expect-single-result="true"
    	stored-procedure-name="FIND_ALL_COFFEE_BEVERAGES">
    	<int-jdbc:returning-resultset name="ref"
    		row-mapper="org.springframework.integration.support.CoffeBeverageMapper" />
    </int-jdbc:stored-proc-outbound-gateway>
    I marked a Gateway interface further upstream as transactional (wired using DirectChannels). The simple stored procedure is declared as:

    Code:
    CREATE OR REPLACE FUNCTION find_all_coffee_beverages()
      RETURNS refcursor AS
    $BODY$
    DECLARE
      ref refcursor;
    BEGIN
      OPEN ref FOR SELECT "ID", "COFFEE_NAME", "COFFEE_DESCRIPTION" FROM "COFFEE_BEVERAGES";
      RETURN ref; 
    END;
    $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
    I will update this thread as soon as I push the sample. I hope this provides some helpful insights.

    Cheers,

    Gunnar

    Comment


    • #3
      Hi,

      A PostgreSQL based sample is now available in the Spring Integration Samples GitHub repository at:

      https://github.com/SpringSource/spri...res-postgresql

      I hope this provide some additional valuable insights. Please let us know if this did not help or you run into further issues.

      Cheers,

      Gunnar

      Comment


      • #4
        Hi,

        Thank you very much for a sample.

        I chceked it, but it is with outbound gateway.

        I would like to use stored-proc-inbound-channel-adapter with a pooler component to retrive data periodically from the database and direct it to different channels via router.

        Ofcourse I can do it with a outbound gatway and do some job which will periodically invoke a method from a service.

        My case is a tipical one for a inbound adapter. Is it possible to use it?

        I already done some serivce methods and declared stored procedures without spring integration just spring core and it works fine. I try to use same procedures now with spring integration.

        I would like to benefit from spring integration and do the integration routes with out of the box components.

        Can you check the stored-proc-inbound-channel-adapter? I can provede my sample where there is a problem with ResultSet.

        Comment


        • #5
          Hi,

          Using my provided example as a starting point, I configured it to use a Stored Procedure Inbound Channel Adapter. At least in my instance it worked fine. The configuration is basically exactly the same as for the Outbound Gateway:

          Code:
          	<int-jdbc:stored-proc-inbound-channel-adapter data-source="dataSource" ignore-column-meta-data="true"
          		channel="loggit" expect-single-result="true" stored-procedure-name="FIND_ALL_COFFEE_BEVERAGES">
          		<int-jdbc:returning-resultset name="ref" row-mapper="org.springframework.integration.support.CoffeBeverageMapper"/>
          	</int-jdbc:stored-proc-inbound-channel-adapter>
          I am using a default (global) poller:

          Code:
          <int:poller default="true" fixed-rate="5000">
              <int:transactional/>
          </int:poller>
          Keep in mind that for PostgreSQL, when returning ResultSets, the Poller must be marked as being transactional. I hope this help. Please let me know if you hit further road-blocks.

          Cheers,

          Gunnar

          Comment


          • #6
            Hi Gunnar,

            Thanks for reply.

            I found when the problem appears.

            When the procedure is defined like this problem appears:

            Code:
            CREATE OR REPLACE FUNCTION aquirenotifications(pnotification_type character, papp_code character, pexcluded_app_codes text, pmax_results integer)
              RETURNS SETOF notification_vo AS
            $BODY$
            DECLARE
                results_row notification_vo%rowtype;
            BEGIN
            for results_row in select n.notification_id, n.notification_type, n.app_code, n.notification_app_id, n.recipient, n.subject, n.content, n.status, n.channel_code, n.mime_type, n.created, c.channel_definition 
            from notification n, channel c  
            where notification_type = pnotification_type 
            and n.channel_code = c.channel_code 
            and (app_code = papp_code or papp_code is null) 
            and ( not app_code = ANY( string_to_array(pexcluded_app_codes, ',')) or pexcluded_app_codes is null)
            and status = 1 order by created asc limit pmax_results
            loop
                results_row.status = 2;
                return next results_row;
                update notification set status = 2 where notification_id = results_row.notification_id;
            end loop;
            return;
            END;
            $BODY$
              LANGUAGE 'plpgsql' VOLATILE
              COST 100
              ROWS 1000;
            but when I use cursor as a return object it works:

            Code:
            CREATE OR REPLACE FUNCTION aquirenotifications2(pnotification_type character, papp_code character, pexcluded_app_codes text, pmax_results integer)
              RETURNS SETOF refcursor AS
            $BODY$
            DECLARE
            	ref refcursor;
            BEGIN
            OPEN ref FOR SELECT n.notification_id, n.notification_type, n.app_code, n.notification_app_id, n.recipient, n.subject, n.content, n.status, n.channel_code, n.mime_type, n.created, c.channel_definition 
            from notification n, channel c  
            where notification_type = pnotification_type 
            and n.channel_code = c.channel_code 
            and (app_code = papp_code or papp_code is null) 
            and ( not app_code = ANY( string_to_array(pexcluded_app_codes, ',')) or pexcluded_app_codes is null)
            and status = 1 order by created asc limit pmax_results;
            loop
            	
            	--update notification set status = 2 from ref where notification_id = ref.notification_id;
            	return next ref;
            	
            end loop;
            close ref;
            END;
            $BODY$
              LANGUAGE 'plpgsql' VOLATILE
              COST 100
              ROWS 1000;
            I would like to use the first version of the procedure becasue I would like to do updates inside loop.


            I the first procedure result parameter apperas as input parameter during the procedure call.

            What could be the problem?

            Wojciech

            Comment

            Working...
            X