Selecting Multiple Rows Using a JDBC Inbound Endpoint
The JDBC transport allows you to poll for messages in a table. However, by default the JDBC inbound endpoint splits the record set into individual MuleMessages, and the outbound endpoint only receives a single row at a time. If you want to select multiple rows at a time and process the rows in a single transaction, you can take the following steps.
First, set the transactionPerMessage property on the <jdbc:connector> element to false. For example:
<jdbc:<a href="https://www.mulesoft.com/exchange#!/?types=connector" target="_blank" rel="" title="Cloud Connectors" >connector</a> name="jdbcConnector" dataSource-ref="jdbcDataSource1"
pollingFrequency="1000" transactionPerMessage="false">
<jdbc:query key="read" value="SELECT id, type, data FROM test WHERE type=1"/>
<jdbc:query key="read.ack" value="UPDATE test SET type=2
WHERE id=#[map-payload:id]"/>
</jdbc:<a href="https://www.mulesoft.com/exchange#!/?types=connector" target="_blank" rel="" title="Cloud Connectors" >connector</a>>
Next, on the inbound endpoint, configure a <jdbc:transaction> element and set the receiveMessageInTransaction property to true. For example:
<jdbc:inbound-endpoint queryKey="read" <a href="https://www.mulesoft.com/exchange#!/?types=connector" target="_blank" rel="" title="Cloud Connectors" >connector-</a>ref="jdbcConnector">
<jdbc:transaction action="ALWAYS_BEGIN"/>
<property key="receiveMessageInTransaction" value="true"/>
</jdbc:inbound-endpoint>
The outbound endpoint will now receive a list of rows from the table. You can save this list to a file, to another table, etc.
Anonymous
February 18th 2010
This post really helped me. Thanks!
jin
February 18th 2010
This post really helped me! thanks
Anonymous Says
May 30th 2010
Puneet, thanks a lot for the solution. It helped me a lot.
Barry
March 30th 2011
Thats cool, but I got an IllegalTransactionStateException error when applying this approach:
Root Exception stack trace:
org.mule.transaction.IllegalTransactionStateException: A transaction is available for this session, but transaction action is “Never”
at org.mule.transaction.TransactionTemplate.execute(TransactionTemplate.java:54)
at org.mule.routing.outbound.AbstractOutboundRouter.dispatch(AbstractOutboundRouter.java:101)
at org.mule.routing.outbound.FilteringOutboundRouter.route(FilteringOutboundRouter.java:83)
at org.mule.routing.outbound.OutboundPassThroughRouter.route(OutboundPassThroughRouter.java:69)
at org.mule.routing.outbound.DefaultOutboundRouterCollection$1.doInTransaction(DefaultOutboundRouterCollection.java:88)
at org.mule.transaction.TransactionTemplate.execute(TransactionTemplate.java:45)
at org.mule.routing.outbound.DefaultOutboundRouterCollection.route(DefaultOutboundRouterCollection.java:93)
at org.mule.service.AbstractService.sendToOutboundRouter(AbstractService.java:892)
at org.mule.model.seda.SedaService.doSend(SedaService.java:258)
at org.mule.service.AbstractService.sendEvent(AbstractService.java:500)
at org.mule.DefaultMuleSession.sendEvent(DefaultMuleSession.java:354)
at org.mule.routing.inbound.DefaultInboundRouterCollection.send(DefaultInboundRouterCollection.java:228)
at org.mule.routing.inbound.DefaultInboundRouterCollection.route(DefaultInboundRouterCollection.java:188)
at org.mule.transport.AbstractMessageReceiver$DefaultInternalMessageListener.onMessage(AbstractMessageReceiver.java:364)
at org.mule.transport.AbstractMessageReceiver.routeMessage(AbstractMessageReceiver.java:252)
at org.mule.transport.AbstractMessageReceiver.routeMessage(AbstractMessageReceiver.java:181)
at org.mule.transport.jdbc.JdbcMessageReceiver.processMessage(JdbcMessageReceiver.java:157)
at org.mule.transport.TransactedPollingMessageReceiver$1.doInTransaction(TransactedPollingMessageReceiver.java:125)
at org.mule.transaction.TransactionTemplate.execute(TransactionTemplate.java:99)
at org.mule.transport.TransactedPollingMessageReceiver.poll(TransactedPollingMessageReceiver.java:131)
at org.mule.transport.PollingReceiverWorker.run(PollingReceiverWorker.java:47)
at org.mule.work.WorkerContext.run(WorkerContext.java:310)
at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1061)
at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:575)
at java.lang.Thread.run(Thread.java:619)
my config is:
Shall the outbound also be in transaction?
Thanks!
maya
May 13th 2011
Hi
How can i create outbound endpoint for writing list of rows from the table into file .. could you give some explanation.
Thanks and regards
Maya
matty
May 4th 2012
I get this error:
org.mule.transaction.IllegalTransactionStateException: Can only bind “javax.sql.DataSource/java.sql.Connection” type resources
matty
May 4th 2012
Ah – the solution is to add a transaction manager as per {url}http://www.mulesoft.org/schema/mule/jbossts http://www.mulesoft.org/schema/mule/jbossts/3.1/mule-jbossts.xsd{url}
and use {code}
{code}
as per
{url}
http://mule.1045714.n5.nabble.com/Issue-with-transactions-td2667157.html{url}