I have the below requirement.
1.One Spring based application runs on one Unix box which creates entries on a table.1 column called created_on which hold time when it got created.
2.Another spring integration based application which has 30 instances are running on 3 different Unix box with 10 instances on each box(10 different PID). All these application has configured with stored-proc-inbound-channel-adapter which pulls data for 1 sec interval from above table.
The table has one status column which marks to Y once the record has been picked by the store procedure.
Store procedure picks record based on created on and not marked Y.
I don't want used external queues to communicate between the 2 applications.
Doubts:- 1.Is there any chance of getting same record has picked by multiple nodes?
1.Is there any chance of getting same record has picked by multiple nodes?
I don't know how to help you from here, but you shouldn't have any problems if all the logic is encapsulated in that stored proc: no one else is going to pick up the same records as long as the update and select logic there is right.
2.Is it possible to return the result based on a specific no?
Fully unclear what is no in your question, but stored procedures indeed can return results - raw sets or plain OUT params. That's already fully out of scope this thread and Spring Integration responsibility.
3.How to pass the node name to the spring context(stored-proc-inbound-channel-adapter)?
Please, study what is Environment in Spring Framework and its related @Value and properties placeholders: https://docs.spring.io/spring/docs/current/spring-framework-reference/core.html#beans-environment. So, you indeed can propagate some property on the matter into the stored proc param.
- Is there going to be any performance issue when 30 nodes try to access same table records threw a same store procedure?
No, that's not. As far as your RDBMS vendor support concurrent access to the data base.
5.How to deal stored-proc-inbound-channel-adapter when no data has been return by the store procedure?
You can apply something like AbstractMessageSourceAdvice into the poller of the stored-proc-inbound-channel-adapter and check the result in the implementation of:
/**
* Subclasses can take actions based on the result of the poll; e.g.
* adjust the {@code trigger}. The message can also be replaced with a new one.
* @param result the received message.
* @param source the message source.
* @return a message to continue to process the result, null to discard whatever the poll returned.
*/
Message<?> afterReceive(Message<?> result, MessageSource<?> source);