Delete entries in DB using receiver JDBC adapter document format

[Former Member]

Hi Experts,

We are having a JDBC to PROXY synchronous scenario where data is being selected from an external DB and is being integrated in ECC system.

After successful integration of data in ECC DB, the same data has to be deleted in external DB.

We are using request-response bean to achieve the above requirement. And for outbound processing, document format for receiver JDBC adapter is being used.

Can you please suggest how can we delete the same records from external database which are being selected in during inbound processing?

Many thanks in advance.

Regards,

Srinivas

[Former Member]

Hi,

As siad above you can achieve by returning the primary of successful insert records from the ECC..

Regarding the Request response bean i want to emphase that there is an option to use the request response bean at the receiver channel rather than at the sender channel level..

The advantage that you will get using the above option is reprocessing ..

if there are any failures occur between while calling the proxy you dont need to retrieve the data again from jdbc you can resend the failed message from PI even for the case while updating the database after receving the proxy response..

HTH

Rajesh

[baskar_gopalakrishnan2]

This is simple.  You can use primary key in the where clause to delete the table rows that are inserted in ECC.   But I want you to make sure after successful insert only you delete the records in external db.

You can use the below structure for delete the records

<StatementName>

<SomeName action= “SQL_DML”>

<access>SQL-String with optional placeholder(s)</access>

<key>

  <placeholder1>value1</placeholder1>

  <placeholder2>value2<placeholder2>   

</key>

</SomeName >

  </StatementName6>

In access tag write your delete statement.

[Former Member]

Hi Baskar,

Thanks for the reply. I was considering this approach.

Kindly clarify my doubt regarding the SQL statement. I am using all the fields from DB as key fields.

i.e. DELETE statement is same as the SELECT statement as in the inbound flow.

Is it a good practice to use any single field and it will delete the corresponding rows in the external DB with this approach?

Regards,

Srinivas

[ambrish_mishra]

Hi Srinivas,

>>>>>I am using all the fields from DB as key fields.

Normally this is not the case unless there is a specific requirement.

>>>>>Is it a good practice to use any single field and it will delete the corresponding rows in the external DB with this approach?

Using a single field for deletion or select is not the criteria. It depends on table design and what are the key field(s).

However, deleting the records from PI in an external DB is not a good practice. It is better to mark the record as processed which means processed in ECC. This keeps an archive of data in DB for reconciliation and then records can be deleted by the DB team later by a job. for example, job which deletes record marked as processed older than 15 days.

Hope it helps!

Ambrish

[Former Member]

Hi baskar,

I have an urgent requirement,

RFC to Soap Scenerio....

In request message mapping,here we are concatenating some 6 fields and sending as a input to a java code and in that java class mapping that field is encryted using some algorithm and finally sending that field to the webservice.

but the same field is not sent by the web service as response..

For our requirement we need that field in the response mapping which is to be sent to BAPI in R/3 system.

Can u please tell me clearly how to achieve this other than using BPM..

And is there any possibility to get it without using lookup.

venkatdasari333 @gmail.com

[ambrish_mishra]

Hi Srinivas,

Delete option is available as document format for receiver JDBC adapter.

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

You can achieve the requirement like below:

Once you have selected the records and updated in ECC, you need to code in proxy to return the key field value of the records which have been successfully updated in ECC and delete the record using the document format specified in the link above.

Basically trigger a statement:

DELETE FROM dbTableName  WHERE <columnName>=’value’

value(s) should come from ECC.

If you need any further inputs, do revert back.

Ambrish

PS: I have done a similar scenario in SAP HR integration where I did JDBC to ECC (async proxy) and in the same proxy called another client proxy asynchrnously with key field as personnel number (PERNR) to update the record in the database as processed. Your requirement is similar.