Hierarchical Calls of Stored Procedures inserting IDoc Segments, IDoc-JDBC

[Former Member]

Hello all,

I have the following business scenario:

An IDoc is sent to SAP and should be inserted into a Database using stored procedure calls. For each IDoc segment there is a dedicated stored procedure (in total 7 of them).

The insert should be done top-down in the IDoc hierarchy (insert first segment, afterwards inserting all subsegments belonging to the first segment, inserting second segment etc.). For each stored procedure call there is only one segment inserted, this means that I have to call the stored procedure five times in case there are 5 segments.

After each stored procedure call the return code will be checked and only if the code returns success the next stored procedure will be called.

Commit is only done in case all of the calls have been successful.

So, now my problem:

I have started created Message Types and also the Message Mapping. If I map the source segment directly to the root of the Stored Procedure Message Type then I get - for example - 5 target roots for 5 segments.

Would this mean that the stored procedure is called 5 times? This would be perfectly fine in case I only have one stored procedure call.

But I think I cannot go this way if want to follow the hierarchy top-down approach using BPM.

Example: I have 5 segments each of them having 2 subsegments:

If I use the approach above as the 5 segments would be inserted at once I think?

What I want to do however is to insert the first segment, then insert the 2 subsegments, then go to the next segment, insert the fields, and then again the 2 subsegments.

- Which way would you go?

- Message mapping connecting the target root with the segment is probably not the correct solution in this case as the stored procedure would be called at once when i use a transformation step?

I would really appreciate your comments and advice on this. I guess there should be also a best practice pattern as this is a very common scenario.

Thank you very much!

[Former Member]

Hello,

unfortunately I was still not able to find a satisfying solution for this problem although it looked to me as a standard problem at first glance.

I think I have to go via BPM as I have to check the return values after each procedure call but I still do not know how I can take care about the hierachical/nested procedure calls ....

Any input and information is highly appreciated!

[Former Member]

Hi Floarian,

For each IDoc segment there is a dedicated stored procedure (in total 7 of them).

The insert should be done top-down in the IDoc hierarchy (insert first segment, afterwards inserting all subsegments belonging to the first segment, inserting second segment etc.). For each stored procedure call there is only one segment inserted, this means that I have to call the stored procedure five times in case there are 5 segments.

After each stored procedure call the return code will be checked and only if the code returns success the next stored procedure will be called.

Commit is only done in case all of the calls have been successful.

---As per above statement you have 7 different SP( stored procedures) which you want to call and base on result of previous one i.e if sucessfull then only call next one....

I think this situation you can handle using one single SP only...you can pass all data to your one SP and inside main SP you can call all your 7 "SPs". With this case you get better control over process and would be much faster than calling 7 SPs from XI or using BPM for same.

Let us know your opinion.

Nilesh

[Former Member]

Hi Nilesh,

thank you very much for your comment.

Indeed it would be much easier to wrap all existing stored procedures into just one stored procedure.

However this solution cannot be implemented in this case as I have certain restrictions from database side and a strict requirement to use exactly those existing stored procedures.

Just to sum up the situation again:

Each stored procedures exactly fits to one IDoc segment and inserts just one segment. However I still have no clue how to design the hierarchical calls (incl. checking the status between them...)

It would be great to have some more input from your side.

[Former Member]

deleted

[Former Member]

Hi Florian,

Check out below thread.. Bhavesh's reply.

You can call multiple SPs here...but I think you need to used BPM to call all sp's in sync..one after another base on previous sp result.

Nilesh

[Former Member]

Hello,

again, thank you for your further information.

I checked the link however this still does not solve my problem.

I have already created that target XML SQL structures in a way that the number of <Statement> tags reflects the number of occurences of the IDoc segments in the source message type. This wouild however mean that the stored procedures of one level are called in a sequence.

However the problem is that first all stored procedures for subsegments should be called before moving on to the next segment.

Example: 5 segment types, each of them with 3 subsegments

Instead of calling those 5 segments in a sequence, first the 3 subsegments have to be called before moving on to the next segment.

Furthermore if I call the multiple stored procedures in a single transformation step in BPM I am note sure how to evaluate the return code for each single call.

If it is easier for you then I could paste the concrete case....

Thank you again for your important help!

[Former Member]

Hi Florian,

I havent worked on exact scenario you have mentioned here...but something we have done using RFC adapter..calling function module base on response using BPM..

Send me your email id..I'll mail you some screen shot(BPM) of the same, which you can use for reference.

Meanwhile you can check this Bhavesh's blog on JDBC sync inteface blog with BPM..request and collecting response.

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

Nilesh

[Former Member]

Hello,

please find the Email you can use for the screenshot below

Thank you very much!

I was just thinking again about a possible solution. Maybe it would also be possible to create a nested target structure with the stored procedure calls exactly representing the order of the stored procedure calls.

However I do not know whether nested <Statement> Elements are possible.

The other problem also could be that there is no possibility to check the return codes of the calls in that case...

Message was edited by:

Kenny Scott

[Former Member]

Sent..

I am not sure about Nested target str. with SP.

But still I feel writing one single sp n calling all remaining in SPs in this one would be much easier and faster..

Hope this will help.

Nilesh

[Former Member]

Thanks a lot for the Email.

I just feel very strange that the nested SP Calls do not seem to be that easy as expected.

Before I came to SAP XI I worked a lot with webMethods and calling nested SP was a common requirement that time and also very easy to implement in webMethods.

Implementing a loop, calling a SP, checking the return code, and in case of success just implementing the next loop... this was not a big deal.

Hopefully I can solve this issue...

[Former Member]

Let us know how you solve this issue once you are through.

Nilesh

[Former Member]

Yes, of course I will let you know. Hopefully I will find a way how to implement these requirements as timeschedule is very tight.

[VijayKonam]

Florian,

As of now, to implement your requirement, BPM is the only answer. As you said it might have been easier in WebMethods. It is easier in XI as well, provided you are not afraid of working with BPM.

Receve in BPM call the SQL Send sterp synchronously, check the return value, have case step in BPM and move on forther with all of your 7 steps. As simple as that.

BPM is provided to solve this kind of business requirements only. If you want to surpass BPM without changing the SPs already exisitng,, then XI is not the solution here.

VJ

[Former Member]

Hello VJ,

thank you for your comment. I am not afraid of working with BPM The other way round, for me it also seemed to be the only possible solution.

However I still do not see how I can use BPM to loop through a hierarchical structure calling a stored procedure for each segment.

Just to clarify again, a stored procedure is called exactly once for each occurence of an segment. If there are 5 segments the stored procedure is called 5 times.

In my current case, there are approx. 50 segments, each of those having 3 subsegments. This would mean that there are 50sp calls and for each of those 3 sp calls for the subsegments.

And, the 50 sp calls are not done in sequence, but the the sp are called for each subsegment first before moving on the next segment.

Do you have an idea on this?

Here is some Pseudocode just for clarfication:

Loop Over SegmentLevel1

(

Call SP for SegmentLevel1

Check ReturnCode

If ReturnCode == sucess

(loop over subsegmentLevel2

Call SP for SegmentLevel2

)

If ReturnCode == error

(RollBack all already called SP)

)

[Former Member]

Hello,

as I am still not able to solve the problem, I would like to reduce the complexity of the requirement a little bit so that maybe a new approach can be found.

Let's say I have only segments of ONE hierarchy level in an IDoc. There is only one stored procedure existing which inserts exactly ONE record.

This means, if I have 10 segments I had to call the stored procedure 10 times.

After each call I have to check the result code returned by the stored procedure.

So, if I create a target XML SQL Message type and map the segment element to the statement element then the stored procedure would be called 10 times I think.

How would I implement this pattern in BPM so that after each call the return code is checked, if the return code is ok then the next call will done.

Commit only after all calls have been successfull, Rollback in case there was a return code indicating failure during the calls.

Thank you again for your valuable input and help!