Saturday 14 May 2011

Customizing delete-polling strategy

I had one requirement, need to poll a table based on a particular field value and at the same time need to update a field on another table.In SOA 10g we can edit the toplink and add our custom query on delete or update tab. But in SOA 11g that toplink is replaced by or-mappings and we need to edit here.

So here are the steps you need to do and in my case I'm updating a field value of same table.

1.I created a table test in scott schema using sqldeveloper,its pretty simple..

image

2.A DB adapter and a polling BPEL process created based on that table.Select delete polling strategy.

image

In the adapter here is the screenshot of last step,

image

So in the polling query I’m checking if column3 is NULL or not.You can have your own criteria.

3.Then open getData-or-Mapping.xml file and add below lines in between  </queries> and </querying> to override the adapter generated delete query.

<delete-query>
               <call xsi:type="sql-call">
                  <sql>update test set column3='READ' where column1= #COLUMN1 </sql>
               </call>
</delete-query>

    In this sql tag you can call your custom pl/sql function or sql query to perform CRUD on any objects and whatever the parameter you are passing ,make sure those were selected during adapter creation wizard.  So here is my or-mappings.xml file,

    <?xml version="1.0" encoding="UTF-8"?>
    <object-persistence xmlns="http://www.eclipse.org/eclipselink/xsds/persistence" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:eclipselink="http://www.eclipse.org/eclipselink/xsds/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="Eclipse Persistence Services - 2.1.3.v20110304-r9073">
       <name>getData-OR</name>
       <class-mapping-descriptors>
          <class-mapping-descriptor xsi:type="object-relational-class-mapping-descriptor">
             <class>getData.Test</class>
             <alias>TEST</alias>
             <primary-key>
                <field table="TEST" name="COLUMN1" xsi:type="column"/>
             </primary-key>
             <events/>
             <querying>
                <queries>
                   <query name="getDataSelect" xsi:type="read-all-query">
                      <criteria function="isNull" xsi:type="function-expression">
                         <arguments>
                            <argument name="column3" xsi:type="query-key-expression">
                               <base xsi:type="base-expression"/>
                            </argument>
                         </arguments>
                      </criteria>
                      <reference-class>getData.Test</reference-class>
                      <lock-mode>none</lock-mode>
                      <container xsi:type="list-container-policy">
                         <collection-type>java.util.Vector</collection-type>
                      </container>
                   </query>
                </queries>
                <delete-query>
                   <call xsi:type="sql-call">
                      <sql>update test set column3='READ' where column1= #COLUMN1 </sql>
                   </call>
                </delete-query>
             </querying>
             <attribute-mappings>
                <attribute-mapping xsi:type="direct-mapping">
                   <attribute-name>column1</attribute-name>
                   <field table="TEST" name="COLUMN1" xsi:type="column"/>
                   <attribute-classification>java.lang.String</attribute-classification>
                </attribute-mapping>
                <attribute-mapping xsi:type="direct-mapping">
                   <attribute-name>column2</attribute-name>
                   <field table="TEST" name="COLUMN2" xsi:type="column"/>
                   <attribute-classification>java.lang.String</attribute-classification>
                </attribute-mapping>
                <attribute-mapping xsi:type="direct-mapping">
                   <attribute-name>column3</attribute-name>
                   <field table="TEST" name="COLUMN3" xsi:type="column"/>
                   <attribute-classification>java.lang.String</attribute-classification>
                </attribute-mapping>
             </attribute-mappings>
             <descriptor-type>independent</descriptor-type>
             <caching>
                <cache-type>weak-reference</cache-type>
                <cache-size>-1</cache-size>
                <always-refresh>true</always-refresh>
             </caching>
             <remote-caching>
                <cache-type>weak-reference</cache-type>
                <cache-size>-1</cache-size>
             </remote-caching>
             <instantiation/>
             <copying xsi:type="instantiation-copy-policy"/>
             <tables>
                <table name="TEST"/>
             </tables>
             <structure>structureName</structure>
          </class-mapping-descriptor>
       </class-mapping-descriptors>
       <login xsi:type="database-login">
          <platform-class>org.eclipse.persistence.platform.database.oracle.Oracle9Platform</platform-class>
          <user-name></user-name>
          <connection-url></connection-url>
       </login>
    </object-persistence>
    4. Deploy the process and you are done….check your test table and instances at em.

    4 comments:

    Anonymous said...

    Vamsi said...

    Hi Shri ,

    I would like to poll the database for no of records available ( Based on the no of records i can proceed further ) Could you please update the thread with how to modify the polling SQL .

    Cheers
    Lakshmi

    shrikworld said...

    I think there is no JCA property for the same. However you can achieve it by creating a view with SQL query tuned based on no of DB rows required.
    So basically you need to poll the view and that do your job.

    Nag said...

    Hi all friends i am fresher on Soa here i have some droughts clarify to me

    1)Explain error handling in BPEL and what is a error handling framework?
    2)What all errors can't be handled by a BPEL process?
    3)What is a nonBlockingAll property?What is the use of it?