Skip to Content

A stored procedure is a named group of SQL statements that have been previously created and stored in the database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedures reduce network traffic and improve performance. Additionally, stored procedures can be used to help ensure the integrity of the database.

PI interface development point of view the difference between Standard INSERT statement design and stored procedure design is JDBC receiver structure,

JDBC Receiver Stored procedure structure:

SP.jpg

Refer below SAP documentation to understand about JDBC receiver structure formats

http://help.sap.com/saphelp_nw73ehp1/helpdata/en/44/7b72b2fde93673e10000000a114a6b/content.htm

Below screenshot shows the mapping between IDoc to JDBC stored procedure.

SP1.jpg

Map action attribute to constant (EXECUTE) and TABLE to stored procedure name like below screen shot and make sure that stored procedure name is correct.

SP2.jpg

PS: Stored procedure will not support batch mode processing hence make sure that batch mode not selected in JDBC receiver channel.

Sp3.jpg

How stored procedure design works:

PI JDBC adapter not going to perform any operation on Data base only it calls JDBC stored procedure with input parameters,Make sure that PI input put parameter hierarchy should match with stored procedure input parameters.

Once stored procedure receives a request from PI then it performs operation on table (INSERT/UPDATE/DELETE).

Advantages:

1)Performance point of view Stored procedure is always right a design to implement because PI is not going to perform any operation on data base it only calls stored procedure to pass input hence JDBC connection gets released short time compare to  compare to earlier designs.

2) Optimal solution to Handling high volume loads.

Disadvantages:

1)      1)As per my observation bit difficult to handle data issues, like MATERIAL number length defined in data base is 18 characters but PI sending more than 18  characters in this case PI calls stored procedure successfully  but record might fail at stored procedure level, but in case of insert statement design message fails at JDBC adapter level.

2)     2) Dependency on data base, DB team has to develop stored procedures hence it required development efforts.

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Mehul Lad

    Hi Raja,

    Thanks for the blog. Its very helpful.

    I need your help.

    I’ve prepared Request as you’ve shown in Screenshot.

    It’s working Successfull, but i’m getting response empty, may be because of wrong structure i’m providing.

    I’ve confirmed that there’s data in database.

    Can you please provide example of response for above request?

    Thanks,

    Mehul Lad.

    (0) 
  2. Rocio Martin

    Thanks for this blog. I have one more question, where does the table name goes? it should be in the SP or there is a field to fill with that information?

    (0) 

Leave a Reply