Can JDBC outbound-channel-adapter invoke a database function?

[gmposkos]

Hi all,

Is it possible for a JDBC outbound-channel-adapter to invoke a database function, instead of performing an insert or update query?

If not, are there any alternatives in order to achieve this?

Thanks for your help.

[oleg.zhurakousky]

You can have a service-activator with custom class which would invoke the DB function.
Does that explain?

[gmposkos]

Hi Oleg, thanks for the reply.

This gives me a hint more or less. I will try to elaborate on your proposed solution and probably come up with new questions/details.

[gmposkos]

Hi again Oleg,

Is that what you meant by defining a method in custom class?

@ServiceActivator
    public void invokeDBFunction() throws SQLException {
        Connection con = null;
        CallableStatement cstmt = null;
        try {
            con = ds.getConnection();
            try {
                cstmt = ds.getConnection().prepareCall("select fire(?, ?)");
                cstmt.setInt(1, 100);
                cstmt.setString(2, "One Hundred");
                cstmt.executeUpdate();
            } catch (SQLException sqle) {
                if (cstmt != null) {
                    cstmt.close();
                }
            }
        } catch (SQLException sqle) {
            if (con != null) {
                con.close();
            }
        }
    }

 <service-activator input-channel="channelIn"
                       output-channel="channelOut"
                       ref="service"
                       method="invokeDBFunction"/>

[Gary Russell]

Do you not need any data from the message in channelIn?

By returning void, no response will be sent to channelOut.

Also, you might want to consider subclassing Spring's StoredProcedure class; that way you can avoid all that nasty JDBC connection handling code - let Spring do it for you.