2 Replies Latest reply on Jul 5, 2012 7:21 AM by Arik

    Problem in Insert or Update in DB adapter

      Hi All,

      I wanted to insert or update the records into multiple tables in single instance.

      Problem is there is a primary key across all 3 tables but that will generate in auto sequence for every new insertion of records. (say, 1,2,3,4, for every new insertion).

      There is one more field common across all the 3 tables but that is not a primary key which actually has the unique records everytime, I should use this field in order to avoid duplication.

      When I used Insert or Update(merge operation), then it is validating with primary key which is autogenerating in this case.

      Hence, I decided to go with Pure SQL but could not find right example to do that.

      I got this query

      if table t has a row exists that has key X:
      update t set mystuff... where mykey=X
      insert into t mystuff...

      But how it should be converted to real case.

      Please can anyone suggest how to perform this.

        • 1. Re: Problem in Insert or Update in DB adapter

          I would either create a stored procedure on the database and then call it from DBAdapter, or do the logic in BPEL and call different DBAdapters according to the case.

          Your logic looks complicated and I don't think you can manage in only one SQL command, that is the idea of DBAdapter.

          1 person found this helpful
          • 2. Re: Problem in Insert or Update in DB adapter

            Why do you want 1 transaction for 3 different tables?
            If all 3 tables has no primary/foreign key between them, so actually they can be in separates transactions. Stored procedure is possible depending on your business logic.
            If one table has an error do you want to continue with other 2 or cancel all the three?
            If it's possible, try to add field in each table so they will be connected by foreign key - that way you will be able to connect between them.

            1 person found this helpful