OSB SOA DB Polling - how to update multiple column during logical delete

DB Polling

1. Create SOA Application
2. Create SOA project with Empty Composite
3. Create Database Adapter by clicking Exposed Services :

4. Name the Service Name :

5. Mention the Connection name along with JNDI :

6. Now select poll for New or Changed Records in a Table :

7. Select the table name from Schema :

8. By default all the columns will be selected to poll. But if we want, we can remove few columns which will not be polled and won't be exposed to the Business Service.

9. Now the trickiest part. Even if we are doing logical Delete we need to select Delete the Rows as marked below.

10. In this page can see polling SQL & After Read SQL in the right. Once Database Adapter created, we will update those SQL from backend.

11. We can add more parameters in select query if required. 

12. Now complete the Adapter configurations :

13. As we use Delete Rows while configuring the DB adapter so we need to update both Select and Update query from backend. Otherwise when this adapter used in SOA/OSB it will poll all the data and delete from table. So first we update the select query. Suppose we want to pick in condition of statusCode=0. For that we need to mention query-key-expression as statusCode and constant-expression value as 0.

            <queries>

               <query name="SendNotificationSelect"

                      xsi:type="read-all-query">

                  <criteria operator="equal" xsi:type="relation-expression">

                     <left name="statusCode" xsi:type="query-key-expression">

                        <base xsi:type="base-expression"/>

                     </left>

                     <right xsi:type="constant-expression">

                        <value xsi:type="xsd:decimal">0</value>

                     </right>

                  </criteria>

                  <reference-class>SendNotification.TbPaymentCrmDetails</reference-class>

                  <refresh>true</refresh>

                  <remote-refresh>true</remote-refresh>

                  <lock-mode>lock-no-wait</lock-mode>

                  <container xsi:type="list-container-policy">

                     <collection-type>java.util.Vector</collection-type>

                  </container>

               </query>

            </queries>

14. Now you need to update query part inside querying tag. Suppose you want to update statusCode as 1 & message as 'In Progress' then update the delete-query part like below.

            <delete-query>

               <call xsi:type="sql-call">

                  <sql>UPDATE TB_XXXXXXX_XXX_DETAILS SET STATUS_CODE =

                       1,STATUS_MSG = 'IN PROGRESS' WHERE (TRANSACTION_ID=

                       #TRANSACTION_ID)</sql>

               </call>

            </delete-query>

Over all the updated part should be like below.e there Which will pick records when status code is 0 & will update as status code as 1 & status_msg as 'In Progress'