I want to extract some data from a database and insert it to another database every hour using tasks and I don't know how to start it. The thing is that in the first database the data is uploaded every hour so I only want to extract and insert (in the second database) only the data inserted in the last hour.
I created a task like this:
<task class="org.apache.synapse.startup.tasks.MessageInjector" group="synapse.simple.quartz" name="task.sms.send" xmlns="http://ws.apache.org/ns/synapse">
<trigger interval="3600"/>
<property name="injectTo" value="sequence" xmlns:task="http://www.wso2.org/products/wso2commons/tasks"/>
<property name="message" xmlns:task="http://www.wso2.org/products/wso2commons/tasks">
<a xmlns="">task</a>
</property>
<property name="sequenceName" value="seq.test" xmlns:task="http://www.wso2.org/products/wso2commons/tasks"/>
</task>
What should the code in the sequence look like?
To answer your question. First, you will have to create two Dataservices. One for the source database and one for the destination. The source dataservice should allow you to retrieve records inserted after a given timestamp. So you should be able to pass the TIMESTAMP to the Dataservice and retrieve records inserted in the last hour. (You can also implement this logic in the Query as well. Different Database providers have inbuilt functions to work with time) Then in the sequence, you can have 2 Call mediators, one to retrieve the records and the second to insert the records. (Assuming no data transformation is required). Also note that WSO2 supports batch processing, so no need to insert records one by one. You can refer this.
Having said that, the approach you are trying to follow is ok if you don't mind missing records or processing duplicate entries. If that's a concern you can save the last processed record ID either in the WSO2 registry or somewhere else and get the records after the last processed record.