This discussion is archived
2 Replies Latest reply: Jul 5, 2012 12:21 AM by Arik RSS

Problem in Insert or Update in DB adapter

905784 Newbie
Currently Being Moderated
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
else
insert into t mystuff...

But how it should be converted to real case.

Please can anyone suggest how to perform this.

Thanks
  • 1. Re: Problem in Insert or Update in DB adapter
    vladodias Guru
    Currently Being Moderated
    Hi,

    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.

    Cheers,
    Vlad
  • 2. Re: Problem in Insert or Update in DB adapter
    Arik Expert
    Currently Being Moderated
    Hi,

    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.

    Arik

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points