5 Replies Latest reply: Sep 24, 2012 6:46 AM by Arik RSS

    Reading 5K records from DB

      Hi All,

      We have a DB table where once in a month we will be polling and getting 5K + records,
      and then inserting these records to some other DB table.

      SQL Server ---> Oracle

      We have a DB adapter polling for this, now once we read this table we are setting the
      BPEL_FLAG column value from N(new) to P(processed)

      If inside the BPEL process some error happens and we stop in between, how can I rollback
      the BPEL_FLAG column back from P to N.

      Please advice.

        • 1. Re: Reading 5K records from DB
          Hi Yatan,

          To rollback the table, you must set a transaction property on the BPEL component. You add bpel.config.transaction into a BPEL process service component section in the composite.xml file. This property configures the transaction behavior for BPEL instances with initiating calls.


          <component name="InternalWarehouseService">
          <implementation.bpel src="InternalWarehouseService.bpel"/>
          <property name="bpel.config.transaction" many="false" type="xs:string">required</property>

          By setting property to required, entire process will execute in one transaction, so in case of fault, it will rollback entire transaction.

          Check if it helps.

          • 2. Re: Reading 5K records from DB

            Define your DB connection as an XA connection and not NonXa.
            In that way you will use a global connection and be able to rollback your transaction.

            • 3. Re: Reading 5K records from DB
              As you are polling the table once in a month you can go as ppl said above to go with transaction with XA.
              Otherwise you can handle the update in exception block, but ideal way is to use transaction boundaries to roll back.

              If the polling is not much then you cannot use transaction as it will end up in infinite loop.

              Let us know how you go.

              • 4. Re: Reading 5K records from DB
                Thanks for reply,

                So If I simply set the Data source as XA it should be fine?
                If No, What are the setting I need to do so that in case of any error
                in my BPEL process the complete transaction should roll back,
                I mean the Logical delete part should be set back to N.

                I am polling on Sql Server.

                Please advice,

                • 5. Re: Reading 5K records from DB

                  Some clarification:
                  A receive activity, among others, is a checkpoint for writing the transaction into the dehydration store .
                  It means, that by default, once you process was initiated, and a process flow started by the receive activity, you can't roll it back.
                  What you can do is change the oneWayDeliveryPolicy property to be synch: <property name="bpel.config.oneWayDeliveryPolicy" many="false" type="xs:string">sync</property> on your composite.xml file under your bpel component.
                  That will invoke your process synchronously.
                  If you will change this parameter, a roll back will be performed and your BPEL_FLAG will remain 'N'.
                  Few things to take in consideration:
                  1. If later on(after the receive), you will hit checkpoint activities like receive, onMessage, Wait or Pick, then your transaction until that moment will be saved into the dehydration store.
                  (Later on rollback will not be possible for the previous transaction)
                  2. Consider your rest of the flow if you want your process to act as a synchronous one.
                  3. For that configuration you can use Xa/NonXa