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>

---

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

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

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.

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

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