Execute two DB statements consecutively

[Former Member]

Hi All,

I am trying to create a scenario where there will be two operations on a DB.

First is the insert statement next is a stored procedure call.

The stored procedure call is dependent on the insert.

I have to call the stored procedure after the insert is successful.

What is the best approach for this ?

Thanks,

Arijit

[rajasekhar_reddy14]

What if insert statement fails in PI, you want to cancel the stored procedure call?

[Former Member]

Hi,

If the insert fails the stored procedure should not be run.

Regards,

Arijit

[rajasekhar_reddy14]

You can handle this in different ways.

1)For better error handling Better to design your interface using BPM, and get response back from DB after insert then call stored procedure.

2)Why do you think insert statement fails?make sure that sender system sends valid data and legnth of each field should be as per DB table,if possible try to write a validation in PI Mapping.

Try executing using EOIO mode as mentioned by AMit, but i am not sure Maintain order runtime will work 100%, try using EOIO mode.

3)Within jdbc receiver structure create two statement structures and fist statement structure for insert and second for Stored procedure.

Think aboove options then decide.

[nabendu_sen]

Hi Arijit,

As Gagan mentioned perform JDBC lookup for Insert statement, and if it fails raise Exception from your graphical mapping through UDF. It will throw exception, send you Alert mail (Alert mechanism needs to be configured) and stop further processing.

Please find the below blog to raise exception:

http://scn.sap.com/people/alessandro.guarneri/blog/2006/01/26/throwing-smart-exceptions-in-xi-graphi...

http://scn.sap.com/community/pi-and-soa-middleware/blog/2007/04/26/xipi-throwing-generic-exceptions-...

If you need any more information, let us know.

[Former Member]

Hi,

Can you please tell me how to execute two statements in the same mapping ?

Thanks,

Arijit

[rajasekhar_reddy14]

If you are using same sender structure for stored procedure also then within one JDBC receiver data type you can include two statements.

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

[Former Member]

Hi Everyone thanks for all the responses.

The final approach :-

I am executing statement 1 and statement 2 within the same structure. Statement 1 for insert. Statement 2 for calling the stored procedure.

For retrieving the nextval I am using and UDF found in the SDN : scn.sap.com/thread/3231836

Again thanks for all the help.

[gagandeep_batra]

Hi Arijit,

i am not sure this approach is helpful or not but you can look at this option also.

create a simple scenario which execute the store procedure at end but at mapping level you can add jdbc lookup to insert the record and get return value as successful inserted and the proceed with store procedure call if it is successful inserted.

Regards

Gagan

[Former Member]

Hello,

Why dont you club the logic (like insert and calling of the SP) in one Stored procedure, and let DB handle this kind of processing?

Thanks

Amit Srivastava

[Former Member]

The interface is already in place in the DB. PI came into picture as a part of upgrade. So users don't want to change the existing code in DB.

Thanks,

Arijit

[Former Member]

Hello,

Your second call is dependent upon the result of first call, so u can opt for BPM.

There is one more option of achieving ur scenario, u can use Maintaing order at runtime (in Interface Determination) but that will ensure that SP is only excuted once Insert is sucessfull in PI (I am assuming that source structure will be same for Insert and SP)

Thanks

Amit Srivastava