JDBC Receiver scenarios best practices – Stored procedure design-Part4
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:
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.
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.
PS: Stored procedure will not support batch mode processing hence make sure that batch mode not selected in JDBC receiver channel.
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.
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.
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?
The Table name can be added as a constant value in the mapping as there are less chances of changing. Does that answer your query?
Hi,
I have a PROXY to JDBC scenario in which i need to UPDATE_INSERT data into the temporary table of the database and then call the Stored Procedure written at the temporary table to put the data in the main table.
Please provide your suggestion on how can this be achieved.
Thanks & Regards,
Akash Chauhan