JDBC Delete before Insert

[Former Member]

Hi All,

I have the scenario proxy to jdbc. I want to delete records for a specific order number, that would then be inserted again.

On the datatype we have two statements. The first for the DELETE the second for the INSERT.

Looking on the database, the DELETE is working, but the INSERT not. The records are not INSERTED back again.

If I disable the DELETE statement within the Mapping, the INSERT is working.

I have viewed various other postings around the same issue. Some have suggested to use a Stored Procedure. Others have suggested the method I am using should work.

Any ideas is most useful.

Thanks

Martin

[iaki_vila]

Hi Martin,

Perhaps the problem could be that the first Delete has not finished yet when the Insert operation begin. Moreover, the Insert operation never can't know that the first call finish correctly.

From my point of view when you have a "strong dependence" between operations, the best ways to do it are:

1. With NW BPM or ccBPM.

2. With a Stored procedure.

3. Do the control in the ABAP proxy code. The report in ECC call two times, and the first scenario is a sync one, in this way from ECC you can control the rightness of the first call.

You have another alternatives, like java mapping or async/sync bridge for example, but i prefer to do clear scenarios in PI and i would chose the two scenario approach.

Regards.

[maheswarareddykonda]

Hi Inaki,

you are right, though do we really have to go to BPM's ?

actually Update_Insert will do same functionality right? please clarify ..thanks

[manoj_khavatkopp]

Another option have 2 mapping one for deletion and another for inserting and in interface determination use the option maintain order so u delete the records first and then go for inserting.

[iaki_vila]

Hi Maheswarareddy,

you are right, though do we really have to go to BPM's ?

Not necessarily to use BPM,as i said there are a lot of possibilities.

Update_Insert will do same functionality right?

This depends about the first delete. I have had scenarios in which for example i nedeed to delete all the table and insert new registers, some with the same old key but another not. I mean with the update_insert you can omit the deletion of some registers that they don't go to be modified or inserted.

Regards.

[maheswarareddykonda]

Hi Martin,

I think you are planing to do update the record if there is ...in case not there you need to insert

then why dont yo try like below

<root>

  <StatementName1>

<dbTableName action=”UPDATE” | “UPDATE_INSERT”>

  <table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2new</col2>

</access>

<key1>

<col2>val2old</col2>

<col4>val4</col4>

</key1>

<key2>

<col2>val2old2</col2>

</key2>

</dbTableName>

  </StatementName1>

Anyway as you said you are doing first delete and insert then are you using structure like below?

<Root>

  <StatementName2>

<dbTableName action=”INSERT”>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2</col2>

</access>

<access>

<col1>val11</col1>

</access>

</dbTableName>

  </StatementName2>

  <StatementName3>

<dbTableName action=”DELETE”>

<key1>

<col2>val2old</col2>

<col4>val4</col4>

</key1>

<key2>

<col2>val2old2</col2>

</key2>

</dbTableName>

  </StatementName3>

</root>

[Former Member]

Hi,

Your second example is what I am doing, except the DELETE is first, then the INSERT.

Thanks

Martin

[maheswarareddykonda]

Hi Matin,

there is an chance to time overlapping while do delete and insert so i would suggest you to go with my first option UPDATE” | “UPDATE_INSERT

because that would do same what you expecting.