How to add resultset to csv file in camel

Question

My xml is given below

 <camelContext trace="false" xmlns="http://camel.apache.org/schema/spring">

    <propertyPlaceholder id="placeholder" location="classpath:application.properties" />

    <!--Route:1 for POLLUX Data Processing   -->

 <route id="processPolluxData-Route" startupOrder="1">
<from uri="{{POLLUX_INPUT_PATH}}?noop=true"/>
  <unmarshal ref="csvBindyDataformatForPolluxData"/>
  <camel:bean ref="polluxDataController" method="processPolluxData"/>
  <camel:log message="Line:${body}" loggingLevel="INFO"/> 
<to uri="sqlComponent:{{sql.insertPolluxData}}?batch=true"  /> 
</route> 

     <!-- Route:2 for RSI Data Processing -->

<route id="processRsiData-Route" startupOrder="2">
<from uri="{{RSI_INPUT_PATH}}?noop=true"/>
  <unmarshal ref="csvBindyDataformatForRsiData"/>
  <camel:bean ref="rsiDataController" method="processRsiData"/>
  <camel:log message="Line:${body}" loggingLevel="INFO"/> 
<to uri="sqlComponent:{{sql.insertRsiData}}?batch=true" /> 
</route>

    <!-- Route for Global Data Processing  -->
    <route id="processGlobalData-Route"  >
    <from uri="sqlComponent:{{sql.selectOrder}}?consumer.useIterator=false" />
         <camel:bean ref="globalDataController" method="processGlobalData" />  
        <marshal>
            <csv delimiter=","/>
        </marshal>
        <log message="${body}" />
    <setHeader headerName="camelFilename">
        <constant>result.csv</constant>
    </setHeader>
    <to uri="{{GLOBAL_OUTPUT_PATH}}?fileExist=Append" />
</route>

My sql statement is

sql.selectOrder=select STID,CLLTR,SOURCE from GSI_DEVL.POLLUX_DATA

bean class for processing result set is

public class GlobalDataController {

List<Map<String, Object>> globalStationProccessedList = new ArrayList<Map<String, Object>>();
List<Map<String, Object>> globalStationMap = new ArrayList<Map<String, Object>>();

@SuppressWarnings("unchecked")
public List<Map<String, Object>> processGlobalData(Exchange exchange) throws Exception {

    // System.out.println("Processing " + exchange.getIn().getBody());

    globalStationMap = (List<Map<String, Object>>) exchange.getIn().getBody();
    globalStationProccessedList.addAll(globalStationMap);

    return globalStationProccessedList;
}

}

Problem now is Route 1 data is transffered to csv file with exact number of rows in database.But no data in the route 2 is append to the csv file I am using camel 2.16

score 0 · Answer 1 · answered Jan 13 '16 at 14:43

If the problem only in large number of files (not in the file format), then here is the solution:

  <route id="processOrder-route">
    <from uri="sqlComponent:{{sql.selectOrder}}"/>
    <camel:bean ref="controllerformarshalling" method="processGlobalData"  />
    <marshal >
    <csv delimiter="," useMaps="true" > </csv>
     </marshal>
    <log message="${body}"/>
    <setHeader headerName="CamelFileName">
        <constant>result.csv</constant>
    </setHeader>  
    <to uri="file://D://cameltest//output&amp;fileExist=Append" />
  </route>

For next pool you can set another file name, based on current time, maybe.

score 0 · Answer 2 · answered Jan 14 '16 at 09:02

0

Have you tried setting this parameter on the sql component?

consumer.useIterator boolean true Camel 2.11: SQL consumer only: If true each row returned when polling will be processed individually. If false the entire java.util.List of data is set as the IN body.

Try setting this to false. That way you should get the entire sql resultset into a single list and then write the entire list to a single file.

answered Jan 14 '16 at 09:02

Souciance Eqdam Rashti

3,143
3
15
31

Your solution worked !! Thanks But the result set contains 34000 rows in database, in the file i am getting only 2000 rows.Is there is any limitation ? please help – Bibin Mathew Jan 14 '16 at 11:20
Seems strange, as I don't think there is any default limitation. In camel, what is the value for the exchange header CamelSqlRowCount after getting the resultset? Is it 2000 or 34000? – Souciance Eqdam Rashti Jan 14 '16 at 11:24
Which version of Camel are you using? – Souciance Eqdam Rashti Jan 14 '16 at 11:28
You can also try setting maxMessagesPerPoll=100000 just to see that all 34000 rows are indeed written if you do get 34000 rows back in the Camel resultset. – Souciance Eqdam Rashti Jan 14 '16 at 11:31
Souciance, I have edited the question with code ? The issue is with one route i think.Can you please read the edited part and help me ? – Bibin Mathew Jan 14 '16 at 12:38
Sure, if the answer is correct, perhaps you can mark as well? That would be appreciated. – Souciance Eqdam Rashti Jan 14 '16 at 14:05
By the way, did you check the Camel header and saw that the resultset in Camel did indeed return 34000 and not 2000? – Souciance Eqdam Rashti Jan 14 '16 at 14:06
how can i see the header values for count,which code i used to do that ? can you explain ? – Bibin Mathew Jan 14 '16 at 14:31
You can find it here: http://camel.apache.org/sql-component.html You can use a simple statement like simple("${headers.CamelSqlRowCount}") or similar after you have fetched the data. Either set the body to this statement or create a log statement. – Souciance Eqdam Rashti Jan 14 '16 at 14:34

How to add resultset to csv file in camel

2 Answers2