transactions in sql component

[Minh Tran <mi...@winning.com.au>]

Hi

I'm using the sql component to read records out of a table and then publish them to a JMS queue. This is what I have so far.

from("sql:select * from monitor where processed=false?dataSource=dataSource&consumer.useIterator=false")
.transacted()
.log("${body.class} ${body}")
.split(body())
.to("sql:update monitor set processed=true where id=:#ID?dataSource=dataSource")
.convertBodyTo(String.class)
.to("activemq:camel_test?transacted=true&lazyCreateTransactionManager=false")
.end();

The only way I could get transaction to work was to put it at the beginning of the route as shown above. 

Is there any possibility of moving the transaction so it starts inside the split? I'd like the first select statement to not participate in the same transaction as the update statement.

Re: transactions in sql component

[Minh Tran <mi...@winning.com.au>]

I found a workaround to this issue. I refactored the contents of the split into a separate route and added transaction to that. 

something like 

from("sql:select * from monitor where processed=false?dataSource=dataSource&consumer.useIterator=false")
.log("${body.class} ${body}")
.split(body())
.to("direct:processRow")
.end();

from("direct:processRow")
.transacted()
.to("sql:update monitor set processed=true where id=:#ID?dataSource=dataSource")
.convertBodyTo(String.class)
.to("activemq:camel_test?transacted=true&lazyCreateTransactionManager=false");

On 07/04/2014, at 12:15 PM, Minh Tran <mi...@winning.com.au> wrote:

> Hi
> 
> I'm using the sql component to read records out of a table and then publish them to a JMS queue. This is what I have so far.
> 
> from("sql:select * from monitor where processed=false?dataSource=dataSource&consumer.useIterator=false")
> .transacted()
> .log("${body.class} ${body}")
> .split(body())
> .to("sql:update monitor set processed=true where id=:#ID?dataSource=dataSource")
> .convertBodyTo(String.class)
> .to("activemq:camel_test?transacted=true&lazyCreateTransactionManager=false")
> .end();
> 
> The only way I could get transaction to work was to put it at the beginning of the route as shown above. 
> 
> Is there any possibility of moving the transaction so it starts inside the split? I'd like the first select statement to not participate in the same transaction as the update statement.