Skip to Content


Scenario:


Http Client sends a Synchronous request to a DB. The stored Procedure is executed and the correspoing role of the user is sent back to the HTTP Client.



image


List Of steps:

  • Below is a sample DB Structure which is used in the scenario.


image


    1. sp_GetRole is the name of the Stored Procedure used.

      Input parameters: Username, Password

      Output parameters: Role


CREATE PROCEDURE .[sp_GetRole]
@UserName char(10),  @Password char(10)  AS
SELECT URole
FROM LoginMaster
WHERE UName = @UserName and UPassword = @Password
GO

    1. Create the required Data types, Message Types & message interfaces required for Request Mapping. U can find the Stored Procedure Message Type on the Left hand side of request mapping shown below.


image


    1. Map the necessary fields.

      EXECUTE–>action

      true–>isInput

      CHAR–>type (CHAR is the Data type of UName & UPassword). Refer to the DB structure in Fig 1.

    2. Create the required Data types, Message Types & message interfaces required for Response mapping. U can find the response of Stored Procedure Message Type on the Right hand side of response mapping shown below.


image

    1. Create the required Interface mapping


image
image


image

    1. Execute the Scenario by sending the request, and you will get the desired output.

Hope this was a comprehensive document for Stored Procedures. For any more queries regarding stored Procedures, SDN is always open 🙂


To report this post you need to login first.

4 Comments

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

  1. This is a superb blog. Thanks!!
    What we need to change to accomplish an INSERT operation via stored proc? Do we need to change anything in XI?
    (0) 
  2. Former Member
    Hi Siva,
    This blog was of great help,
    Do you have any blog explaing the process for extracting data from a Oracle database using a stored procedure,

    Thanks,
    Younus

    (0) 
  3. Former Member
    Hi Shiva,
    Good Blog…I need help for the same scenrio but in my came I am using Sender as IDoc. I want return code from Database once its successfully processed in receiver. Here my doubt is

    Where can I Capture the response from the database in PI?
    I dont want to use BPM in this scenario..How can i achive this…

    (0) 

Leave a Reply