In camel how i can update a result set coloumn to a value

Question

My select query is

sql.selectOrder=select STID,CLLTR from GSI_DEVL.POLLUX_DATA WHERE PROCESSED='FALSE'

My Route is

<route id="markRowsAsProcessed-Route"  >
         <!--    <from uri="timer://markRowsAsProcessed?delay=5000"/>-->
        <from uri="sqlComponent:{{sql.selectOrder}}?consumer.useIterator=false" />   
        <doTry>
              <to uri="bean:rowProcessController"/>  
            <to uri="sqlComponent:{{sql.markRows}}?batch=true"/>
            <doCatch>
                <exception>java.sql.SQLException</exception>    
                <exception>java.lang.IllegalStateException</exception>
                <exception>java.sql.SQLException</exception>
                <exception>java.lang.ClassCastException</exception> 
            </doCatch>
        </doTry>    
    </route>

My bean is

public class RowProcessController {
    List<Map<String, Object>> stationUnMarkedList = new ArrayList<Map<String, Object>>();
    List<Map<String, Object>> stationMarkedList = new ArrayList<Map<String, Object>>();
    Map<String,Object> stationMap = null;

    @SuppressWarnings("unchecked")
    @Handler
    public List<Map<String, Object>> markRowAsProcessed(Exchange exchange)
    {
        stationUnMarkedList = (List<Map<String, Object>>)exchange.getIn().getBody();
        for(Map<String,Object> data: stationMarkedList) {
            System.out.println(data.get("STID"));
            stationMap=new HashMap<String,Object>();
            stationMap.put("stationId", ((String)data.get("STID")));
            stationMap.put("callLetter", ((String)data.get("CLLTR")));
            stationMarkedList.add(stationMap);
        }
        return stationMarkedList;
    }
}

I want to update the result set processed column to done or some value.

I tried

sql.markRows=UPDATE POLLUX_DATA SET PROCESSED='DONE' where stid=:stationId

But this does not update any values in the database. Why not?

Are you sure that the body has the correct type? Named variables used in SQL statements must be definied as header or in the body if its a java.util.Map (see [link](http://camel.apache.org/sql-component.html)). You could also try to set header **stationId** on the exchange. — Ewout, Jan 21 '16 at 14:26
if remove named queries,the same issue persist.if i remove the select statement and try a direct update statement then its working.I think some problems with these combinatins.In camel is there is any restriction to use select and update together.I mean can i process the result set throug a bean and update the database ? — Bibin Mathew, Jan 21 '16 at 14:53

score 0 · Answer 1 · answered Jan 21 '16 at 15:14

0

Can you try and change the query's named parameter notation to :# instead of just :

sql.markRows=UPDATE POLLUX_DATA SET PROCESSED='DONE' where stid=:#stationId

answered Jan 21 '16 at 15:14

Sundar

564
3
7

Sundar,Stiil its not updating.i was using :#stationId when i was posting this.Wrongly i pasted here as :stationId. I need to mark the row as processed – Bibin Mathew Jan 22 '16 at 06:34
On looking in to your code i think the issue is a typo , you are trying to iterate through a empty list in your bean , you are reading the body in to a list stationUnMarkedList but iterating through stationMarkedList , so the body to the update query is a empty list. I would suggest to add logs and also when handling exceptions try to do something about it , do not swallow exceptions. – Sundar Jan 22 '16 at 17:05

In camel how i can update a result set coloumn to a value

1 Answers1