Allow for a sequence of updates on Jdbc endpoints [INT-3068]

[spring-operator]

John Coffie opened INT-3068 and commented

Jdbc endpoints allow to perform a single sql statement by specifying the "update" attribute. At times it would be convenient to perform a sequence of sql statements within the same endpoint.

---

No further details from INT-3068

[spring-operator]

Artem Bilan commented

The most RDBMSs have auto-increment (identity) feature. For this purpose such columns should not be included to the INSERT statement. For others there is specific expression to be included to the statement, e.g. for Oralce:

<int-jdbc:outbound-gateway update="insert into bars (id, status, name) values (MY_SEQUNCE.NEXTVAL, :headers[status], :payload[foo])"/>

For those cases when there is need to invoke sequence manually the Spring Framework provides the DataFieldMaxValueIncrementer abstraction. The vendor-specific incrementer can be deifned as generic bean and used from SqlParameterSourceFactory for update-sql-parameter-source-factory attribute, e.g. as an expression:

<bean id="sqlParameterSourceFactory" class="org.springframework.integration.jdbc.ExpressionEvaluatingSqlParameterSourceFactory">
    <property name="parameterExpressions">
        <map>
            <entry key="nextVal" value="@icrementer.nextKey" />
        </map>
    </property>
</bean>

Is it appropriate for you? Do you have something specific in mind?

[spring-operator]

Gary Russell commented

Artem,

perform a sequence of sql statements within the same endpoint

I believe John is asking for multiple updates, not just using sequences in that context...

insert into foo values( :payload);
insert into bar values( :headers['foo'])
...

Of course, this can be done with a pub-sub channel and multiple adapters.

[spring-operator]

Artem Bilan commented

Oops! OK, we can use for this purpose

int[] batchUpdate(String[] sql) throws DataAccessException;

but as we see this method doesn't support parameters. Is it really reasonable to go ahead, if the stored procedure or a bunch of raw JDBC adapters might be more flexible ?

[spring-operator]

John Coffie commented

I believe a sequence of insert/update/delete statements to be more expressive. However, as mentioned we can continue to use multiple adapters connected with a pub-sub channel. You’ll need to evaluate the tradeoffs and determine if the change is warranted.

[spring-operator]

Artem Bilan commented

Closed as very complex from configuration perspective for the single endpoint. The set of adapters or stored procedure is more appropriate for such cases.