Integration

Get Involved. Join the Conversation.

Topic

    David Tran
    how to update database with the database adapter?
    Topic posted July 24, 2019 by David TranRed Ribbon: 250+ Points, last edited July 24, 2019, tagged Adapters, Database, Integration, Mapping, Orchestration, PaaS, Process 
    24 Views, 2 Comments
    Title:
    how to update database with the database adapter?
    Summary:
    need to update rows based on a value found in one of the fields
    Content:

    Hello Gurus,

    I'm processing data from a database using the DB adapter. I'm able to get data from the database.

    after processing that data, i want to use the primary key from that row of data to update a field value.

     

    the workflow is:

    Get data from DB
         for each row
              process data
              update a field in db to know its been processed.
         end loop
    end orchestration

     I tried the the update option in the connection configuration, but I can only map data, I'm guessing this will perform an update on the entire field vs updating the field where the primary key is found.

    I want something closer to:

    update tablename
    set field1="value"
    where field2 = key;

    such that key is a value I'm getting from iterating over the data in the orchestration.

    How can I do this using the DB adapter?

     

    Thank you in advance.

    Comment

     

    • Kunal Sonje

      Hi,

      Can you try using Merge operation?

      PFA snapshot from DB aapter.

      As I understood, it will insert/update based on Primary Key.

      Thanks,

      Kunal

    • David Tran

      Hello All,

      Just as Kunal Sonje has shown, I did select that option when configuring the database adapter in OIC. But I still was not able to map to any of the fields in the table, as in none of the fields in the table appeared in the mapping at all. I think this was due to a privilege issue.

      Anyhow, what I ended up doing was writing a pl/sql script and storing it as a procedure on the database. I declared necessary inbound variables and those were able to show up in the mapping section in OIC.

      Insert complete.