Integration JDBC, stored proc outbound gateway

[angelo.andreetto]

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!!!

[JaneDo]

wish i could help! good luck!

[Cleric]

Hello

You should use in such case ignore-column-meta-data="true" and also should define every procedure parameter:

<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

[angelo.andreetto]

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:

<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:

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:

    <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>

[Cleric]

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:

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.

[angelo.andreetto]

Hi CLeric,
here it is:

   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:

@Component
public interface SignBookBp {

    public String lookUp(String BP);

}

[Cleric]

So, your config should be like this:

<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>

[angelo.andreetto]

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

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?

[Cleric]

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...

[angelo.andreetto]

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!