I am pretty sure talend should be able to do this task relatively easily, but I am not sure the best way to go about it. I have 100,000 rows of data, but an tSOAP I am calling can only take 100 rows of data per tSOAP call. I would like to execute an tSOAP call on 100 rows each time until I have looped through the full 100,000 row data set. Any advice/recommended components on going about this is much appreciated.
For Oracle DBs, you would have to get the row count first by using a tDBRow component and performing a "SELECT COUNT(*) FROM XXX WHERE YYY". Use a tFlowToIterate component after that and store the only result record (there can be only one) via a tSetGlobalVar in a global variable.
Instead of the second tFileInputXXX component, you would use a tDBInput component and for Oracle, you would use a statement like the following:
SELECT A, B, C, D FROM XXX WHERE YYY AND ROWNUM > " + String.valueOf(((Integer)globalMap.get("tLoop_1_CURRENT_VALUE")))) + " AND ROWNUM < " + String.valueOf(((Integer)globalMap.get("tLoop_1_CURRENT_VALUE"))) +100)
I think PostgreSQL and MySQL (and others) do not have the ROWNUM pseudo-column - you will have to find your own way here...
I am trying to do the same thing in sql server. I am able to get trest call out for one row. In that case i am using value from 1 to row count with step as 1 in tloop component and the output of tdbinput am writing as global variable in tjava. But am not able to do the same in batch of 10. I am able to do till tdbinput by putting where condtion as id >=" + (Integer)globalMap.get("tLoop_1_CURRENT_VALUE") + "and id <= " + (Integer)globalMap.get("tLoop_1_CURRENT_VALUE") +10 . But not able to proceed after that. I tried titeratetoflow with value ((String)globalMap.get("tDBinput_4_QUERY")).
