Announcement Announcement Module
Collapse
No announcement yet.
Integration JDBC, stored proc outbound gateway Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Integration JDBC, stored proc outbound gateway

    Hi everybody,
    I'm trying to use the stored-proc-outbound-gateway to integrate a stored proc to perform some data base lookup, it' supposed to accept a single String param as input and to return a single String (which is actually an XML built in the DB)

    <int-jdbc:stored-proc-outbound-gateway id="outbound-gateway-procedure" request-channel="procedureRequestChannel" data-source="dataSource"
    stored-procedure-name="PKG_INTERF_01.GET_MAIN_ADDR_BY_BP_SYM" expect-single-result="true" is-function="false">
    <int-jdbcarameter name="I_BP_SYM" expression="payload"/>
    </int-jdbc:stored-proc-outbound-gateway>

    So far so good,
    the only thing is that I cannot pass the storedProc package as part of the proc name, I'm ending with the following error:


    org.springframework.dao.InvalidDataAccessApiUsageE xception: Unable to determine the correct call signature for PKG_INTERF_01.GET_MAIN_ADDR_BY_BP_SYM - package name should be specified separately using '.withCatalogName("PKG_INTERF_01")'
    at org.springframework.jdbc.core.metadata.GenericCall MetaDataProvider.processProcedureColumns(GenericCa llMetaDataProvider.java:308)
    at org.springframework.jdbc.core.metadata.GenericCall MetaDataProvider.initializeWithProcedureColumnMeta Data(GenericCallMetaDataProvider.java:102)

    Any help ss greatly appreciated!!!

  • #2
    wish i could help! good luck!

    Comment


    • #3
      Hello

      You should use in such case ignore-column-meta-data="true" and also should define every procedure parameter:
      HTML Code:
      <jdbc:sql-parameter-definition name="I_BP_SYM"/>
      Automatic reading of procedure MetaData doesn't support by many data bases.
      So, my recomendation: always use ignore-column-meta-data="true" and define all procedure parameters directly in the adapter config.

      Good luck,
      Artem Bilan

      Comment


      • #4
        Originally posted by Cleric View Post
        Automatic reading of procedure MetaData doesn't support by many data bases.
        So, my recomendation: always use ignore-column-meta-data="true" and define all procedure parameters directly in the adapter config.
        Good luck,
        Artem Bilan
        Thanks for the quick reply, of course the following question is how?

        I'm trying something like that:
        HTML Code:
        <int-jdbc:sql-parameter-definition name="I_BP_SYM"
                                            direction="IN"
                                            type="STRING"/>
        but it doesn't work.

        The database is Oracle 11G and I'm using oracle6 driver.

        the stored proc is declared as follows:

        HTML Code:
        DECLARE 
          RetVal VARCHAR2(32767);
          I_BP_SYM VARCHAR2(32767);
        
        BEGIN 
          I_BP_SYM := 'a1234';
        
          RetVal := SIGNBOOK_CREATOR.PKG_INTERF_01.GET_MAIN_ADDR_BY_BP_SYM ( I_BP_SYM );
          DBMS_OUTPUT.PUT_LINE(RetVal);
          COMMIT; 
        END;
        and I'm using the following configuration:
        HTML Code:
            <bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close">
                <property name="connectionCachingEnabled" value="true"/>
                <property name="URL" value="jdbc:oracle:thin:@xxxxxxxxxxxxxxxxxxxxt:11000:xxxxxxxxxxxx"/>
                <property name="password" value="xxxxxxxxxxxx"/>
                <property name="user" value="xxxxxxxxxxxxx"/>
                <property name="connectionCacheProperties">
                    <props merge="default">
                        <prop key="MinLimit">3</prop>
                        <prop key="MaxLimit">20</prop>
                    </props>
                </property>
            </bean>
            <int:channel id="procedureRequestChannel"/>
            <int:channel id="replyChannel"/>
        
            <int:gateway id="gateway" default-request-timeout="5000"
                         default-reply-timeout="5000"
                         default-request-channel="procedureRequestChannel"
                         service-interface="ch.bsource.signbook.interfaces.db.SignBookBp">
                <int:method name="lookUp" request-channel="procedureRequestChannel"/>
            </int:gateway>
        
        
            <int-jdbc:stored-proc-outbound-gateway id="outbound-gateway-procedure"
                                                   request-channel="procedureRequestChannel"
                                                   data-source="dataSource"
                                                   stored-procedure-name="SIGNBOOK_CREATOR.PKG_INTERF_01.GET_MAIN_ADDR_BY_BP_SYM"
                                                   expect-single-result="true"
                                                   is-function="false"
                                                   ignore-column-meta-data="true"
                                                   use-payload-as-parameter-source="false">
        
                <int-jdbc:sql-parameter-definition name="I_BP_SYM"
                                            direction="IN"
                                            type="STRING"
                                            scale="32767"/>
        
        
            </int-jdbc:stored-proc-outbound-gateway>

        Comment


        • #5
          the stored proc is declared as follows:
          No, you've shown how you call procedure in the Oracle, but not procedure declaration. It should be something like this:
          Code:
          PROCEDURE get_extregdates(in_registryid IN registry.id%TYPE,
                                      out_dates     OUT SYS_REFCURSOR);
          And also if you use use-payload-as-parameter-source="false" you should declare values for you IN-parameters, as you show it in your first post.

          Comment


          • #6
            Hi CLeric,
            here it is:
            HTML Code:
               PROCEDURE GET_MAIN_BP_ADDR_BY_BP_SYM  (I_BP_SYM IN  VARCHAR2,
                                                      O_XML    OUT VARCHAR2);
            thanks for the reply, next question the parameter value is the payload, as the interface has just a method:
            HTML Code:
            @Component
            public interface SignBookBp {
            
                public String lookUp(String BP);
            
            }

            Comment


            • #7
              So, your config should be like this:
              HTML Code:
              <int-jdbc:stored-proc-outbound-gateway id="outbound-gateway-procedure"
                                                         request-channel="procedureRequestChannel"
                                                         data-source="dataSource"
                                                         stored-procedure-name="SIGNBOOK_CREATOR.PKG_INTERF_01.GET_MAIN_ADDR_BY_BP_SYM"
                                                         expect-single-result="true"
                                                         ignore-column-meta-data="true">
                      <int-jdbc:sql-parameter-definition name="I_BP_SYM"
                                                  direction="IN"
                                                  type="STRING"
                                                  scale="32767"/>
                      <int-jdbc:sql-parameter-definition name="O_XML"
                                                  direction="OUT"
                                                  type="STRING"/>
                       <int-jdbc:parameter name="I_BP_SYM" expression="payload"/>
                  </int-jdbc:stored-proc-outbound-gateway>

              Comment


              • #8
                Cleric,
                I already did try something like that but I'm getting the following exception:

                HTML Code:
                Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.integration.jdbc.StoredProcOutboundGateway#0': Cannot create inner bean '(inner bean)' of type [org.springframework.jdbc.core.SqlParameter] while setting bean property 'sqlParameters' with key [0]; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name '(inner bean)#3': Could not resolve matching constructor (hint: specify index/type/name arguments for simple parameters to avoid type ambiguities)
                any Idea what am i doing wrong?

                Comment


                • #9
                  O-h! No!

                  Sorry, it's my mistake
                  type="STRING" isn't java.sql.Types.
                  So, use VARCHAR or remove this attribute at all. He is VARCHAR by default.
                  And about our issue for type="STRING" you're free to open JIRA: https://jira.springsource.org/browse/INT
                  It's realy not freandly behaviour about wrong value for attribute...

                  Comment


                  • #10
                    You're the man!
                    thank you so much for the assistance, you saved my day.
                    I think I can live with the VARCHAR/STRING issue, now I've documented it!

                    Comment


                    • #11
                      Hello!

                      I've got the same problem and solved it with solution described here.
                      But I'm still confused a little. I understand that I can set ignore-column-meta-data to true, then I have to define all procedure parameters as <sql-parameter-definition>. But why this affects my ability to specify procedure name with its package? It looks like some workaround and it enlarges XML definition of my gateway. Is there any way to specify package and procedure with ignore-column-meta-data="false" and no parameter definitions?

                      Regards,
                      -- Alexey

                      Comment

                      Working...
                      X