Selecting Multiple Rows Using a JDBC Inbound Endpoint

October 26 2009

7 comments 0
motif

The transport allows you to poll for messages in a table. However, by default the 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.


We'd love to hear your opinion on this post


7 Responses to “Selecting Multiple Rows Using a JDBC Inbound Endpoint”

  1. This post really helped me. Thanks!

  2. This post really helped me! thanks

  3. Puneet, thanks a lot for the solution. It helped me a lot.

  4. 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!

  5. 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

  6. I get this error:

    org.mule.transaction.IllegalTransactionStateException: Can only bind “javax.sql.DataSource/java.sql.Connection” type resources

  7. 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}