Integrationpublic

Integration

Get Involved. Join the Conversation.

This is a public Forum  public

Topic

    Gabriel G Huo
    What's the difference of "For Each" action and...
    Topic posted October 28, 2019 by Gabriel G HuoBronze Medal: 1,250+ Points, tagged Adapters, Connection, Database, Integration, Mapping, Orchestration, PaaS 
    39 Views, 6 Comments
    Title:
    What's the difference of "For Each" action and adding for-each during mapping
    Content:

    Hi experts,

    In integration we are reading one file which has multiple lines, after that we want to call a DB procedure for each line(line value as input parameter or procedure).

    One way is in integration we use For Each action and call the procedure within the loop

    The other way looks to be during mapping, add for-each(mapping readfile lines) at the top of procedure call request

    Will they behave the same? Is that the 1st one will call DB agent multiple times but the 2nd one will only call DB agent once?

    Suppose there are 20000 records from source file, which one would be better?

    The procedure here we want to use is to update one DB table based on the input records.

    Thanks in advance!

    Comment

     

    • Jorge Herreria

      The difference is scope (execution scope):

      • The orchestration for-each action has a body/scope which can have multiple activities (assigns, notify, invoke, switch, etc). 
      • In the Map (XSLT), the scope of  is limited to the <xsl:for-each> template (body of the <xsl:for-each>);  In XSLT there is no straight way to call a DB procedure. Therefor no straight way to call the DB procedure within the template (body) of the <xsl:for-each>; no straight way to call the DB procedure for  each record within the Map (XSLT). The Map (XSLT) purpose is to create the payload to be sent to the DB procedure in the Invoke action.

       

      With respect which one is best depends on the DB procedure. If the DB procedure accepts multiple records in the payload, then use Map to create the payload having multiple records. If the DB procedure only accepts one record at the time, then use the For-Each Action and within the body of the For-Each Action invoke the DB procedure. The Invoke will require you to create a map (build the payload). In the Map select the record from the For-Action definition.

      • Gabriel G Huo

        Hi Jorge, 

        Thanks for the elaboration, i've prepared a screenshot to explain the for-each within mapping. i am not saying call procedure in mapping, just want to know if there is any advantage to use for-each in mapping instead of wrapping DB procedure call within Integration For-Each action 

        • Gabriel G Huo
          • Gabriel G Huo

            Not sure how to add multiple pics in one post...

            below is the Integration For-Each action i am saying. i believe like this the DB adapter procedure call will be trigger multiple times for each source data record.

            But if we do it like the first picture, will it be any difference? Or it won't work at all unless the procedure accepts table record input parameter? Because i am using sql server DB, looks OIC does not support table records to be passed in as parameter for it.

            Thanks

            • Jorge Herreria

              This is they way you need to go: ForEach created in the Orchestration level.

              Now, to do the map (XSLT), you want to map the values from the variable that you defined in the forEachGLJournallm under the Current Element Name ... You'll see it in the Map editor starting with a $ then the name you provided for the "Current Element Name" field. Map from that variable/parameter and that's it.

              The for-loop will pickup a record at the time from the input payload and put it in the $ var of the for-each. The map will take the values from the $var for each and send them to the DB.

               

              Screen Shot 2019-10-30 at 2.53.14 PM.png (141KB)
        • Jorge Herreria

          Ok, now I'm clear what you are asking.

          Definitely you do not want to go this route (having a <xsl:for-each> in the Map (XSLT). Reason being, the Target is not unbounded (repeated). The target only accepts 1 "record" a the time.

          BTW - just for fun, click the TEST button, generate a payload and run your mapping. You'll see the odd results on the map... Do not worry, the DB will not be called by this TEST, it will only run the XSLT in memory and show you the result.... again, just for fun.

     

    community forum