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

  • stored-proc-inbound-channel-adapter examples needed

    Hi,

    I am trying to use stored-proc-inbound-channel-adapter with MS-SQL Server 2008. Could you provide some complete examples for the adapter plus how to configure a jdts data source? My application also have a hibernate 3 session that I could use, but I am not sure how to use with stored-proc-inbound-channel-adapter.

    Thanks,
    Krassimir

  • #2
    Hi,

    Thanks for your question. What specific problems are you running into? Can you maybe provide some additional details? Right now, we have samples for:

    The setup for MS-SQL Server 2008 should be very similar. In fact the Stored Procedure setup is not Spring Integration specific as we simply reuse the functionality provided by the SimpleJdbcCall class: http://static.springsource.org/sprin...eJdbcCall.html

    See also the Spring reference manual at: http://static.springsource.org/sprin...le-jdbc-call-1.

    Please let us know if there are areas in the reference documentation at http://static.springsource.org/sprin...red-procedures that are not clear or lacking and please open a Jira for those.

    Cheers,

    Gunnar

    Comment


    • #3
      Hello Gunnar,

      I appreciate you help.

      Examples you provided helped, but there are for the outbound adapter only.

      Pretty much I am trying to poll a SQL 2008 database using a stored procedure and getting the result set fed into a SI Message, where I can route, filter, transform, etc.

      I managed to configure stored-proc-inbound-channel-adapter to use MS_SQL Server 2008 dataSource. Here is the partial configuration:



      Code:
      	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
      		<property name="driverClass" value="net.sourceforge.jtds.jdbc.Driver" />
      	    <property name="jdbcUrl" value="jdbc:jtds:sqlserver://hostname:1433/dbname"/>
      	    <property name="user" value="username"/>
      	    <property name="password" value="password"/>
      	</bean>
      
      	<int-jdbc:stored-proc-inbound-channel-adapter
      		stored-procedure-name="sp_SpringIntegrationsTest" 
      		data-source="dataSource" 
      		channel="ch1" >
      		<int:poller fixed-rate="5000" />
      	</int-jdbc:stored-proc-inbound-channel-adapter>
      
      	<int:channel id="ch1"></int:channel>
      
      	<int:transformer  method="transform" input-channel="ch1" output-channel="ch2">
      	        <bean id="Transformer1" class="Transformer1" />
      	</int:transformer>
      	
      	<int:channel id="ch2"></int:channel>
      	
      	<int-file:outbound-channel-adapter
      		id="FileOut" directory="c:/temp" channel="ch2">
      	</int-file:outbound-channel-adapter>

      The stored procedure is simple

      Code:
      CREATE PROCEDURE [dbo].[sp_SpringIntegrationsTest] 
      AS
      BEGIN
      
      	SELECT 'SUCCESS'
      END
      GO
      When I execute, I am getting "Received no Message during the poll, returning 'false'" - see below:


      Code:
      DEBUG: org.springframework.jdbc.core.simple.SimpleJdbcCall - Compiled stored procedure. Call string is [{call sp_SpringIntegrationsTest()}]
      DEBUG: org.springframework.jdbc.core.simple.SimpleJdbcCall - SqlCall for procedure [sp_SpringIntegrationsTest] compiled
      DEBUG: org.springframework.jdbc.core.metadata.CallMetaDataContext - Matching [] with []
      DEBUG: org.springframework.jdbc.core.metadata.CallMetaDataContext - Found match for []
      DEBUG: org.springframework.jdbc.core.simple.SimpleJdbcCall - The following parameters are used for call {call sp_SpringIntegrationsTest()} with: {}
      DEBUG: org.springframework.jdbc.core.JdbcTemplate - Calling stored procedure [{call sp_SpringIntegrationsTest()}]
      DEBUG: org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
      DEBUG: com.mchange.v2.resourcepool.BasicResourcePool - trace com.mchange.v2.resourcepool.BasicResourcePool@42f972 [managed: 3, unused: 2, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@12b98b5)
      DEBUG: org.springframework.jdbc.core.JdbcTemplate - CallableStatement.execute() returned 'true'
      DEBUG: org.springframework.jdbc.core.JdbcTemplate - CallableStatement.getUpdateCount() returned -1
      DEBUG: org.springframework.jdbc.core.JdbcTemplate - CallableStatement.getUpdateCount() returned -1
      DEBUG: org.springframework.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource
      DEBUG: com.mchange.v2.resourcepool.BasicResourcePool - trace com.mchange.v2.resourcepool.BasicResourcePool@42f972 [managed: 3, unused: 2, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@12b98b5)
      DEBUG: org.springframework.integration.endpoint.SourcePollingChannelAdapter - Poll resulted in Message: null
      DEBUG: org.springframework.integration.endpoint.SourcePollingChannelAdapter - Received no Message during the poll, returning 'false'
      Seems like the adapter is not creating the Message?

      Please help.

      Krassimir

      Comment


      • #4
        Hi,

        I looks like your Stored Procedure is not returning any values. Using Transact-SQL, you return values using either output parameters or (numeric) return codes. See the following link for more details:

        http://msdn.microsoft.com/en-us/library/ms188655.aspx

        In your case, you need to refactor your Stored Procedure a little bit:

        Code:
        CREATE PROCEDURE [dbo].[SpringIntegrationsTest]
        	@OutValue varchar(100) output
        AS
        BEGIN
            SET NOCOUNT ON;
            SELECT @OutValue = 'SUCCESS'
            --return 5  -- alternatively you can also return a numeric code
        END
        
        GO
        You can then call your Stored Procedure via Spring Integration:

        Code:
        <int-jdbc:stored-proc-inbound-channel-adapter stored-procedure-name="SpringIntegrationsTest"
            data-source="dataSource" expect-single-result="true" channel="loggingChannel">
            <int:poller fixed-rate="4000"/>
        </int-jdbc:stored-proc-inbound-channel-adapter>
        I hope this helps. Please let us know if you are hitting further issues.

        Cheers,

        Gunnar

        PS: In case you want to process return codes, I just noticed that the 'return-value-required' attribute which triggers returns codes to be included in the results, is missing on the Inbound Channel Adapter. I created a Jira for it:

        https://jira.springsource.org/browse/INT-2728

        Comment


        • #5
          Hello Everyone,

          I have a question on this. I am new to using Spring Integration. But let's say we have a stored procedure with multiple inputs, an output value and a result set. How can I map that using the stored-proc-inbound-channel-adapter?

          Code:
          	<int-jdbc:stored-proc-inbound-channel-adapter
          		data-source="dataSource" id="getCaseInfo"
          		channel="getCaseInfo.Channel" stored-procedure-name="getCaseInfo"
          		expect-single-result="true" ignore-column-meta-data="false"
          		is-function="false" skip-undeclared-results="false" >
          	</int-jdbc:stored-proc-inbound-channel-adapter>
          Are there any more examples or references on this?

          Please let me know.

          Thank you,

          Dave

          Comment


          • #6
            Hi!

            with multiple inputs, an output value and a result set
            You need to declare <int-jdbc:sql-parameter-definition> and <int-jdbc:returning-resultset> respectively.
            Take a look into Oracle sample: https://github.com/SpringSource/spri...cedures-oracle
            and read a Reference Manual: http://static.springsource.org/sprin...ig-subelements

            Take care,
            Artem

            Comment

            Working...
            X