Using CCBPM

[Former Member]

I have a scenario where I have to execute a stored procedure , then a sql statement and again a stored procedure to get the data out of a oracle db.After that it needs to be mapped to an idoc. Can it be done without CCBPM? The stored procedure is basically used to lock the rows till i am exceuting the sql statement and unlock it after i have executed sql.

[Former Member]

Couple of things though,

In help.sap.com it says the sender adapter can have a stored procedure with exactly one select in that. Secondly I donot have luxury to call everyhting in one Stored Procedure since the database i am using is a centrally managed one.Do you think i still can do it without CCBPM?We are using latest SP for XI.

Thanks

[Former Member]

Hi Manish,

if you don't want to write only one stored procedure, it's your right. But as you had already seen in the help sap, JDBC adapter can only work with only one SQL request: SELECT...or EXECUTE stored procedure that I prefer because there is no impact for XI if tomorrow the external DB want to change their table names, field names, join tables, etc..., because for XI it's just to receive the same statement (if fields are not changed) with the data.

If you want to distinguish the three first steps, personnally I see only the BPM... but I find it's a complex solution:

1 BPM + 3 CC + 3 Mapping + many lines in the SXMB_MONI (just for a management of a flag)

Compare to the use of only one big stored procedure:

1 CC + 1 message into SXMB_MONI

It's at you to decide what type of flow you want to have.... at you, or at your client... of course !

Maybe there are other solution like to update the flag directly in your mapping... like a table lookup (but not with a SELECTrr but with an UPDATErr). We did it also with our Java mappings, but it's really a complex solution that I don't suggest you for only one flow !!!

[Former Member]

Michael,

Thx for answering. My point is i have execute these 5 steps in this order

1)Stored procedure to insert a flag(has to be a stored procedure) in oracle db

2)sql statemant(to get the data) in oracle db

3)Stored procedure to make the flag as done in oracle db

4)map to idoc in Integration Server

5)send the idoc to sap.

My preference is to have first 3 steps in the same xtn so that if anyone fails then all fails.

[Former Member]

Hi Manish,

on my project, we did the same: the three first steps in only one stored procedure.

No special matter as the flag and the data were in the same DB (but different table).

Mickael

[Former Member]

Hi,

of course, you can do without BPM...

In your sender CommunicationChannel which is a JDBC adapter, just write something like that 'execute stored_procedure' in the Query SQL Statement.

The good one is available on help sap, sorry I have not my xi with me

This stored procedure will return a statement which will be automatically converted to a XML message. (its the role of your sender Communication Channel !!).

And then it's like all your others interfaces, you have an inbound XML message that you want to map into an Idoc thanks to Interface Mapping.

So for more detail, have a look an help sap.

http://help.sap.com/saphelp_nw04/helpdata/en/14/80243b4a66ae0ce10000000a11402f/frameset.htm

Regards.

Mickael

Edited by: Mickael Huchet on Apr 22, 2008 7:25 PM

Edited by: Mickael Huchet on Apr 22, 2008 7:31 PM