12 Replies Latest reply: Nov 29, 2012 8:09 AM by 902286 RSS

    Three database or multiple Database adapters in One Composite

    902286
      Hi All,

      I have below requirement:

      1) I have two applications A and B.I am integrating them with SOA.I have to recieve data from A and update that data into application B for different Device IDs.
      I am using one database adapter(insert operation) to recieve the data from A and insert that data into a staging table.(I have to hold on to the data into a table for 24 hrs and update the whole data into B at the ene of the day.)

      BPEL-1 : Application A --- > Staging table(insert DB Adapter) (for every half an hour interval in that day)

      That data(two request schema example) would be like below:

      1) <devID> 345 </devID>
      <Con>45</devId>

      2) <devID> 343 </devID>
      <Con>45</devId>



      I will get the above schema for evry interval for dfifferent devIds. I am inserting the data into only one staging table even though the data would be for different device IDs.

      After inserting the data for 24 hrs in the staging table,I have to use Application B WSDL url to update data into B.



      I am using two Database adapters(one for each deviceId) for selecting the data for two device IDS and inserting into Application B.

      BPEL-2:DB adapter(Select operation-- Staging table which has the data for this deviceID 345) ----------> application B WSDL

      BPEL-3 : DB adapter(Select operation-- Staging table which has the data for this deviceIDs 343) ----------> application B WSDL


      As of now after entering the data for couple of intervals for two deviceIDs,I am getting instances for BPEL-1 and after BPEL-2 which is for device ID 345.
      But I am not getting instances for BPEL-3 which is for device ID 343.


      Can I use three DB adapters(one for Insert,two for selecting the data from same table?

      Why I am not able to update the data related to device Id 343 into B.?


      Please guide me on this.
        • 1. Re: Three database or multiple Database adapters in One Composite
          Neeraj Sehgal
          Hi,

          I don't know if I understood your requirement well or not but you could do the following.

          - Create a BPEL-A process that just does the insertion into the Staging Table.
          - Create another BPEL-B that selects BOTH the device IDs in single SELECT statement and then call the Application B WSDL (I didn't understand here why you were calling the application B WSDL differently for different Device IDs)

          Please explain in detail what you're looking for and we can help.

          Regards,
          Neeraj Sehgal
          • 2. Re: Three database or multiple Database adapters in One Composite
            Arik
            Hi,

            There are some missing information in your scenario.
            Are you using commit in any part of your process? How does Bpel 2/3 are being called?
            Did you check the UnitOfWork checkbox in your DB adapter? If you are using the same DB transaction for Insert/Update/Select...it is recommend to use this option.

            Arik
            • 3. Re: Three database or multiple Database adapters in One Composite
              902286
              Hi Neeraj,

              Thank you very much for your reply

              I already did the BPEL-A which inserts the data for every half an hour into staging table.

              I can't use one BPEL-B that select both device IDs in one select.
              The reason why I am calling application B WSDL differently for different deviceIds below:

              Application B wsdl IS only for one device ID at a time.That too it is for 24 hrs device consumption for couple of half an hour intervals.

              DeviceID -1 ------> application B WSDL ---> application B DeviceID-1 page

              <deviceID> deviceID1 <deviceID>
              <Consumption1>24 </Consumption1>
              <Consumption2>28 </Consumption2>
              <Consumption3>30 </Consumption3>

              ---------------------

              ---------------------------------------

              <Consumption34>400</Consumption34>

              application A sends Consumption for every half an hour(Consumption1,Consunption2 ----------------------- till the end of the day for every half an hour for each device.)

              DeviceID-2 --------> application B WSDL --> application B DeviceID-2 page

              <deviceID> deviceID2 <deviceID>
              <Consumption1>20 </Consumption1>
              <Consumption2>27 </Consumption2>
              <Consumption3>32 </Consumption3>


              like that.


              The scehema for each device ID should go in one request schema to application B WSDL at the end of the day(after getting all consumptions).


              For that, I have created one BPEL-1 and DB adapter-1("insert" operation) for inserting into staging table irrespective of device IDs.

              Buit I have to segregate the data from staging table for each device ID so that I can send the seperate requests for each device.

              I have created BPEL-2 and DB adapter-2(select operation) for device ID 1 to update this device data in B

              I have created BPEL-3 and DB adapter-2(select operation) for device ID 2 to update this device data in B

              If I have multiple devices ,I want to create that many DB Adapters and BPEL processes.

              As of now I am getting the instance of BPEL-2,but I am not for BPEL-3.

              I am using Same datasource and DB adapter.


              I hope you understand my req clearly.

              Please guide me on this to resolve this issue.

              Edited by: 899283 on Nov 25, 2012 6:02 AM
              • 4. Re: Three database or multiple Database adapters in One Composite
                902286
                Hi Arik,

                I posted my requirement elaborately in reply to neeraj.

                Please look into that and let me know any inputs you need.

                Thank you very much for the reply
                • 5. Re: Three database or multiple Database adapters in One Composite
                  Arik
                  Hi,

                  Still if you can answer my below questions please.

                  Arik
                  • 6. Re: Three database or multiple Database adapters in One Composite
                    902286
                    Hi Arik,

                    I am using seperate DB adapters (for each operation of insert(for any device ID),Select(for device ID1),Select(for Device ID 2) etc.

                    I have created seperate BPEL processes for above each operation as every device should be in seperate request.

                    Where do u want me to commit?

                    As of now, after u said I have set the Unit of wrok value as true in all _db.jca files.

                    Please find the below jca files for each operation:

                    Insert operation:

                    <adapter-config name="InsertWinamrData" adapter="Database Adapter" wsdlLocation="InsertWinamrData.wsdl" xmlns="http://platform.integration.oracle/blocks/adapter/fw/metadata">

                    <connection-factory location="eis/DB/SOADB" UIConnectionName="SOADB" adapterRef=""/>
                    <endpoint-interaction portType="InsertWinamrData_ptt" operation="insert">
                    <interaction-spec className="oracle.tip.adapter.db.DBWriteInteractionSpec">
                    <property name="DescriptorName" value="InsertWinamrData.Imdreading"/>
                    <property name="DmlType" value="insert"/>
                    <property name="MappingsMetaDataURL" value="InsertWinamrData-or-mappings.xml"/>
                    <property name="DetectOmissions" value="true"/>
                    <property name="GetActiveUnitOfWork" value="true"/>
                    </interaction-spec>
                    </endpoint-interaction>

                    </adapter-config>


                    Select ope on one device ID:

                    <adapter-config name="GetIMDReadingIntoMDM" adapter="Database Adapter" wsdlLocation="GetIMDReadingIntoMDM.wsdl" xmlns="http://platform.integration.oracle/blocks/adapter/fw/metadata">

                    <connection-factory location="eis/DB/SOADB" UIConnectionName="SOADB" adapterRef=""/>
                    <endpoint-activation portType="GetIMDReadingIntoMDM_ptt" operation="receive">
                    <activation-spec className="oracle.tip.adapter.db.DBActivationSpec">
                    <property name="DescriptorName" value="GetIMDReadingIntoMDM.Imdreading"/>
                    <property name="QueryName" value="GetIMDReadingIntoMDMSelect"/>
                    <property name="MappingsMetaDataURL" value="GetIMDReadingIntoMDM-or-mappings.xml"/>
                    <property name="PollingStrategy" value="LogicalDeletePollingStrategy"/>
                    <property name="MarkReadColumn" value="READSTATUS"/>
                    <property name="MarkReadValue" value="YES"/>
                    <property name="MarkUnreadValue" value="NO"/>
                    <property name="PollingInterval" value="240"/>
                    <property name="MaxRaiseSize" value="4"/>
                    <property name="MaxTransactionSize" value="8"/>
                    <property name="NumberOfThreads" value="1"/>
                    <property name="ReturnSingleResultSet" value="false"/>
                    <property name="EnableStreaming" value="false"/>
                    <property name="GetActiveUnitOfWork" value="false"/>

                    </activation-spec>
                    </endpoint-activation>

                    </adapter-config>

                    seldct of other device ID:

                    <adapter-config name="GetIDMReadingIntoOne" adapter="Database Adapter" wsdlLocation="GetIDMReadingIntoOne.wsdl" xmlns="http://platform.integration.oracle/blocks/adapter/fw/metadata">

                    <connection-factory location="eis/DB/SOADB" UIConnectionName="SOADB" adapterRef=""/>
                    <endpoint-activation portType="GetIDMReadingIntoOne_ptt" operation="receive">
                    <activation-spec className="oracle.tip.adapter.db.DBActivationSpec">
                    <property name="DescriptorName" value="GetIDMReadingIntoOne.Imdreading"/>
                    <property name="QueryName" value="GetIDMReadingIntoOneSelect"/>
                    <property name="MappingsMetaDataURL" value="GetIDMReadingIntoOne-or-mappings.xml"/>
                    <property name="PollingStrategy" value="LogicalDeletePollingStrategy"/>
                    <property name="MarkReadColumn" value="READSTATUS"/>
                    <property name="MarkReadValue" value="YES"/>
                    <property name="MarkUnreadValue" value="NO"/>
                    <property name="PollingInterval" value="360"/>
                    <property name="MaxRaiseSize" value="4"/>
                    <property name="MaxTransactionSize" value="8"/>
                    <property name="NumberOfThreads" value="1"/>
                    <property name="ReturnSingleResultSet" value="false"/>
                    <property name="GetActiveUnitOfWork" value="false"/>

                    </activation-spec>
                    </endpoint-activation>

                    </adapter-config>

                    Please guide me on this.

                    Edited by: 899283 on Nov 25, 2012 10:27 PM
                    • 7. Re: Three database or multiple Database adapters in One Composite
                      Arik
                      Hi,

                      The idea is to set the GetActiveUnitOfWork parameter to true in all of the jca files/DbAdapters, only of you are using a global transaction and the same DB.
                      I still didn't understand the full process of yours. Is it possible to share it or send it by mail?

                      Arik
                      • 8. Re: Three database or multiple Database adapters in One Composite
                        902286
                        Hi Arik,

                        I am again explaining my requirement:

                        Application A sends data to BPEL for multiple devices for multiple times(This is energy consumption values for a period of intervals).

                        First I am trying to insert the all the data for multiple devices into a staging table thourgh insert Db adapter.

                        I want to use select db adapter and I want to send the seperate request schema to application B WSDL for each device ID as Application B WSDL collects the whole data of that data for a single device.

                        Is there any way to segregate the data so that I can send seperate request for each device.?
                        • 9. Re: Three database or multiple Database adapters in One Composite
                          Neeraj Sehgal
                          Hi,

                          I still don't understand why you want to use different processes for calling the same BPEL-2 WSDL. Agree that there're different details flowing in for Device IDs but the Target WSDL still remains the same. If I'm right, I'd implement the above in following way. See if that fits in your requirements.

                          - Create a process A for Inserting in staging table (this you've already done so not elaborating on it)
                          - Create another process B that polls for all the Device IDs at night (24 hrs). Once you've polled all the device IDs, segregate them within the process (using XSLT group-by or many other options) and call the BPEL-2 WSDL for each Device ID group in a Loop. I don't know the nature (Synchronous/Asynchronous) of your BPEL-2 WSDL but if it is Asynchronous/Fire & Forget, you can also use Flow activity further to concurrently process them to reduce the execution time.

                          You can also consider invoking your process B through a scheduler every night instead of polling and scheduler can pass in the Device IDs as input parameter to the process B after which you can process them internally.

                          Let us know how you get along.

                          Regards,
                          Neeraj Sehgal
                          • 10. Re: Three database or multiple Database adapters in One Composite
                            902286
                            Hi Neeraj,

                            After I get the data for multiple devices,I have to invoke Application B.Bit only problem I am facing is :

                            I will get the records for multiple device Ids like below:
                            For device 1:
                            1 request : \
                            <deviceId>1<deviceId>

                            <purpose>consumption</purpose>


                            <concumption>
                            <firstInterval> 1</firstInterval>
                            </consumtion>

                            2 dn request:

                            <deviceId>1<deviceId>

                            <purpose>consumption</purpose>

                            <concumption>
                            <secondInterval> 45</SecondInterval>
                            </consumtion>

                            For device 2:


                            1 request : \
                            <deviceId>2<deviceId>

                            <purpose>consumption</purpose>

                            <concumption>
                            <firstInterval> 57</firstInterval>
                            </consumtion>

                            2 dn request:

                            <deviceId>2<deviceId>

                            <purpose>consumption</purpose>

                            <concumption>
                            <secondInterval> 55</SecondInterval>
                            </consumtion>



                            But the request schema for application B WSDL only wants deviceID once

                            <DeviceID>1<DeviceID>

                            <Consumption1> ----<Consumption1>
                            <Consumption2> ----<Consumption2> like that


                            I will get the collection of the data records in which every record has deviceID for multiple intervals.

                            How do I map common parameters for multiple deviceIDs in application B wsdl in order to segregate the schema for evry device request .

                            I hope you got the my problem.
                            • 11. Re: Three database or multiple Database adapters in One Composite
                              Neeraj Sehgal
                              Hi,

                              Use While loop. Firstly call the Application B WSDL for deviceID - 1 and then for 2 and so on.

                              Regards,
                              Neeraj Sehgal
                              • 12. Re: Three database or multiple Database adapters in One Composite
                                902286
                                Hi Neeraj,

                                I will eaxctly provide the kind of schema I have after I got the data into the input of BPEL-2 process:

                                <select_arrid_collection xmlns="http://xmlns.oracle.com/pcbpel/adapter/db/Select_Arrid">

                                <select_arrid>
                                <arr_id>arr1</arr_id>
                                <consumption1> 23</Consumption1>
                                <Interval> 1</Interval>
                                </select_arrid>

                                <select_arrid>
                                <arr_id>arr2</arr_id>
                                <consumption1> 27</Consumption1>
                                <Interval>1 </Interval>
                                </select_arrid>

                                <select_arrid>
                                <arr_id>arr1</arr_id>
                                <consumption2> 25</Consumption2>
                                <Interval> 2</Interval>

                                </select_arrid>
                                <select_arrid>
                                <arr_id>arr2</arr_id>
                                <consumption2> 23</Consumption2>
                                <Interval> 2</Interval>
                                </select_arrid>
                                </select_arrid_collection>


                                I need the below output:

                                <root xmlns="http://xmlns.oracle.com/pcbpel/adapter/db/Select_Arrid">
                                <select_arrid_collection>
                                <select_arrid>
                                <arr_id>arr1</arr_id>
                                <consumption1> 23</Consumption1>
                                <Interval> 1</Interval>
                                </select_arrid>
                                <select_arrid>
                                <arr_id>arr1</arr_id>
                                <consumption2> 25</Consumption2>
                                <Interval> 2</Interval>

                                </select_arrid>
                                </select_arrid_collection>

                                <select_arrid_collection>
                                <select_arrid>
                                <arr_id>arr2</arr_id>
                                <consumption1> 27</Consumption1>
                                <Interval>1 </Interval>
                                </select_arrid>
                                <select_arrid>
                                <arr_id>arr2</arr_id>
                                <consumption2> 23</Consumption2>
                                <Interval> 2</Interval>
                                </select_arrid>
                                </select_arrid_collection>
                                </root>


                                Once I searched the blogs,I came to know that I f I use below XSL content,I will get the collections for each device(arr_id):

                                Note: I also have other elements for each arr_id content

                                <xsl:stylesheet version="2.0" xmlns:ns1="http://xmlns.oracle.com/pcbpel/adapter/db/Select_Arrid" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
                                <xsl:template match="/">
                                <ns1:root>
                                <xsl:for-each-group select="*/ns1:Select_Arrid" group-by="ns1:Arr_id">
                                <xsl:sort select="current-grouping-key()">
                                <ns1:select_arrid_collection>
                                <xsl:copy-of select="current-group()">
                                <xsl:apply-templates/>
                                </xsl:copy-of>
                                </ns1:select_arrid_collection>
                                </xsl:sort>
                                </xsl:for-each-group>
                                </ns1:root>
                                </xsl:template>
                                </xsl:stylesheet>



                                But What I want to know Where I have include this xsl file.

                                I have below flow for BPEL-2 process in order of below steps:

                                1) DBAdapter (Select all records including multiple records for each device ID)
                                2) Receive activity of BPEL process which takes the all the records as the collection -
                                3) As of now I am using transform activity after receive activity.This has Source as Collection from receive and target I am using application B WSDL input variable.
                                to map the variables from receive input and wsdl input.

                                But before that I have to modify the collection object from receive activity with above xsl content .Where can I put that file and how to map the new content to application B wsdl input.

                                How do I loop through the new collection in while loop?

                                And I have to seperately send the request schema for each collection(I mean for the collection of a deviceID)


                                Please guide me on this as I am in last step to achieve the required result


                                Thank you very much for your replies so far

                                Edited by: 899283 on Nov 29, 2012 5:42 AM

                                Edited by: 899283 on Nov 29, 2012 6:03 AM